PostgreSQL分区表的执行计划
开始
比如说我已经做好了对分区表的规则：
postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS TRIGGER AS $$ postgres$# postgres$# BEGIN postgres$# postgres$# IF ( NEW.id <5000000 ) THEN postgres$# INSERT INTO ctest01 VALUES (NEW.*); postgres$# ELSIF ( NEW.id >= 5000000 ) THEN postgres$# INSERT INTO ctest02 VALUES (NEW.*); postgres$# ELSE postgres$# RAISE EXCEPTION 'Error while inserting data'; postgres$# END IF; postgres$# postgres$# RETURN NULL; postgres$# END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# postgres=# CREATE TRIGGER insert_ptest_trigger BEFORE INSERT ON ptest FOR EACH ROW postgres-# EXECUTE PROCEDURE ptest_insert_trigger(); CREATE TRIGGER postgres=#
就是说 ctest01 的数据， id<5000000, ctest02的数据， id>=5000000。
此时我的执行计划仍然是这个样子的：它似乎没有意识到我的ptest表的规则：
postgres=# explain select * from ptest where id=5000 or id=6000000; QUERY PLAN ----------------------------------------------------------------------------------------------- Result (cost=0.00..54.93 rows=5 width=20) -> Append (cost=0.00..54.93 rows=5 width=20) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: ((id = 5000) OR (id = 6000000)) -> Bitmap Heap Scan on ctest01 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) -> Bitmap Heap Scan on ctest02 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) (18 rows) postgres=#
对where 条件，它把它应用到每一个分区子表上了！
这里要谈到一个参数：
constraint_exclusion (enum)
Controls the query planner's use of table constraints to optimize queries. The allowed values of constraint_exclusion areon (examine constraints for all tables), off (never examine constraints), and partition (examine constraints only for inheritance child tables and UNION ALL subqueries). partition is the default setting. It is often used with inheritance and partitioned tables to improve performance.
当其为on或者 partition 的时候，在我这个例子里都是一样效果（我的父表没有数据）
postgres=# show constraint_exclusion; constraint_exclusion ---------------------- partition (1 row) postgres=# explain select * from ptest where id=5000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 5000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) (6 rows) postgres=# postgres=# explain select * from ptest where id=600000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 600000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 600000) (6 rows) postgres=# postgres=# explain select * from ptest where id=5000 UNION ALL select * from ptest where id=6000000; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Result (cost=0.00..27.55 rows=4 width=36) -> Append (cost=0.00..27.55 rows=4 width=36) -> Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 5000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) -> Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 6000000) -> Index Scan using ctest02_id_idx on ctest02 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 6000000) (14 rows) postgres=# postgres=# explain select * from ptest where id=5000 or id=6000000; QUERY PLAN ----------------------------------------------------------------------------------------------- Result (cost=0.00..54.93 rows=5 width=20) -> Append (cost=0.00..54.93 rows=5 width=20) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: ((id = 5000) OR (id = 6000000)) -> Bitmap Heap Scan on ctest01 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) -> Bitmap Heap Scan on ctest02 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) (18 rows) postgres=#
postgres=# set session constraint_exclusion=on; SET postgres=# postgres=# explain select * from ptest where id=5000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 5000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) (6 rows) postgres=# postgres=# postgres=# explain select * from ptest where id=6000000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 6000000) -> Index Scan using ctest02_id_idx on ctest02 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 6000000) (6 rows) postgres=# postgres=# explain select * from ptest where id=5000 postgres-# UNION ALL postgres-# select * from ptest where id=6000000; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Result (cost=0.00..27.55 rows=4 width=36) -> Append (cost=0.00..27.55 rows=4 width=36) -> Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 5000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) -> Result (cost=0.00..13.75 rows=2 width=36) -> Append (cost=0.00..13.75 rows=2 width=36) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 6000000) -> Index Scan using ctest02_id_idx on ctest02 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 6000000) (14 rows) postgres=# postgres=# explain select * from ptest where id=5000 or id=6000000; QUERY PLAN ----------------------------------------------------------------------------------------------- Result (cost=0.00..54.93 rows=5 width=20) -> Append (cost=0.00..54.93 rows=5 width=20) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: ((id = 5000) OR (id = 6000000)) -> Bitmap Heap Scan on ctest01 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest01_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) -> Bitmap Heap Scan on ctest02 ptest (cost=19.49..27.46 rows=2 width=9) Recheck Cond: ((id = 5000) OR (id = 6000000)) -> BitmapOr (cost=19.49..19.49 rows=2 width=0) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 5000) -> Bitmap Index Scan on ctest02_id_idx (cost=0.00..9.74 rows=1 width=0) Index Cond: (id = 6000000) (18 rows) postgres=#
也就是说， constraint_exclusion 的识别能力也是有限的。对于 where 条件比较复杂的，也是无法处理的。
那么，constraint_exclusion off 时候，又如此？此时连对 id=5000 这样的，都需在所有的分区表里查询：
postgres=# set session constraint_exclusion=off; SET postgres=# postgres=# postgres=# explain select * from ptest where id=5000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..27.51 rows=3 width=27) -> Append (cost=0.00..27.51 rows=3 width=27) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 5000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) -> Index Scan using ctest02_id_idx on ctest02 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 5000) (8 rows) postgres=# postgres=# explain select * from ptest where id=6000000; QUERY PLAN ------------------------------------------------------------------------------------------------- Result (cost=0.00..27.51 rows=3 width=27) -> Append (cost=0.00..27.51 rows=3 width=27) -> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=62) Filter: (id = 6000000) -> Index Scan using ctest01_id_idx on ctest01 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 6000000) -> Index Scan using ctest02_id_idx on ctest02 ptest (cost=0.00..13.75 rows=1 width=9) Index Cond: (id = 6000000) (8 rows) postgres=#
结束
