标簽
PostgreSQL , 分區控制 , enable_partition_pruning
https://github.com/digoal/blog/blob/master/201804/20180424_02.md#%E8%83%8C%E6%99%AF 背景
PostgreSQL 10開始支援了分區表的文法，可以通過新的文法建立分區表，而更早的版本則需要使用inherit+check限制+rule/trigger來建立分區表。
《分區表鎖粒度差異 - pg_pathman VS native partition table》 《PostgreSQL 傳統 hash 分區方法和性能》以往，PG通過constraint_exclusion參數來控制select,update,delete的選擇。（作用于表、繼承、分區表、UNION ALL等，根據設定決定要判斷哪些表的check限制）
https://www.postgresql.org/docs/10/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER分區較多時，constraint_exclusion控制，性能可能較差。
PG 11增加了一個參數 enable_partition_pruning ，僅用于控制分區表（不用于控制inherit, union all等操作）的QUERY。
也就是說，以後使用建立分區表的文法建立的表，必須通過enable_partition_pruning參數來控制，是否要對select,update,delete操作過濾到目标分區。
Add GUC enable_partition_pruning
This controls both plan-time and execution-time new-style partition
pruning. While finer-grain control is possible (maybe using an enum GUC
instead of boolean), there doesn't seem to be much need for that.
This new parameter controls partition pruning for all queries:
trivially, SELECT queries that affect partitioned tables are naturally
under its control since they are using the new technology. However,
while UPDATE/DELETE queries do not use the new code, we make the new GUC
control their behavior also (stealing control from
constraint_exclusion), because it is more natural, and it leads to a
more natural transition to the future in which those queries will also
use the new pruning code.
Constraint exclusion still controls pruning for regular inheritance
situations (those not involving partitioned tables).
Author: David Rowley
Review: Amit Langote, Ashutosh Bapat, Justin Pryzby, David G. Johnston
Discussion: https://postgr.es/m/CAKJS1f_0HwsxJG9m+nzU+CizxSdGtfe6iF_ykPYBiYft302DCw@mail.gmail.com
https://github.com/digoal/blog/blob/master/201804/20180424_02.md#test-case test case
+--
+-- Ensure the enable_partition_prune GUC properly disables partition pruning.
+--
+create table pp_lp (a int, value int) partition by list (a);
+create table pp_lp1 partition of pp_lp for values in(1);
+create table pp_lp2 partition of pp_lp for values in(2);
+explain (costs off) select * from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Append
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+(3 rows)
+
+explain (costs off) update pp_lp set value = 10 where a = 1;
+ QUERY PLAN
+--------------------------
+ Update on pp_lp
+ Update on pp_lp1
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+(4 rows)
+
+explain (costs off) delete from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Delete on pp_lp
+ Delete on pp_lp1
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+(4 rows)
+set enable_partition_pruning = off;
+set constraint_exclusion = 'partition'; -- this should not affect the result.
+explain (costs off) select * from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Append
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(5 rows)
+
+explain (costs off) update pp_lp set value = 10 where a = 1;
+ QUERY PLAN
+--------------------------
+ Update on pp_lp
+ Update on pp_lp1
+ Update on pp_lp2
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
+
+explain (costs off) delete from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Delete on pp_lp
+ Delete on pp_lp1
+ Delete on pp_lp2
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
+set constraint_exclusion = 'off'; -- this should not affect the result.
+explain (costs off) select * from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Append
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(5 rows)
+
+explain (costs off) update pp_lp set value = 10 where a = 1;
+ QUERY PLAN
+--------------------------
+ Update on pp_lp
+ Update on pp_lp1
+ Update on pp_lp2
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
+
+explain (costs off) delete from pp_lp where a = 1;
+ QUERY PLAN
+--------------------------
+ Delete on pp_lp
+ Delete on pp_lp1
+ Delete on pp_lp2
+ -> Seq Scan on pp_lp1
+ Filter: (a = 1)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
+drop table pp_lp;
+-- Ensure enable_partition_prune does not affect non-partitioned tables.
+create table inh_lp (a int, value int);
+create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "value" with inherited definition
+create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "value" with inherited definition
+set constraint_exclusion = 'partition';
+-- inh_lp2 should be removed in the following 3 cases.
+explain (costs off) select * from inh_lp where a = 1;
+ QUERY PLAN
+---------------------------
+ Append
+ -> Seq Scan on inh_lp
+ Filter: (a = 1)
+ -> Seq Scan on inh_lp1
+ Filter: (a = 1)
+(5 rows)
+
+explain (costs off) update inh_lp set value = 10 where a = 1;
+ QUERY PLAN
+---------------------------
+ Update on inh_lp
+ Update on inh_lp
+ Update on inh_lp1
+ -> Seq Scan on inh_lp
+ Filter: (a = 1)
+ -> Seq Scan on inh_lp1
+ Filter: (a = 1)
+(7 rows)
+
+explain (costs off) delete from inh_lp where a = 1;
+ QUERY PLAN
+---------------------------
+ Delete on inh_lp
+ Delete on inh_lp
+ Delete on inh_lp1
+ -> Seq Scan on inh_lp
+ Filter: (a = 1)
+ -> Seq Scan on inh_lp1
+ Filter: (a = 1)
+(7 rows)
+-- Ensure we don't exclude normal relations when we only expect to exclude
+-- inheritance children
直接操作子表，不起作用constraint_exclusion = 'partition', on則起作用
+explain (costs off) update inh_lp1 set value = 10 where a = 2;
+ QUERY PLAN
+---------------------------
+ Update on inh_lp1
+ -> Seq Scan on inh_lp1
+ Filter: (a = 2)
+(3 rows)
+
直接操作子表，不起作用constraint_exclusion = 'partition', on則起作用
postgres=# set constraint_exclusion = 'on';
SET
postgres=# explain (costs off) update inh_lp1 set value = 10 where a = 2;
QUERY PLAN
--------------------------------
Update on inh_lp1
-> Result
One-Time Filter: false
(3 rows)
+\set VERBOSITY terse \\ -- suppress cascade details
+drop table inh_lp cascade;
+NOTICE: drop cascades to 2 other objects
+\set VERBOSITY default
+reset enable_partition_pruning;
+reset constraint_exclusion;
![spring data JPA中的主鍵政策[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)