[其他] PostgreSQL 19聚合查询性能优化：海量数据分析提速5倍

PostgreSQL 19 在性能改进上的一个关键方向，聚焦于聚合操作的执行效率。其中一项突破性优化能够对现有应用完全透明，无需修改任何代码或调整参数即可直接生效，大幅提升查询速度。

传统聚合执行逻辑：先关联，后聚合

在此前的 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

在这种场景中，维度表（如性别表）通常很小，但主表（如人员表）的数据量可能高达百万甚至千万级。传统的执行逻辑是：

1. 顺序扫描主表的每一条记录。
2. 根据关联字段（如gender_id）逐条查找维度表中对应的名称（如gender_name）。
3. 将结果累加到相应的分组中，最后输出。

这种逻辑本身没错，也是多数数据库系统的常规处理方式。但当数据呈现“主表巨大、维表极小”的典型特征时，性能瓶颈就会暴露出来：相同的维度值被反复查找，导致聚合性能随数据规模线性下降。

颠覆性优化：先聚合，后关联

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
);
-- 插入20万条产品数据，模拟海量表
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 = p.category_id
GROUP BY 1, 2;

在 PostgreSQL 19 之前，执行计划遵循传统路径（先关联后聚合），需要为每一条产品记录反复查询两个维度表的名称。

而启用 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)

新计划的核心逻辑是： 直接扫描产品主表，先按color_id等关联字段进行聚合（HashAggregate），将海量数据压缩成极少量的中间结果。随后，再通过高效的嵌套循环连接（Nested Loop）去查找对应的维度名称。由于聚合后数据量急剧减少，后续的关联操作成本变得极低，从而避免了大量重复的维度值查找。

性能对比与分析

执行效率的提升是显著的：

• 旧方式（先关联后聚合）: 95.3 ms
• 新方式（先聚合后关联）: 16.8 ms

查询速度提升了超过5倍。 随着查询中涉及的小型维度表数量增加，这种优化带来的性能收益会进一步放大。这对于复杂报表与实时统计分析场景具有重大价值。

测试环境说明： 本次测试为首次执行（未利用缓存提示位），使用全新的统计信息。环境为 MacBook M3，PostgreSQL 默认配置。

注意事项：GROUP BY CUBE 的局限性

尽管这项优化在绝大多数GROUP BY场景下效果显著，但仍有少数特性无法完全受益，例如GROUP BY CUBE：

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 = p.category_id
GROUP BY CUBE(1, 2);

其执行计划显示，CUBE操作所需的多组聚合仍然需要在关联后的上层完成。由于CUBE的语义限制，聚合逻辑无法被完全下推到关联之前。值得庆幸的是，相比常规的GROUP BY，CUBE在实际业务系统中的使用频率相对较低，因此不影响此项优化带来的整体巨大收益。

总结

PostgreSQL 19 通过允许优化器选择“先聚合，后关联”的执行策略，为处理星型或雪花型模型下的海量数据聚合查询提供了开箱即用的性能飞跃。这尤其利好数据仓库、商业智能（BI）报表等数据分析密集型应用。该优化完全自动化，无需开发者干预，彰显了 PostgreSQL 在查询优化器深度优化上的持续领先。