个人中心
退出
PotgreSQL 11 新特性之智能分区连接/聚合
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
智能分区连接
智能分区聚合
PostgreSQL 11 提供了两个分区级别的新功能：
智能分区连接（partition-wise join），对于两个分区方式相同的分区表，如果使用分区键进行等值连接，允许使用匹配的分区直接进行连接操作。
智能分区聚合（partition-wise aggregation），对于分区表的聚合操作，如果 GROUP BY 中包含了分区键，允许针对各个分区进行并行的聚合操作，然后再合并结果。
智能分区连接
PostgreSQL 11 增加了一个新的选项：enable_partitionwise_join，用于控制查询计划器是否使用分区级别的连接。默认值为 off。
首先，创建两个分区表，它们使用相同的分区方式：
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 2 = 0;
CREATE INDEX iprt1_p1_a on prt1_p1(a);
CREATE INDEX iprt1_p2_a on prt1_p2(a);
CREATE INDEX iprt1_p3_a on prt1_p3(a);
ANALYZE prt1;
CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 3 = 0;
CREATE INDEX iprt2_p1_b on prt2_p1(b);
CREATE INDEX iprt2_p2_b on prt2_p2(b);
CREATE INDEX iprt2_p3_b on prt2_p3(b);
ANALYZE prt2;
查看默认情况下，两个表的连接操作：
show enable_partitionwise_join;
enable_partitionwise_join
---------------------------
off
(1 row)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
--------------------------------------------------
Sort
Sort Key: t1.a
-> Hash Join
Hash Cond: (t2.b = t1.a)
-> Append
-> Seq Scan on prt2_p1 t2
-> Seq Scan on prt2_p2 t2_1
-> Seq Scan on prt2_p3 t2_2
-> Hash
-> Append
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
-> Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Seq Scan on prt1_p3 t1_2
Filter: (b = 0)
(16 rows)
默认情况下，需要先分别扫描两个表的所有分区，将分区结果分别整合在一起（Append），然后执行两个表的哈希连接（Hash Join），最后进行排序（Sort）。
接下来，启用智能分区连接功能，即将参数 enable_partitionwise_join 设置为 on。再次查看相同的连接操作：
set enable_partitionwise_join to true;
SET
rgsdb=# show enable_partitionwise_join;
enable_partitionwise_join
---------------------------
on
(1 row)
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
QUERY PLAN
--------------------------------------------------
Sort
Sort Key: t1.a
-> Append
-> Hash Join
Hash Cond: (t2.b = t1.a)
-> Seq Scan on prt2_p1 t2
-> Hash
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Seq Scan on prt2_p2 t2_1
-> Hash
-> Seq Scan on prt1_p2 t1_1
Filter: (b = 0)
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_p3 t2_2
-> Hash
-> Seq Scan on prt1_p3 t1_2
Filter: (b = 0)
(21 rows)
可以看到，启用该功能之后，先针对两个表中的相应分区执行哈希连接（Hash Join），然后将结果整合在一起（Append），最后进行排序（Sort）。其中，分区级别的哈希连接可以并行执行，而且分区中的数据量比整个表少，性能会更好。
目前，智能分区连接要求连接条件中必须包含所有的分区字段，它们的数据类型必须相同，并且分区方式一致。由于智能分区连接的计划阶段需要占用更多的 CPU 时间和内存，默认没有启用该功能。
智能分区聚合
智能分区聚合（分组）使用选项 enable_partitionwise_aggregate 进行控制。默认值为 off。
首先，创建一个分区表：
CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c);
CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003');
CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '0005', '0006', '0007');
CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '0009', '0010', '0011');
INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i;
ANALYZE pagg_tab;
默认情况下的聚合操作：
show enable_partitionwise_aggregate;
enable_partitionwise_aggregate
--------------------------------
off
(1 row)
EXPLAIN (COSTS OFF)
SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
QUERY PLAN
-----------------------------------------------------------------------
Sort
Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b))
-> HashAggregate
Group Key: pagg_tab_p1.c
Filter: (avg(pagg_tab_p1.d) < '15'::numeric)
-> Append
-> Seq Scan on pagg_tab_p1
-> Seq Scan on pagg_tab_p2
-> Seq Scan on pagg_tab_p3
(9 rows)
默认情况下，需要先分别扫描表的所有分区，将分区结果整合在一起（Append），然后执行哈希聚合（HashAggregate），最后进行排序（Sort）。
启用智能分区聚合功能，查看相同的聚合操作：
SET enable_partitionwise_aggregate TO true;
SET
EXPLAIN (COSTS OFF)
SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
QUERY PLAN
-----------------------------------------------------------------------
Sort
Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b))
-> Append
-> HashAggregate
Group Key: pagg_tab_p1.c
Filter: (avg(pagg_tab_p1.d) < '15'::numeric)
-> Seq Scan on pagg_tab_p1
-> HashAggregate
Group Key: pagg_tab_p2.c
Filter: (avg(pagg_tab_p2.d) < '15'::numeric)
-> Seq Scan on pagg_tab_p2
-> HashAggregate
Group Key: pagg_tab_p3.c
Filter: (avg(pagg_tab_p3.d) < '15'::numeric)
-> Seq Scan on pagg_tab_p3
(15 rows)
可以看到，启用该功能之后，先针对表中的所有分区执行哈希聚合（HashAggregate），然后将结果整合在一起（Append），最后进行排序（Sort）。其中，分区级别的聚合可以并行执行，性能会更好。
如果 GROUP BY 子句中没有包含分区字段，只会基于分区执行部分聚合操作，然后再对结果进行一次最终的聚合。
以下查询使用字段 a 进行分组聚合：
EXPLAIN (COSTS OFF)
SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3;
QUERY PLAN
-----------------------------------------------------------------------
Sort
Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b)), (avg(pagg_tab_p1.b))
-> Finalize HashAggregate
Group Key: pagg_tab_p1.a
Filter: (avg(pagg_tab_p1.d) < '15'::numeric)
-> Append
-> Partial HashAggregate
Group Key: pagg_tab_p1.a
-> Seq Scan on pagg_tab_p1
-> Partial HashAggregate
Group Key: pagg_tab_p2.a
-> Seq Scan on pagg_tab_p2
-> Partial HashAggregate
Group Key: pagg_tab_p3.a
-> Seq Scan on pagg_tab_p3
(15 rows)
由于字段 a 不是分区键，所以先执行分区级别的部分哈希聚合（Partial HashAggregate），聚合的结果中可能存在相同的分组（不同分区中的字段 a 存在相同的值），需要执行最终的哈希聚合（Finalize HashAggregate）操作。
由于智能分区聚合（分组）的计划阶段需要占用更多的 CPU 时间和内存，默认没有启用该功能。
可以同时启用智能分区连接与智能分区聚合功能。首先，创建两个分区表：
CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x);
CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10);
CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20);
CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30);
CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y);
CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10);
CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20);
CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30);
INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i;
INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i;
ANALYZE pagg_tab1;
ANALYZE pagg_tab2;
使用分区字段作为连接条件，同时使用分区字段进行分组聚合：
SET enable_partitionwise_join TO true;
SET enable_partitionwise_aggregate TO true;
EXPLAIN (COSTS OFF)
rgsdb-# SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3;
QUERY PLAN
-------------------------------------------------------------
Sort
Sort Key: t1.x, (sum(t1.y)), (count(*))
-> Append
-> HashAggregate
Group Key: t1.x
-> Hash Join
Hash Cond: (t1.x = t2.y)
-> Seq Scan on pagg_tab1_p1 t1
-> Hash
-> Seq Scan on pagg_tab2_p1 t2
-> HashAggregate
Group Key: t1_1.x
-> Hash Join
Hash Cond: (t1_1.x = t2_1.y)
-> Seq Scan on pagg_tab1_p2 t1_1
-> Hash
-> Seq Scan on pagg_tab2_p2 t2_1
-> HashAggregate
Group Key: t1_2.x
-> Hash Join
Hash Cond: (t2_2.y = t1_2.x)
-> Seq Scan on pagg_tab2_p3 t2_2
-> Hash
-> Seq Scan on pagg_tab1_p3 t1_2
(24 rows)
执行计划先针对两个表中的相应分区执行哈希连接（Hash Join），然后基于连接结果执行分区级别的哈希聚合（HashAggregate），将结果整合在一起（Append），最后进行排序（Sort）。分区级别的哈希连接以及分区级别的聚合可以并行执行。
另外，PostgreSQL 11 还支持 postgres_fdw 外部分区的聚合操作下推，即将聚合操作下推至外部表执行。同时还支持 postgres_fdw 外部分区上的 INSERT、UPDATE、COPY 操作。
参考：
Basic partition-wise join functionality.
Implement partition-wise grouping/aggregation.
分类导航