关于PostgreSQL的分区表的历史及分区裁剪参数enable_partition_pruning与constraint_exclusion的区别

2024-08-28 23:52
文章标签 参数 历史 postgresql 裁剪 区别 分区 分区表 enable partition constraint exclusion pruning

本文主要是介绍关于PostgreSQL的分区表的历史及分区裁剪参数enable_partition_pruning与constraint_exclusion的区别，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1. 疑惑

我们知道控制分区裁剪的参数有两个：

  • enable_partition_pruning
  • constraint_exclusion

这两个参数有什么区别呢？

2. 解答

要说明这两个参数的区别需要先讲一讲PostgreSQL数据库中分区的历史，在PostgreSQL 10版本之前，PostgreSQL数据库实际上是没有单独的创建分区表的DDL语句，都是通过表继承的原理来创建分区表，这样使得在PostgreSQL中使用分区表不是很方便，到PostgreSQL 10之后，PostgreSQL扩展了创建表的DDL语句，可以用这个DDL语句来创建分区表，原先使用继承的方式还是可以创建分区表，但这两种分区表是不能混用的。于是PostgreSQL 10增加的分区表叫声明式分区（Declarative Partitioning），原先使用表继承的方式仍然可以实现分区表的功能。

而使用继承的方式实现的分区表的分区裁剪是靠设置参数“constraint_exclusion=partition”来实现的，而如果使用了声明式分区表，则需要使用参数“enable_partition_pruning”来控制是否使用分区裁剪功能。

3. 测试

创建声明式分区表:

 

  1. CREATE TABLE ptab01 (
  2. id int not null,
  3. tm timestamptz not null
  4. ) PARTITION BY RANGE (tm);
  6. create table ptab01_202001 partition of ptab01 for values from ('2020-01-01') to ('2020-02-01');
  7. create table ptab01_202002 partition of ptab01 for values from ('2020-02-01') to ('2020-03-01');
  8. create table ptab01_202003 partition of ptab01 for values from ('2020-03-01') to ('2020-04-01');
  9. create table ptab01_202004 partition of ptab01 for values from ('2020-04-01') to ('2020-05-01');
  10. create table ptab01_202005 partition of ptab01 for values from ('2020-05-01') to ('2020-06-01');
  12. insert into ptab01 select extract(epoch from seq), seq from generate_series('2020-01-01'::timestamptz, '2020-05-31 23:59:59'::timestamptz, interval '10 seconds') as seq;
 

创建传统的继承式的分区表:
 

 

  3. CREATE TABLE ptab02 (
  4. id int not null,
  5. tm timestamptz not null
  6. );
  9. CREATE TABLE ptab02_202001 (
  10. CHECK ( tm >= '2020-01-01'::timestamptz AND tm < '2020-02-01'::timestamptz )
  11. ) INHERITS (ptab02);
  13. CREATE TABLE ptab02_202002 (
  14. CHECK ( tm >= '2020-02-01'::timestamptz AND tm < '2020-03-01'::timestamptz )
  15. ) INHERITS (ptab02);
  17. CREATE TABLE ptab02_202003 (
  18. CHECK ( tm >= '2020-03-01'::timestamptz AND tm < '2020-04-01'::timestamptz )
  19. ) INHERITS (ptab02);
  21. CREATE TABLE ptab02_202004 (
  22. CHECK ( tm >= '2020-04-01'::timestamptz AND tm < '2020-05-01'::timestamptz )
  23. ) INHERITS (ptab02);
  25. CREATE TABLE ptab02_202005 (
  26. CHECK ( tm >= '2020-05-01'::timestamptz AND tm < '2020-06-01'::timestamptz )
  27. ) INHERITS (ptab02);
  29. CREATE OR REPLACE FUNCTION ptab02_insert_trigger()
  30. RETURNS TRIGGER AS $$
  31. BEGIN
  32. IF NEW.tm >= '2020-01-01'::timestamptz AND NEW.tm < '2020-02-01'::timestamptz THEN
  33. INSERT INTO ptab02_202001 VALUES (NEW.*);
  34. ELSIF NEW.tm >= '2020-02-01'::timestamptz AND NEW.tm < '2020-03-01'::timestamptz THEN
  35. INSERT INTO ptab02_202002 VALUES (NEW.*);
  36. ELSIF NEW.tm >= '2020-02-01'::timestamptz AND NEW.tm < '2020-04-01'::timestamptz THEN
  37. INSERT INTO ptab02_202003 VALUES (NEW.*);
  38. ELSIF NEW.tm >= '2020-02-01'::timestamptz AND NEW.tm < '2020-05-01'::timestamptz THEN
  39. INSERT INTO ptab02_202004 VALUES (NEW.*);
  40. ELSIF NEW.tm >= '2020-02-01'::timestamptz AND NEW.tm < '2020-06-01'::timestamptz THEN
  41. INSERT INTO ptab02_202005 VALUES (NEW.*);
  42. ELSE
  43. RAISE 'value % out of range ', NEW.tm;
  44. END IF;
  45. RETURN NULL;
  46. END;
  47. $$
  48. LANGUAGE plpgsql;
  50. CREATE TRIGGER insert_ptab02_trigger
  51. BEFORE INSERT ON ptab02
  52. FOR EACH ROW EXECUTE FUNCTION ptab02_insert_trigger();
  55. insert into ptab02 select extract(epoch from seq), seq from generate_series('2020-01-01'::timestamptz, '2020-05-31 23:59:59'::timestamptz, interval '10 seconds') as seq;
 

