個人中心
退出
PotgreSQL 11 新特性之智能分區(qū)連接/聚合
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
智能分區(qū)連接
智能分區(qū)聚合
PostgreSQL 11 提供了兩個分區(qū)級別的新功能：
智能分區(qū)連接（partition-wise join），對于兩個分區(qū)方式相同的分區(qū)表，如果使用分區(qū)鍵進行等值連接，允許使用匹配的分區(qū)直接進行連接操作。
智能分區(qū)聚合（partition-wise aggregation），對于分區(qū)表的聚合操作，如果 GROUP BY 中包含了分區(qū)鍵，允許針對各個分區(qū)進行并行的聚合操作，然后再合并結(jié)果。
智能分區(qū)連接
PostgreSQL 11 增加了一個新的選項：enable_partitionwise_join，用于控制查詢計劃器是否使用分區(qū)級別的連接。默認(rèn)值為 off。
首先，創(chuàng)建兩個分區(qū)表，它們使用相同的分區(qū)方式：
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;
查看默認(rèn)情況下，兩個表的連接操作：
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)
默認(rèn)情況下，需要先分別掃描兩個表的所有分區(qū)，將分區(qū)結(jié)果分別整合在一起（Append），然后執(zhí)行兩個表的哈希連接（Hash Join），最后進行排序（Sort）。
接下來，啟用智能分區(qū)連接功能，即將參數(shù) enable_partitionwise_join 設(shè)置為 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)
可以看到，啟用該功能之后，先針對兩個表中的相應(yīng)分區(qū)執(zhí)行哈希連接（Hash Join），然后將結(jié)果整合在一起（Append），最后進行排序（Sort）。其中，分區(qū)級別的哈希連接可以并行執(zhí)行，而且分區(qū)中的數(shù)據(jù)量比整個表少，性能會更好。
目前，智能分區(qū)連接要求連接條件中必須包含所有的分區(qū)字段，它們的數(shù)據(jù)類型必須相同，并且分區(qū)方式一致。由于智能分區(qū)連接的計劃階段需要占用更多的 CPU 時間和內(nèi)存，默認(rèn)沒有啟用該功能。
智能分區(qū)聚合
智能分區(qū)聚合（分組）使用選項 enable_partitionwise_aggregate 進行控制。默認(rèn)值為 off。
首先，創(chuàng)建一個分區(qū)表：
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;
默認(rèn)情況下的聚合操作：
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)
默認(rèn)情況下，需要先分別掃描表的所有分區(qū)，將分區(qū)結(jié)果整合在一起（Append），然后執(zhí)行哈希聚合（HashAggregate），最后進行排序（Sort）。
啟用智能分區(qū)聚合功能，查看相同的聚合操作：
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)
可以看到，啟用該功能之后，先針對表中的所有分區(qū)執(zhí)行哈希聚合（HashAggregate），然后將結(jié)果整合在一起（Append），最后進行排序（Sort）。其中，分區(qū)級別的聚合可以并行執(zhí)行，性能會更好。
如果 GROUP BY 子句中沒有包含分區(qū)字段，只會基于分區(qū)執(zhí)行部分聚合操作，然后再對結(jié)果進行一次最終的聚合。
以下查詢使用字段 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 不是分區(qū)鍵，所以先執(zhí)行分區(qū)級別的部分哈希聚合（Partial HashAggregate），聚合的結(jié)果中可能存在相同的分組（不同分區(qū)中的字段 a 存在相同的值），需要執(zhí)行最終的哈希聚合（Finalize HashAggregate）操作。
由于智能分區(qū)聚合（分組）的計劃階段需要占用更多的 CPU 時間和內(nèi)存，默認(rèn)沒有啟用該功能。
可以同時啟用智能分區(qū)連接與智能分區(qū)聚合功能。首先，創(chuàng)建兩個分區(qū)表：
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;
使用分區(qū)字段作為連接條件，同時使用分區(qū)字段進行分組聚合：
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)
執(zhí)行計劃先針對兩個表中的相應(yīng)分區(qū)執(zhí)行哈希連接（Hash Join），然后基于連接結(jié)果執(zhí)行分區(qū)級別的哈希聚合（HashAggregate），將結(jié)果整合在一起（Append），最后進行排序（Sort）。分區(qū)級別的哈希連接以及分區(qū)級別的聚合可以并行執(zhí)行。
另外，PostgreSQL 11 還支持 postgres_fdw 外部分區(qū)的聚合操作下推，即將聚合操作下推至外部表執(zhí)行。同時還支持 postgres_fdw 外部分區(qū)上的 INSERT、UPDATE、COPY 操作。
參考：
Basic partition-wise join functionality.
Implement partition-wise grouping/aggregation.
分類導(dǎo)航