PostgreSQL16-新特性-并行聚合

1、并行聚合

PG中并行聚合指：以并行和分布式的方式处理大数据集合的聚合函数（比如SUM、AVG、MAX、MIN等），从而是查询执行速度更快。其实和GreenPlum的分阶段聚合有些类似。

执行聚合查询时，数据库系统自动将结果集分解成更小的部分，并在可用资源之间分配工作，然后组合结果以产生最终输出。这种方法可以显着提高大型数据集聚合查询的性能，但它需要足够的资源并且可能并不总是比串行执行更快。

并不是所有聚合函数都支持所谓的“Partial Mode”，这表示聚合有资格参与各种优化，例如并行聚合。array_agg()和string_agg()函数支持并行聚合。第一个函数采集所有输入值包括NULL到一个数组中，第二个将非NULL输入值连接成一个字符串。我们先看下这2个函数语法及执行效果：

string_agg(expression,delimiter)

string_agg函数能将结果集某个字段的所有行连接成字符串，并以delimiter分隔符分隔，expression表示要处理的字符类型数据。

create table t1(id1 int, id2 varchar(64));
insert into t1 values(1,’nanjing’),(1,’suzhou’),(2,’xingtai’),(2,’shijiazhuang’);
select id1,string_agg(id2,’,’) group by id1;
id1 |   string_agg
------------------------------------
1  | nanjing,suzhou
2  | xingtai,shijiazhuang

array_agg函数和string_agg函数类似，最主要的区别为返回的类型为数组，数组数据类型同输入数据类型一致。

select id1,array_agg(id2) group by id1;
id1 |   array_agg
---------------------------------------
1  | {nanjing,suzhou}
2  | {xingtai,shijiazhuang}

2、PG16中有什么新功能？

David Rowley 为string_agg()和array_agg()函数实现了并行聚合的新功能。该补丁由 Andres Freund、Tomas Vondra、Stephen Frost 和 Tom Lane 审核。由David Rowley提交。提交消息是：

This adds combine, serial and deserial functions for the array_agg() and

string_agg() aggregate functions, thus allowing these aggregates to

partake in partial aggregations. This allows both parallel aggregation to

take place when these aggregates are present and also allows additional

partition-wise aggregation plan shapes to include plans that require

additional aggregation once the partially aggregated results from the

partitions have been combined.

Author: David Rowley

Reviewed-by: Andres Freund, Tomas Vondra, Stephen Frost, Tom Lane

Discussion: https://postgr.es/m/CAKJS1f9sx_6GTcvd6TMuZnNtCh0VhBzhX6FZqw17TgVFH-ga_A@mail.gmail.com

3、并行聚合的效果

test=# select version();
version                                                  
----------------------------------------------------------------------------------------------------------
PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
test=# create table pagg_test (x int, y int);
CREATE TABLE
test=# insert into pagg_test
test-# select (case x % 4 when 1 then null else x end), x % 10
test-# from generate_series(1,5000) x;
INSERT 0 5000
test=# set parallel_setup_cost TO 0;
SET
test=# set parallel_tuple_cost TO 0;
SET
test=# set parallel_leader_participation TO 0;
SET
test=# set min_parallel_table_scan_size = 0;
SET
test=# explain select
test-# y,
test-# string_agg(x::text, ',') AS t,
test-# string_agg(x::text::bytea, ',') AS b,
test-# array_agg(x) AS a,
test-# array_agg(ARRAY[x]) AS aa
test-# from pagg_test
test-# group by y;QUERY PLAN                                          
----------------------------------------------------------------------------------------------Finalize GroupAggregate  (cost=117.14..118.02 rows=10 width=132)Group Key: y->  Gather Merge  (cost=117.14..117.37 rows=20 width=132)Workers Planned: 2->  Sort  (cost=117.12..117.14 rows=10 width=132)Sort Key: y->  Partial HashAggregate  (cost=116.75..116.95 rows=10 width=132)Group Key: y->  Parallel Seq Scan on pagg_test  (cost=0.00..48.00 rows=2500 width=8)
(9 rows)

我们在 PostreSQL 13 集群上尝试相同的测试。PostgreSQL 14 和 15 的输出EXPLAIN相同。

...QUERY PLAN
----------------------------------------------------------------------------------HashAggregate  (cost=185.50..185.70 rows=10 width=132)Group Key: y->  Gather  (cost=0.00..48.00 rows=5000 width=8)Workers Planned: 2->  Parallel Seq Scan on pagg_test  (cost=0.00..48.00 rows=2500 width=8)
(5 rows)

我们无法比较时间，但我们可以比较成本。如您所见，即将实施并行聚合的 PostgreSQL 16 的最终成本为118.02！之前版本的最终执行成本为185.70。

成本是以任意单位表示的查询执行时间的估计度量，表示执行查询计划中的特定步骤所需的处理能力。它通常表示 CPU、I/O 和内存使用情况的组合，帮助查询计划者选择最快的执行计划。

4、原文

https://www.cybertec-postgresql.com/en/tag/whats-new/