postgresql建立分区及测试
|
目录 1 建立分区... 2 1.1. 创建主表... 2 1.2. 创建分区表... 2 1.3. 分区键上建索引... 3 1.4. 创建触发器函数... 3 1.5. 创建触发器... 5 1.6. 改用RULE规则... 5 2 测试... 5 2.1. 查看所有表... 5 2.2. 查看主表结构... 6 2.3. 插入数据... 6 2.4. 查看主表数据... 7 2.5. 查看分表数据... 7 2.6. 分区排除和查询优化... 8 2.7. 开启约束排除... 9 1建立分区1.1. 创建主表CREATE TABLE measurement ( city_id int not null, logdatedate not null, peaktemp int, unitsales int ); CREATE TABLE 这里date类型精确到天，如'2006-02-01'，如果时间精确到秒，如'2013-01-09 00:00:00' 使用timestamp 类型 1.2. 创建分区表CREATE TABLEmeasurement_y2006m01 ( CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m02 ( CHECK (logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK (logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m04 ( CHECK (logdate >= DATE '2006-04-01' AND logdate < DATE '2006-05-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m05 ( CHECK (logdate >= DATE '2006-05-01' AND logdate < DATE '2006-06-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m06 ( CHECK (logdate >= DATE '2006-06-01' AND logdate < DATE '2006-07-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m07 ( CHECK (logdate >= DATE '2006-07-01' AND logdate < DATE '2006-08-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m08 ( CHECK (logdate >= DATE '2006-08-01' AND logdate < DATE '2006-09-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m09 ( CHECK (logdate >= DATE '2006-09-01' AND logdate < DATE '2006-10-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m10 ( CHECK (logdate >= DATE '2006-10-01' AND logdate < DATE '2006-11-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m11 ( CHECK (logdate >= DATE '2006-11-01' AND logdate < DATE '2006-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m12 ( CHECK (logdate >= DATE '2006-12-01' AND logdate < DATE '2007-01-01' ) ) INHERITS (measurement); CREATE TABLE 1.3. 分区键上建索引CREATE INDEX measurement_y2006m02_logdateON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdateON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2006m04_logdateON measurement_y2006m04 (logdate); CREATE INDEX measurement_y2006m05_logdateON measurement_y2006m05 (logdate); CREATE INDEX measurement_y2006m06_logdateON measurement_y2006m06 (logdate); CREATE INDEX measurement_y2006m07_logdateON measurement_y2006m07 (logdate); CREATE INDEX measurement_y2006m08_logdateON measurement_y2006m08 (logdate); CREATE INDEX measurement_y2006m09_logdateON measurement_y2006m09 (logdate); CREATE INDEX measurement_y2006m10_logdateON measurement_y2006m10 (logdate); CREATE INDEX measurement_y2006m11_logdateON measurement_y2006m11 (logdate); CREATE INDEX measurement_y2006m12_logdateON measurement_y2006m12 (logdate); CREATE INDEX 执行insert into measurement命令向表中插入数据，希望数据按一定的规则插入相应分区，需要前期人工处理下，这里有两种策略，建立触发器和RULE规则。建立触发器方法需要1.4、1.5两步操作，1.6是建立RULE规则方法。 1.4. 创建触发器函数CREATE OR REPLACEFUNCTION measurement_insert_trigger() RETURNSTRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-01-01'AND NEW.logdate < DATE '2006-02-01' )THEN INSERT INTO measurement_y2006m01 VALUES(NEW.*); ELSIF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01'AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-04-01'AND NEW.logdate < DATE '2006-05-01' ) THEN INSERT INTO measurement_y2006m04 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-05-01'AND NEW.logdate < DATE '2006-06-01' ) THEN INSERT INTO measurement_y2006m05 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-06-01'AND NEW.logdate < DATE '2006-07-01' ) THEN INSERT INTO measurement_y2006m06 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-07-01'AND NEW.logdate < DATE '2006-08-01' ) THEN INSERT INTO measurement_y2006m07 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-08-01'AND NEW.logdate < DATE '2006-09-01' ) THEN INSERT INTO measurement_y2006m08 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-09-01' AND NEW.logdate < DATE '2006-10-01' ) THEN INSERT INTO measurement_y2006m09 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-10-01' AND NEW.logdate < DATE '2006-11-01' ) THEN INSERT INTO measurement_y2006m10 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-11-01'AND NEW.logdate < DATE '2006-12-01' ) THEN INSERT INTO measurement_y2006m11 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-12-01' AND NEW.logdate < DATE '2007-01-01' ) THEN INSERT INTO measurement_y2006m12 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range.Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE FUNCTION 说明：如果不想丢失数据，上面的ELSE 条件可以改成INSERTINTO measurement _error_ logdate VALUES (NEW.*); 同时需要创建一张结构和measurement 一样的表measurement_error_logdate，这样，错误的logdate 数据就可以插入到这张表中而不是报错了。 1.5. 创建触发器CREATE TRIGGERinsert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDUREmeasurement_insert_trigger(); CREATE TRIGGER 1.6. 改用RULE规则也可以不用触发器，改用RULE规则，为每个分表创建规则
CREATE RULEmeasurement_y2006m01 AS NEW.logdate < DATE'2006-02-01') DO INSTEAD INSERT INTO measurement VALUES (NEW.*); CREATE RULEmeasurement_y2006m02 AS NEW.logdate < DATE'2006-03-01') DO INSTEAD INSERT INTO measurement VALUES (NEW.*); ……省略 2测试1. 2. 2.1. 查看所有表mydb=# d List of relations Schema | Name | Type| Owner --------+----------------------+-------+------- public | measurement | table | super public | measurement_y2006m01 | table | super public | measurement_y2006m02 | table | super public | measurement_y2006m03 | table | super public | measurement_y2006m04 | table | super public | measurement_y2006m05 | table | super public | measurement_y2006m06 | table | super public | measurement_y2006m07 | table | super public | measurement_y2006m08 | table | super public | measurement_y2006m09 | table | super public | measurement_y2006m10 | table | super public | measurement_y2006m11 | table | super public | measurement_y2006m12 | table | super (13rows) 2.2. 查看主表结构mydb=# d measurement Table "public.measurement" Column | Type| Modifiers -----------+---------+----------- city_id| integer | not null logdate| date |not null peaktemp| integer | unitsales | integer | Triggers: insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROWEXECUTE PROCEDURE measurement_insert_trigger(). Number of child tables: 12 (Use d+ to listthem.) 2.3. 插入数据mydb=# insert into measurement values(1,date '2006-02-10',1,1); INSERT 0 0 mydb=# insert into measurement values(1,date '2006-03-10',date '2006-04-10',1); INSERT 0 0 2.4. 查看主表数据mydb=# select * from measurement; city_id |logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2006-02-10 | 1 | 1 1 | 2006-03-10 | 1 | 1 1 | 2006-04-10 | 1 | 1 (3 rows) 2.5. 查看分表数据mydb=# select * frommeasurement_y2006m03; city_id |logdate | peaktemp | unitsales ---------+------------+----------+----------- 1 | 2006-03-10 | 1 | 1 (1row) 2.6. 分区排除和查询优化默认情况下，是不会开启分区排除的，索引针对一个基于分区键条件的检索，要扫描所有的分区 mydb=# SET constraint_exclusion = off; SET mydb=# EXPLAIN SELECT count(*) FROMmeasurement WHERE logdate <= DATE '2006-02-01'; QUERYPLAN ----------------------------------------------------------------------------------------------- Aggregate(cost=436.80..436.81 rows=1 width=0) -> Append (cost=0.00..417.62 rows=7670 width=0) -> Seq Scan onmeasurement (cost=0.00..32.12 rows=590width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m01 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m02 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m03 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m04 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m05 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan on measurement_y2006m06measurement (cost=0.00..32.12 rows=590width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m07 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <= '2006-02-01'::date) -> Seq Scan onmeasurement_y2006m08 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m09 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m10 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m11 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan onmeasurement_y2006m12 measurement(cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) (28 rows) 2.7. 开启约束排除mydb=# SET constraint_exclusion = on; SET mydb=> EXPLAIN SELECT count(*) FROM measurement WHERE logdate<= DATE '2006-02-01'; QUERYPLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=100.80..100.81 rows=1 width=0) -> Append(cost=0.00..96.38 rows=1770 width=0) -> Seq Scan on measurement (cost=0.00..32.12 rows=590 width=0) Filter: (logdate <='2006-02-01'::date) -> Seq Scan on measurement_y2006m01measurement (cost=0.00..32.12 rows=590width=0) Filter:(logdate <= '2006-02-01'::date) -> Seq Scan on measurement_y2006m02 measurement (cost=0.00..32.12 rows=590 width=0) Filter:(logdate <= '2006-02-01'::date) (8 rows) 至此分区表建立完成 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |