postgresql 分区与优化

--对于分区表constraint_exclusion 这个参数需要配置为partition或on
postgres=# show constraint_exclusion ;
 constraint_exclusion 
----------------------
 partition

 
 
 --创建父子表, 用于存储分区数据
create table t(id int primary key);
create table t1(like t including all) inherits(t);
create table t2(like t including all) inherits(t);
create table t3(like t including all) inherits(t);
create table t4(like t including all) inherits(t);
--PostgreSQL的子表和子表之间的约束是没有任何关系的, 所以也可以有重叠, 即非全局约束.
 alter table t1 add constraint ck_t1_1 check(id<0);
 alter table t2 add constraint ck_t2_1 check(id>=0 and id<100);
 alter table t3 add constraint ck_t3_1 check(id>=100 and id<200);
 alter table t4 add constraint ck_t4_1 check(id>=200);
 
 --分区字段传入常量, 执行时扫描的是父表和约束对应的子表 :
postgres=#  explain select * from t where id=10;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.00..8.17 rows=2 width=4)
   ->  Seq Scan on t  (cost=0.00..0.00 rows=1 width=4)
         Filter: (id = 10)
   ->  Index Only Scan using t2_pkey on t2  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = 10)
(5 rows)

--分区字段传入常量, 执行时扫描的是父表和约束对应的子表;
postgres=#  prepare p_test as select * from t where id=$1;
PREPARE
postgres=# explain execute p_test(1);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.00..8.17 rows=2 width=4)
   ->  Seq Scan on t  (cost=0.00..0.00 rows=1 width=4)
         Filter: (id = 1)
   ->  Index Only Scan using t2_pkey on t2  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = 1)
(5 rows)

--子句查询, 执行时扫描的是父表和所有子表, 注意这里使用的子查询是子表的查询, 理论上应该是扫描父表和该子表
postgres=#  explain select * from t where id=(select id from t1 limit 1);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.01..32.70 rows=5 width=4)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.01 rows=1 width=4)
           ->  Seq Scan on t1 t1_1  (cost=0.00..34.00 rows=2400 width=4)
   ->  Seq Scan on t  (cost=0.00..0.00 rows=1 width=4)
         Filter: (id = $0)
   ->  Index Only Scan using t1_pkey on t1  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = $0)
   ->  Index Only Scan using t2_pkey on t2  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = $0)
   ->  Index Only Scan using t3_pkey on t3  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = $0)
   ->  Index Only Scan using t4_pkey on t4  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: (id = $0)
(14 rows)

--综上可知在对分区表进行查询时最好使用字面常量，而不要使用子查询之类复杂的sql


--如果子表上约束删除，则pg不得不把删除约束的子表也加入到查询中(即使子表可以忽略)
alter table t4 drop constraint ck_t4_1;
postgres=#  explain select * from t where id=10;   
                                 QUERY PLAN                                  
--------------------------