PostgreSQL 11 新特性之分区裁剪增强

文章目录

在之前的版本中，只在查询的计划阶段执行分区排除操作（通过 constraint_exclusion 变量控制），意味着许多连接查询和预编译查询无法使用分区排除。另外，这种方法占用的时间会随着分区的数量线性增长。

PostgreSQL 11 通过两个方面的改进提供了更加强大且快速的分区裁剪功能：

    查询计划阶段更快的分区排除，可以提高分区表（尤其是包含许多分区的分区表）的访问性能。
    支持执行阶段的分区排除。

分区裁剪使用选项 enable_partition_pruning 进行控制。该参数默认值为 on。

show enable_partition_pruning;
 enable_partition_pruning
--------------------------
 on
(1 row)

CREATE TABLE rtable(c1 INT, c2 VARCHAR(10)) PARTITION BY RANGE(c1);
CREATE TABLE rtable100 PARTITION OF rtable FOR VALUES FROM (1) TO (100);
CREATE TABLE rtable200 PARTITION OF rtable FOR VALUES FROM (101) TO (200);
CREATE TABLE rtable300 PARTITION OF rtable FOR VALUES FROM (201) TO (300);
CREATE TABLE rtable400 PARTITION OF rtable FOR VALUES FROM (301) TO (400);

explain analyze select * from rtable where c1=256;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..24.53 rows=6 width=42) (actual time=0.009..0.009 rows=0 loops=1)
   ->  Seq Scan on rtable300  (cost=0.00..24.50 rows=6 width=42) (actual time=0.007..0.007 rows=0 loops=1)
         Filter: (c1 = 256)
 Planning Time: 0.397 ms
 Execution Time: 0.042 ms
(5 rows)

如果将该参数设置为 off，将会禁用分区裁剪功能：

set enable_partition_pruning=off;
explain analyze select * from rtable where c1=256;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..98.12 rows=24 width=42) (actual time=0.015..0.015 rows=0 loops=1)
   ->  Seq Scan on rtable100  (cost=0.00..24.50 rows=6 width=42) (actual time=0.007..0.007 rows=0 loops=1)
         Filter: (c1 = 256)
   ->  Seq Scan on rtable200  (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (c1 = 256)
   ->  Seq Scan on rtable300  (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (c1 = 256)
   ->  Seq Scan on rtable400  (cost=0.00..24.50 rows=6 width=42) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (c1 = 256)
 Planning Time: 0.246 ms
 Execution Time: 0.167 ms
(11 rows)

    注意 Currently, pruning of partitions during the planning of an UPDATE
    目前，对于 UPDATE 和 DELETE 语句，计划阶段的分区裁剪基于之前的约束排除方法实现（但是，该功能使用 enable_partition_pruning 选项控制，而不是 constraint_exclusion 选项）。
    另外，执行阶段的分区裁剪目前支持 Append 节点类型，而不支持 MergeAppend 或者 ModifyTable（UPDATE 或者 DELETE）。
    这些行为很可能在将来的 PostgreSQL 版本中进行修改。

在 PostgreSQL 11 中，查询计划阶段的分区排除使用二分查找法搜索匹配的分区（LIST 分区表和 RANGE 分区表）；对于哈希分区表，使用哈希函数查找匹配的分区。但是，对于 UPDATE/DELETE 语句，仍然使用约束排除的方法。

更多请见：http://www.mark-to-win.com/tutorial/51636.html