PostgreSQL 19：超高速聚合的全新突破
PostgreSQL 18 正式发布后，PostgreSQL 19 的性能改进方向已经引发广泛关注。其中，聚合性能的重大优化被认为是最具突破性的改进之一，并且这一优化对现有应用完全透明，无需修改代码、无需调整参数，即可直接生效。
PostgreSQL 中的数据聚合
在 PostgreSQL 此前的版本中，聚合的基本执行规则是：
先关联（Join），后聚合（Aggregate）
典型示例如下：
SELECT j.gender_name, count(*)
FROM person AS p, gender AS j
WHERE p.gender_id = j.gender_id
GROUP BY j.gender_name
在该类场景中，通常只存在少量维度数据（如性别类型），但主表数据规模可能达到百万级。传统执行逻辑如下：
- 顺序读取 person 表中的每一条记录
- 依据 gender_id 逐条查找对应的 gender_name，并将结果累加到对应分组中
- 输出聚合结果
该方式在逻辑上并不存在错误，也是大多数数据库系统的常规处理方式。但当数据呈现出“主表极大、维表极小”的典型特征时，性能问题便会显现：
- 相同的维度值被反复查找
- 聚合性能随数据规模下降
突破性改进：先聚合，后关联
PostgreSQL 19 引入了一项关键优化能力：
执行计划可在“先聚合，后关联”与“先关联，后聚合”之间自主选择。
这一看似细微的调整，实则能带来颠覆性的性能飞跃。
在大量业务系统中，以下结构极为常见：
CREATE TABLE t_category (
category_id int4 PRIMARY KEY,
category_name text
);
INSERT INTO t_category VALUES
(0, 'Shoes'), (1, 'Shirts'),
(2, 'Car'), (3, 'Bike');
CREATE TABLE t_color (
color_id int4 PRIMARY KEY,
color_name text
);
INSERT INTO t_color VALUES
(0, 'Red'), (1, 'Green'),
(2, 'Yellow'), (3, 'Blue');
CREATE TABLE t_product (
category_id int4 REFERENCES t_category (category_id),
color_id int4 REFERENCES t_color (color_id),
whatever text
);
该数据模型包含两个极小的维度表（类别表、颜色表）和一个数据量巨大的产品表，本示例中产品表规模为 200,000 行：
INSERT INTO t_product
SELECT id % 4, (id * random())::int4 % 4, md5(id::text)
FROM generate_series(1, 200000) AS id;
目标是按“类别 + 颜色”统计产品数量，对应的 SQL 查询语句如下：
SELECT category_name, color_name, count(*)
FROM t_product AS p, t_category AS c1, t_color AS c2
WHERE p.color_id = c2.color_id
AND c1.category_id = c1.category_id
GROUP BY 1, 2;
这是一个仅涉及三张数据表的关联查询，核心逻辑是针对每条产品记录，查询两类维度名称，PostgreSQL 19 之前的版本对应执行计划如下：
QUERY PLAN
------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=13167.09..13170.53 rows=16 width=18)
Group Key: c1.category_name, c2.color_name
-> Gather Merge (cost=13167.09..13170.17 rows=27 width=18)
Workers Planned: 1
-> Sort (cost=12167.08..12167.12 rows=16 width=18)
Sort Key: c1.category_name, c2.color_name
-> Partial HashAggregate (cost=12166.60..12166.76 rows=16 width=18)
Group Key: c1.category_name, c2.color_name
-> Hash Join (cost=2.49..8637.19 rows=470588 width=10)
Hash Cond: (p.color_id = c2.color_id)
-> Parallel Seq Scan on t_product p (cost=0.00..3046.47 rows=117647 width=4)
-> Hash (cost=2.29..2.29 rows=16 width=14)
-> Nested Loop (cost=0.00..2.29 rows=16 width=14)
-> Seq Scan on t_category c1 (cost=0.00..1.04 rows=4 width=5)
-> Materialize (cost=0.00..1.06 rows=4 width=9)
-> Seq Scan on t_color c2 (cost=0.00..1.04 rows=4 width=9)
(16 rows)
分析该执行计划需遵循从内向外的原则。执行流程以对颜色表和类别表的全表扫描为起点，随后将维度表与产品主表完成关联，待关联操作全部结束后，才会启动聚合计数。也就是说，系统需要针对每条产品记录，重复执行两次维度名称查询。
采用 PostgreSQL 19 新优化机制后的执行计划如下：
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Finalize GroupAggregate (cost=4636.63..4638.60 rows=15 width=18)
Group Key: c1.category_name, c2.color_name
-> Gather Merge (cost=4636.63..4638.34 rows=15 width=18)
Workers Planned: 1
-> Sort (cost=3636.62..3636.64 rows=9 width=18)
Sort Key: c1.category_name, c2.color_name
-> Nested Loop (cost=3634.84..3636.48 rows=9 width=18)
-> Nested Loop (cost=3634.84..3635.33 rows=2 width=13)
-> Partial HashAggregate (cost=3634.71..3634.75 rows=4 width=12)
Group Key: p.color_id
-> Parallel Seq Scan on t_product p (cost=0.00..3046.47 rows=117647 width=4)
-> Index Scan using t_color_pkey on t_color c2 (cost=0.13..0.15 rows=1 width=9)
Index Cond: (color_id = p.color_id)
-> Materialize (cost=0.00..1.06 rows=4 width=5)
-> Seq Scan on t_category c1 (cost=0.00..1.04 rows=4 width=5)
(15 rows)
新执行计划的核心逻辑是直接读取 product 主表，先按相关 ID 字段完成聚合计算，随后再通过嵌套循环方式完成数据关联。此后执行过程将变得非常高效，因为在 HashAggregate 之后，数据量已经被大幅压缩，只剩下极少量行。这种方案的巧妙之处在于：在按 ID 完成聚合之后，只需要查找极少量名称值，从而节省了大量重复迭代操作。
数据库性能分析
从执行效率来看，新执行方式具备明显优势，性能对比如下所示：
old method: 95.3 ms
new method: 16.8 ms
测试结果显示，新方式的查询速度提升 5 倍以上。并且随着参与关联的查找表数量增加，性能收益还将进一步放大，该优化在复杂报表、统计分析类场景中表现尤为突出。
补充说明：本次测试为首次运行，未启用提示位（hint bits），采用全新统计信息；测试环境为 MacBook M3，数据库配置为 PostgreSQL 默认参数。
CUBE：局限性
尽管 PostgreSQL 19 的新优化机制在绝大多数场景下效果显著，但仍然存在少数特性无法完全受益，GROUP BY CUBE 就是典型案例：
PgSQL
explain
SELECT category_name, color_name, count(*)
FROM t_product AS p, t_category AS c1, t_color AS c2
WHERE p.color_id = c2.color_id
AND c1.category_id = c1.category_id
GROUP BY CUBE(1, 2);
其对应的执行计划如下：
QUERY PLAN
----------------------------------------------------------------------------------------
MixedAggregate (cost=2.49..29372.74 rows=25 width=18)
Hash Key: c1.category_name, c2.color_name
Hash Key: c1.category_name
Hash Key: c2.color_name
Group Key: ()
-> Hash Join (cost=2.49..13372.49 rows=800000 width=10)
Hash Cond: (p.color_id = c2.color_id)
-> Seq Scan on t_product p (cost=0.00..3870.00 rows=200000 width=4)
-> Hash (cost=2.29..2.29 rows=16 width=14)
-> Nested Loop (cost=0.00..2.29 rows=16 width=14)
-> Seq Scan on t_category c1 (cost=0.00..1.04 rows=4 width=5)
-> Materialize (cost=0.00..1.06 rows=4 width=9)
-> Seq Scan on t_color c2 (cost=0.00..1.04 rows=4 width=9)
(13 rows)
在该场景中可以看到，CUBE 所涉及的多组聚合仍然需要在上层统一完成。由于执行语义上的限制，相关聚合逻辑无法完全下推。需要指出的是，与常规 GROUP BY 相比，CUBE 在实际业务系统中的使用频率相对较低，因此对整体优化收益影响有限。
结语
若需进一步了解 PostgreSQL 中的 CUBE 与分组集（Grouping Sets）相关机制，可参考以下技术资料：
原文链接：
https://www.cybertec-postgresql.com/en/super-fast-aggregations-in-postgresql-19/
作者：Hans-Jürgen Schönig
浙公网安备 33010602011771号