默认情况下constraint_exclusion为partition和enable_partition_pruning为on，不管是表继承方式实现的分区表还是声明式分区表都可以走到分区裁剪，如下所示：
 

 

  1. postgres=# show constraint_exclusion;
  2. constraint_exclusion
  3. ----------------------
  4. partition
  5. (1 row)
  7. postgres=# show enable_partition_pruning;
  8. enable_partition_pruning
  9. --------------------------
  10. on
  11. (1 row)
  12. postgres=# explain select * from ptab01 where tm='2020-01-07'::timestamptz;
  13. QUERY PLAN
  14. ---------------------------------------------------------------------------------------
  15. Gather (cost=1000.00..4417.51 rows=1 width=12)
  16. Workers Planned: 1
  17. -> Parallel Seq Scan on ptab01_202001 ptab01 (cost=0.00..3417.41 rows=1 width=12)
  18. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  19. (4 rows)
  21. postgres=# explain select * from ptab02 where tm='2020-01-07'::timestamptz;
  22. QUERY PLAN
  23. -----------------------------------------------------------------------------------------------
  24. Gather (cost=1000.00..4417.62 rows=2 width=12)
  25. Workers Planned: 2
  26. -> Parallel Append (cost=0.00..3417.42 rows=2 width=12)
  27. -> Parallel Seq Scan on ptab02_202001 ptab02_2 (cost=0.00..3417.41 rows=1 width=12)
  28. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  29. -> Parallel Seq Scan on ptab02 ptab02_1 (cost=0.00..0.00 rows=1 width=12)
  30. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  31. (7 rows)
 

从上面可以看出，声明式分区表只扫描了包括指定时间实际的分区ptab01_202001，没有扫描其他时间段的分区，继承式的分区表只扫描了父表ptab02和包括指定时间实际的分区ptab02_202001。
 

当我们把参数enable_partition_pruning设置为off，这是可以看到查询声明式分区表时，会扫描所有分区，没有进行分区裁剪，但是继承式分区表还是进行了分区裁剪：
 

 

  1. postgres=# set enable_partition_pruning to off;
  2. SET
  3. postgres=# explain select * from ptab01 where tm='2020-01-07'::timestamptz;
  4. QUERY PLAN
  5. -----------------------------------------------------------------------------------------------
  6. Gather (cost=1000.00..17758.00 rows=5 width=12)
  7. Workers Planned: 2
  8. -> Parallel Append (cost=0.00..16757.50 rows=5 width=12)
  9. -> Parallel Seq Scan on ptab01_202001 ptab01_1 (cost=0.00..3417.41 rows=1 width=12)
  10. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  11. -> Parallel Seq Scan on ptab01_202003 ptab01_3 (cost=0.00..3417.41 rows=1 width=12)
  12. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  13. -> Parallel Seq Scan on ptab01_202005 ptab01_5 (cost=0.00..3417.41 rows=1 width=12)
  14. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  15. -> Parallel Seq Scan on ptab01_202004 ptab01_4 (cost=0.00..3307.88 rows=1 width=12)
  16. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  17. -> Parallel Seq Scan on ptab01_202002 ptab01_2 (cost=0.00..3197.35 rows=1 width=12)
  18. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  19. (13 rows)
  21. postgres=# explain select * from ptab02 where tm='2020-01-07'::timestamptz;
  22. QUERY PLAN
  23. -----------------------------------------------------------------------------------------------
  24. Gather (cost=1000.00..4417.62 rows=2 width=12)
  25. Workers Planned: 2
  26. -> Parallel Append (cost=0.00..3417.42 rows=2 width=12)
  27. -> Parallel Seq Scan on ptab02_202001 ptab02_2 (cost=0.00..3417.41 rows=1 width=12)
  28. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  29. -> Parallel Seq Scan on ptab02 ptab02_1 (cost=0.00..0.00 rows=1 width=12)
  30. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  31. (7 rows)
 

