开发者社区> 德哥> 正文

PostgreSQL 11 preview 分区过滤控制参数 - enable_partition_pruning

简介:
+关注继续查看

标签

PostgreSQL , 分区控制 , enable_partition_pruning


背景

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  

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;  

参考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=055fb8d33da6ff9003e3da4b9944bdcd2e2b2a49

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
hive 动态分区(Dynamic Partition)异常处理
Changing Hive Dynamic Partition Limits Symptoms: Hive enforces limits on the number of dynamic partitions that it creates.
1348 0
虚拟化VMware简介9——网络 I/O 控制 (NIOC) 简介
文章整理自VMware 官网: 网络 I/O控制 (NIOC) 通过持续监控网络上的 I/O负载并根据具体业务需求动态分配可用  I/O 资源,划分网络访问的优先级。
1010 0
Java基础-23总结多线程,线程实现Runnable接口,线程名字获取和设置,线程控制,线程安全,同步线程
你需要的是什么,直接评论留言。 获取更多资源加微信公众号“Java帮帮” (是公众号,不是微信好友哦) 还有“Java帮帮”今日头条号,技术文章与新闻,每日更新,欢迎阅读 学习交流请加Java帮帮交流QQ群553841695 分享是一种美德,分享更快乐! 1:多线程(理解) (1)多线程:一个应用程序有多条执行路径 进程:正
2880 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载