您的位置：首页 > 文旅 > 美景 > 关于PostgreSQL的分区表的历史及分区裁剪参数enable_partition_pruning与constraint_exclusion的区别

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

2024/12/23 8:08:50 来源：https://blog.csdn.net/forrest_hu/article/details/141645742  浏览:  次  关键词：关于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)”的提示，表示把这些分区表都跳过了。








版权声明:



本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。


我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com














最新新闻







热搜词



IBM Power Cloud Box案例解读
各个省市mysql表附带行政id(一)
Q1. 使数组元素互不相同所需的最少操作次数
CISCN  CCB crypto
56. 合并区间
2024/12/22

















声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！


客户服务 | 关于我们  | 版权声明


版权所有：


Copyright  2024 尧图网  All Rights Reserved.QQ:809451989