当我们把参数constraint_exclusion设置为off，这是可以看到继承式分区表就没有再做分区裁剪了：
 

 

  1. postgres=# set constraint_exclusion to off;
  2. SET
  3. postgres=# explain select * from ptab02 where tm='2020-01-07'::timestamptz;
  4. QUERY PLAN
  5. -----------------------------------------------------------------------------------------------
  6. Gather (cost=1000.00..17758.10 rows=6 width=12)
  7. Workers Planned: 2
  8. -> Parallel Append (cost=0.00..16757.50 rows=6 width=12)
  9. -> Parallel Seq Scan on ptab02_202001 ptab02_2 (cost=0.00..3417.41 rows=1 width=12)
  10. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  11. -> Parallel Seq Scan on ptab02_202003 ptab02_4 (cost=0.00..3417.41 rows=1 width=12)
  12. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  13. -> Parallel Seq Scan on ptab02_202005 ptab02_6 (cost=0.00..3417.41 rows=1 width=12)
  14. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  15. -> Parallel Seq Scan on ptab02_202004 ptab02_5 (cost=0.00..3307.88 rows=1 width=12)
  16. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  17. -> Parallel Seq Scan on ptab02_202002 ptab02_3 (cost=0.00..3197.35 rows=1 width=12)
  18. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  19. -> Parallel Seq Scan on ptab02 ptab02_1 (cost=0.00..0.00 rows=1 width=12)
  20. Filter: (tm = '2020-01-07 00:00:00+08'::timestamp with time zone)
  21. (15 rows)
 

由此可见：
 

  • enable_partition_pruning：只控制声明式分区表的分区裁剪，对继承式分区表没有影响。
  • constraint_exclusion：只控制继承式分区表的分区裁剪，对声明式分区表没有影响。
 

4. 区别
 

