PostgreSQL中的partition-wise aggregation

partition-wise aggregation允许对每个分区分别执行的分区表进行分组或聚合。如果GROUP BY子句不包括分区键，则只能在每个分区的基础上执行部分聚合，并且必须稍后执行最终处理。由于partitionwise分组或聚合可能在计划期间占用大量CPU时间和内存，因此默认设置为关闭。

通过变量enable_partitionwise_aggregate控制是否启用该特性。

 

创建一个分区表，用于测试：

?

1 2 3 4 5 6

create table pagg_t (a int, b int, c text, d int) partition by list(c); create table pagg_t_p1 partition of pagg_t for values in ('000', '001', '002', '003'); create table pagg_t_p2 partition of pagg_t for values in ('004', '005', '006', '007'); create table pagg_t_p3 partition of pagg_t for values in ('008', '009', '010', '011'); insert into pagg_t select i % 20, i % 30, to_char(i % 12, 'fm0000'), i % 30 from generate_series(0, 2999) i; analyze pagg_t;

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

postgres=# show enable_partitionwise_aggregate;  off   postgres=# explain (costs off) select c, sum(a), avg(b), count(*), min(a), max(b) from pagg_t group by c having avg(d) < 15 order by 1, 2, 3;  Sort    Sort Key: pagg_t_p1.c, (sum(pagg_t_p1.a)), (avg(pagg_t_p1.b))    ->  HashAggregate          Group Key: pagg_t_p1.c          Filter: (avg(pagg_t_p1.d) < '15'::numeric)          ->  Append                ->  Seq Scan on pagg_t_p1                ->  Seq Scan on pagg_t_p2                ->  Seq Scan on pagg_t_p3   postgres=#

默认情况下，需要先分别扫描表的所有分区，将分区结果整合在一起（Append），然后执行哈希聚合（HashAggregate），最后进行排序（Sort）。

启用智能分区聚合功能，查看相同的聚合操作：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

postgres=# set enable_partitionwise_aggregate=on; SET postgres=# show enable_partitionwise_aggregate;  on   postgres=# explain (costs off) select c, sum(a), avg(b), count(*), min(a), max(b) from pagg_t group by c having avg(d) < 15 order by 1, 2, 3;  Sort    Sort Key: pagg_t_p1.c, (sum(pagg_t_p1.a)), (avg(pagg_t_p1.b))    ->  Append          ->  HashAggregate                Group Key: pagg_t_p1.c                Filter: (avg(pagg_t_p1.d) < '15'::numeric)                ->  Seq Scan on pagg_t_p1          ->  HashAggregate                Group Key: pagg_t_p2.c                Filter: (avg(pagg_t_p2.d) < '15'::numeric)                ->  Seq Scan on pagg_t_p2          ->  HashAggregate                Group Key: pagg_t_p3.c                Filter: (avg(pagg_t_p3.d) < '15'::numeric)                ->  Seq Scan on pagg_t_p3   postgres=#

可以看到，启用该功能之后，先针对表中的所有分区执行哈希聚合（HashAggregate），然后将结果整合在一起（Append），最后进行排序（Sort）。其中，分区级别的聚合可以并行执行，性能会更好。

如果GROUP BY子句中没有包含分区字段，只会基于分区执行部分聚合操作，然后再对结果进行一次最终的聚合。

以下查询使用字段 a 进行分组聚合：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

postgres=# explain (costs off) select a, sum(b), avg(b), count(*), min(a), max(b) from pagg_t group by a having avg(d) < 15 order by 1, 2, 3;  Sort    Sort Key: pagg_t_p1.a, (sum(pagg_t_p1.b)), (avg(pagg_t_p1.b))    ->  Finalize HashAggregate          Group Key: pagg_t_p1.a          Filter: (avg(pagg_t_p1.d) < '15'::numeric)          ->  Append                ->  Partial HashAggregate                      Group Key: pagg_t_p1.a                      ->  Seq Scan on pagg_t_p1                ->  Partial HashAggregate                      Group Key: pagg_t_p2.a                      ->  Seq Scan on pagg_t_p2                ->  Partial HashAggregate                      Group Key: pagg_t_p3.a                      ->  Seq Scan on pagg_t_p3   postgres=#

由于字段 a 不是分区键，所以先执行分区级别的部分哈希聚合（Partial HashAggregate），聚合的结果中可能存在相同的分组（不同分区中的字段 a 存在相同的值），需要执行最终的哈希聚合（Finalize HashAggregate）操作。