https://www.cnblogs.com/butterfly100/p/9034281.html
--1、创建主表
CREATE TABLE tbl_partition ( date_key date, hour_key smallint, client_key integer, item_key integer, account integer, expense numeric );--2、创建多个分表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列。
CREATE TABLE tbl_partition_2016_01() inherits (tbl_partition); CREATE TABLE tbl_partition_2016_02() inherits (tbl_partition); CREATE TABLE tbl_partition_2016_03() inherits (tbl_partition); --CREATE TABLE tbl_partition_2016_04() inherits (tbl_partition);--3、TODO为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠。
ALTER TABLE tbl_partition_2016_01 ADD CONSTRAINT tbl_partition_2016_01_check_date_key CHECK (date_Key >= '2016-01-01'::date AND date_Key < '2016-02-01'::date); ALTER TABLE tbl_partition_2016_02 ADD CONSTRAINT tbl_partition_2016_02_check_date_key CHECK (date_Key >= '2016-02-01'::date AND date_Key < '2016-03-01'::date); ALTER TABLE tbl_partition_2016_03 ADD CONSTRAINT tbl_partition_2016_03_check_date_key CHECK (date_Key >= '2016-03-01'::date AND date_Key < '2016-04-01'::date);--4、为每一个分区表，在主要的列上创建索引。该索引并不是严格必须创建的，但在大部分场景下，它都非常有用。
---CREATE INDEX tbl_partition_all_date_key ON tbl_partition_all (date_key,client_key); CREATE INDEX tbl_partition_date_key_2016_01 ON tbl_partition_2016_01 (date_key,client_key); CREATE INDEX tbl_partition_date_key_2016_02 ON tbl_partition_2016_02 (date_key,client_key); CREATE INDEX tbl_partition_date_key_2016_03 ON tbl_partition_2016_03 (date_key,client_key);--5、定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表。
--创建分区函数 CREATE OR REPLACE FUNCTION tbl_partition_trigger() RETURNS TRIGGER AS $$ BEGIN IF NEW.date_key >= DATE '2016-01-01' AND NEW.date_Key < DATE '2016-02-01' THEN INSERT INTO tbl_partition_2016_01 VALUES (NEW.*); ELSIF NEW.date_key >= DATE '2016-02-01' AND NEW.date_Key < DATE '2016-03-01' THEN INSERT INTO tbl_partition_2016_02 VALUES (NEW.*); ELSIF NEW.date_key >= DATE '2016-03-01' AND NEW.date_Key < DATE '2016-04-01' THEN INSERT INTO tbl_partition_2016_03 VALUES (NEW.*); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;--6、挂载分区Trigger
CREATE TRIGGER insert_tbl_partition_trigger BEFORE INSERT ON tbl_partition FOR EACH ROW EXECUTE PROCEDURE tbl_partition_trigger();--7、创建全表
CREATE TABLE tbl_partition_all ( date_key date, hour_key smallint, client_key integer, item_key integer, account integer, expense numeric );--8、自动建表触发器
CREATE OR REPLACE FUNCTION tbl_partition_trigger() RETURNS TRIGGER AS $$ DECLARE month_text TEXT; this_month_first_day_text TEXT; next_month_first_day_text TEXT; insert_statement TEXT; BEGIN SELECT to_char(NEW.date_key, 'YYYY_MM') INTO month_text; SELECT get_month_first_day(NEW.date_key) INTO this_month_first_day_text; SELECT to_char(to_date(this_month_first_day_text,'YYYY-MM-DD') + interval '1 month', 'YYYY-MM-DD') INTO next_month_first_day_text; insert_statement := 'INSERT INTO tbl_partition_' || month_text||' VALUES ($1.*)'; EXECUTE insert_statement USING NEW; RETURN NULL; EXCEPTION WHEN UNDEFINED_TABLE THEN EXECUTE 'CREATE TABLE IF NOT EXISTS tbl_partition_' || month_text || '(CHECK (date_key >= ''' || this_month_first_day_text || ''' and date_key<''' || next_month_first_day_text || ''')) INHERITS (tbl_partition)'; RAISE NOTICE 'CREATE NON-EXISTANT TABLE tbl_partition_%', month_text; EXECUTE 'CREATE INDEX tbl_partition_date_key_' || month_text || ' ON tbl_partition_' || month_text || '(date_key)'; EXECUTE insert_statement USING NEW; RETURN NULL; END; $$ LANGUAGE plpgsql;--get_month_first_day
CREATE OR REPLACE FUNCTION get_month_first_day(in in_date date,out out_date text) AS $$ BEGIN SELECT to_char(in_date, 'YYYY_MM')||'_01' INTO out_date; END; $$ LANGUAGE plpgsql;--插入数据脚本
INSERT INTO tbl_partition_all select (select array_agg(i::date) from generate_series( '2015-12-01'::date, '2015-12-30'::date, '1 day'::interval) as t(i) )[floor(random()*4)+1] as date_key, floor(random()*24) as hour_key, floor(random()*1000000)+1 as client_key, floor(random()*100000)+1 as item_key, floor(random()*20)+1 as account, floor(random()*10000)+1 as expense from generate_series(1,300000000,1); INSERT INTO tbl_partition SELECT * FROM tbl_partition_all; -------------------------------------------------主体结束----------------------------------------- -------------------------------------------------主体结束-------------------------------------------使用rule创建分表
CREATE RULE tbl_partition_rule_2016_01 AS ON INSERT TO tbl_partition WHERE date_key >= DATE '2016-01-01' AND date_Key < DATE '2016-02-01' DO INSTEAD INSERT INTO tbl_partition_2016_01 VALUES (NEW.*);--删除继承关系
ALTER TABLE tbl_partition_2016_01 NO INHERIT tbl_partition;--查询对比测试
EXPLAIN ANALYZE select count(account) ,client_key from tbl_partition v where v.date_key >='2016-03-02' and v.date_key <='2016-03-07' group by client_key ;EXPLAIN ANALYZE
select count(account) ,client_key from tbl_partition_all v where v.date_key >='2016-03-02' and v.date_key <='2016-03-12' group by client_key ;