通常声明式分区比继承式分区表在更多的情况下能走到分区裁剪，如在select * from tab01 where tm = (select tm from tab02);类似的SQL中：
 

 

  1. postgres=# explain analyze select * from ptab01 where tm = (select tm from tabtm);
  2. QUERY PLAN
  3. ------------------------------------------------------------------------------------------------------------------------------------------
  4. Gather (cost=1001.01..17759.01 rows=5 width=12) (actual time=4.154..27.762 rows=1 loops=1)
  5. Workers Planned: 2
  6. Params Evaluated: $0
  7. Workers Launched: 2
  8. InitPlan 1 (returns $0)
  9. -> Seq Scan on tabtm (cost=0.00..1.01 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
  10. -> Parallel Append (cost=0.00..16757.50 rows=5 width=12) (actual time=1.228..6.338 rows=0 loops=3)
  11. -> Parallel Seq Scan on ptab01_202001 ptab01_1 (cost=0.00..3417.41 rows=1 width=12) (actual time=3.652..18.978 rows=1 loops=1)
  12. Filter: (tm = $0)
  13. Rows Removed by Filter: 267839
  14. -> Parallel Seq Scan on ptab01_202003 ptab01_3 (cost=0.00..3417.41 rows=1 width=12) (never executed)
  15. Filter: (tm = $0)
  16. -> Parallel Seq Scan on ptab01_202005 ptab01_5 (cost=0.00..3417.41 rows=1 width=12) (never executed)
  17. Filter: (tm = $0)
  18. -> Parallel Seq Scan on ptab01_202004 ptab01_4 (cost=0.00..3307.88 rows=1 width=12) (never executed)
  19. Filter: (tm = $0)
  20. -> Parallel Seq Scan on ptab01_202002 ptab01_2 (cost=0.00..3197.35 rows=1 width=12) (never executed)
  21. Filter: (tm = $0)
  22. Planning Time: 0.121 ms
  23. Execution Time: 27.858 ms
  24. (20 rows)
  26. postgres=# explain analyze select * from ptab02 where tm = (select tm from tabtm);
  27. QUERY PLAN
  28. -------------------------------------------------------------------------------------------------------------------------------------------
  29. Gather (cost=1001.01..17759.11 rows=6 width=12) (actual time=107.141..107.310 rows=1 loops=1)
  30. Workers Planned: 2
  31. Params Evaluated: $0
  32. Workers Launched: 2
  33. InitPlan 1 (returns $0)
  34. -> Seq Scan on tabtm (cost=0.00..1.01 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)
  35. -> Parallel Append (cost=0.00..16757.50 rows=6 width=12) (actual time=59.127..81.720 rows=0 loops=3)
  36. -> Parallel Seq Scan on ptab02_202001 ptab02_2 (cost=0.00..3417.41 rows=1 width=12) (actual time=5.747..39.632 rows=0 loops=2)
  37. Filter: (tm = $0)
  38. Rows Removed by Filter: 133920
  39. -> Parallel Seq Scan on ptab02_202003 ptab02_4 (cost=0.00..3417.41 rows=1 width=12) (actual time=45.304..45.304 rows=0 loops=1)
  40. Filter: (tm = $0)
  41. Rows Removed by Filter: 267840
  42. -> Parallel Seq Scan on ptab02_202005 ptab02_6 (cost=0.00..3417.41 rows=1 width=12) (actual time=21.245..21.245 rows=0 loops=2)
  43. Filter: (tm = $0)
  44. Rows Removed by Filter: 133920
  45. -> Parallel Seq Scan on ptab02_202004 ptab02_5 (cost=0.00..3307.88 rows=1 width=12) (actual time=60.385..60.385 rows=0 loops=1)
  46. Filter: (tm = $0)
  47. Rows Removed by Filter: 259200
  48. -> Parallel Seq Scan on ptab02_202002 ptab02_3 (cost=0.00..3197.35 rows=1 width=12) (actual time=17.671..17.671 rows=0 loops=1)
  49. Filter: (tm = $0)
  50. Rows Removed by Filter: 250560
  51. -> Parallel Seq Scan on ptab02 ptab02_1 (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.001 rows=0 loops=1)
  52. Filter: (tm = $0)
  53. Planning Time: 0.171 ms
  54. Execution Time: 107.431 ms
  55. (26 rows)
 

从上面可以看到声明式分区可以做动态的分区裁剪，不需要扫描的分区后面会有“(never executed)”的提示，表示把这些分区表都跳过了。

                                    

                                        这篇关于关于PostgreSQL的分区表的历史及分区裁剪参数enable_partition_pruning与constraint_exclusion的区别的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
                                    

                                    

                                

                            

                            

                        

                    

                    

                        

                            

                            http://www.chinasem.cn/article/1116245。
                            
                        

                    

                    
                    

                        
                            
                        
                    

                

                

                    相关文章
                

                    

                        

                            

                                

                                    
                                        Android使用ImageView.ScaleType实现图片的缩放与裁剪功能
                                    
                                

                            

                            

                                

                                    Android使用ImageView.ScaleType实现图片的缩放与裁剪功能
                                

                                

                                    《Android使用ImageView.ScaleType实现图片的缩放与裁剪功能》ImageView是最常用的控件之一,它用于展示各种类型的图片,为了能够根据需求调整图片的显示效果,Android提...																									目录什么是 ImageView.ScaleType？FIT_XYFIT_STARTFIT_CENTE
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        SpringBoot请求参数接收控制指南分享
                                    
                                

                            

                            

                                

                                    SpringBoot请求参数接收控制指南分享
                                

                                

                                    《SpringBoot请求参数接收控制指南分享》：本文主要介绍SpringBoot请求参数接收控制指南,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教...																									目录Spring Boot 请求参数接收控制指南1. 概述2. 有注解时参数接收方式对比3. 无注解时接收参数默认位置
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        最详细安装 PostgreSQL方法及常见问题解决
                                    
                                

                            

                            

                                

                                    最详细安装 PostgreSQL方法及常见问题解决
                                

                                

                                    《最详细安装PostgreSQL方法及常见问题解决》：本文主要介绍最详细安装PostgreSQL方法及常见问题解决,介绍了在Windows系统上安装PostgreSQL及Linux系统上安装Po...																									目录一、在 Windows 系统上安装 PostgreSQL1. 下载 PostgreSQL 安装包2.
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Python使用getopt处理命令行参数示例解析(最佳实践)
                                    
                                

                            

                            

                                

                                    Python使用getopt处理命令行参数示例解析(最佳实践)
                                

                                

                                    《Python使用getopt处理命令行参数示例解析(最佳实践)》getopt模块是Python标准库中一个简单但强大的命令行参数处理工具,它特别适合那些需要快速实现基本命令行参数解析的场景,或者需要...																									目录为什么需要处理命令行参数？getopt模块基础实际应用示例与其他参数处理方式的比较常见问http
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Linux内核参数配置与验证详细指南
                                    
                                

                            

                            

                                

                                    Linux内核参数配置与验证详细指南
                                

                                

                                    《Linux内核参数配置与验证详细指南》在Linux系统运维和性能优化中,内核参数（sysctl）的配置至关重要,本文主要来聊聊如何配置与验证这些Linux内核参数,希望对大家有一定的帮助...																									目录1. 引言2. 内核参数的作用3. 如何设置内核参数3.1 临时设置（重启失效）3.2 永久设置（重启仍生效
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        go 指针接收者和值接收者的区别小结
                                    
                                

                            

                            

                                

                                    go 指针接收者和值接收者的区别小结
                                

                                

                                    《go指针接收者和值接收者的区别小结》在Go语言中,值接收者和指针接收者是方法定义中的两种接收者类型,本文主要介绍了go指针接收者和值接收者的区别小结,文中通过示例代码介绍的非常详细,需要的朋友们下...																									目录go 指针接收者和值接收者的区别易错点辨析go 指针接收者和值接收者的区别指针接收者和值接收者的
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        售价599元起! 华为路由器X1/Pro发布 配置与区别一览
                                    
                                

                            

                            

                                

                                    售价599元起! 华为路由器X1/Pro发布 配置与区别一览
                                

                                

                                    《售价599元起!华为路由器X1/Pro发布配置与区别一览》华为路由器X1/Pro发布，有朋友留言问华为路由X1和X1Pro怎么选择，关于这个问题，本期图文将对这二款路由器做了期参数对比，大家看...																									华为路由 X1 系列已经正式发布并开启预售，将在 4 月 25 日 10:08 正式开售，两款产品分别为华
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        SpringMVC获取请求参数的方法
                                    
                                

                            

                            

                                

                                    SpringMVC获取请求参数的方法
                                

                                

                                    《SpringMVC获取请求参数的方法》：本文主要介绍SpringMVC获取请求参数的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...																									目录1、通过ServletAPI获取2、通过控制器方法的形参获取请求参数3、@RequestParam4、@
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        MySQL 分区与分库分表策略应用小结
                                    
                                

                            

                            

                                

                                    MySQL 分区与分库分表策略应用小结
                                

                                

                                    《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如...																									目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念
                                

                                

                                    阅读更多...
                                

                            

                        

                    

                    

                        

                            

                                

                                    
                                        Mysql表如何按照日期字段的年月分区
                                    
                                

                            

                            

                                

                                    Mysql表如何按照日期字段的年月分区
                                

                                

                                    《Mysql表如何按照日期字段的年月分区》：本文主要介绍Mysql表如何按照日期字段的年月分区的实现方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教...																									目录一、创键表时直接设置分区二、已有表分区1、分区的前置条件2、分区操作三、验证四、注意总结一、创键表时直接设置分区
                                

                                

                                    阅读更多...
                                

                            

                        

                    

            

        

    


    


    

        

            Copyright China编程   23002807@qq.com
        

        

            沪ICP备2023033072号-2