PostgreSQL / 系统课时

连接、排序与聚合算法

连接、排序与聚合算法

同一条 SQL 可能因为输入规模、数据顺序和可用内存不同,切换成完全不同的连接、排序或聚合算法。本课通过 EXPLAIN (ANALYZE) 识别这些节点,并判断 work_mem 过小时何时发生磁盘排序或哈希分批溢写。

学习目标

  • 区分 Nested Loop、Hash Join 与 Merge Join 的典型适用条件
  • 从执行计划识别外部排序、哈希分批和聚合溢写
  • 正确理解 work_mem 对单个执行操作的影响及其并发风险

连接算法取决于输入形态

Nested Loop 会让外层输入的每一行驱动一次内层访问。当外层行数较少,并且内层可以通过索引快速定位时,这种算法通常很有效;如果两侧都很大而内层又需要反复扫描,成本会迅速增加。

Hash Join 通常用于等值连接。执行器先读取一侧建立哈希表,再扫描另一侧按连接键探测;哈希表装不进允许的内存时,会拆成多个 batch 并借助临时文件处理。Merge Join 则要求两侧按连接键有序,已有索引顺序时尤其有价值,否则可能需要额外 Sort。

优化器不会按固定优先级选择这三种算法,而是比较候选计划成本。因此前置节点的行数估算一旦严重失真,也可能进一步造成连接顺序或连接算法选择不合理。

排序和哈希怎样落盘

Sort 节点在数据能够放进内存时通常直接进行内存排序;超出工作内存后,可转换为 external merge,并在执行计划中报告磁盘使用。Hash Join 或 HashAggregate 的哈希结构无法容纳时,则可能分成多个批次处理并使用临时磁盘空间。

连接排序聚合算法与 work_mem 溢写关系

复杂计划里可能同时存在多个 Sort 或 Hash 操作,并发会话和并行执行还会进一步叠加内存需求。因此,提高 work_mem 应以实际执行计划为依据,而不是把一个较大的值直接设成所有连接的全局默认值。

用低内存制造可观察的溢写

下面创建 10 万客户和 50 万订单。查询需要 Hash Join、对 10 万个分组执行聚合,并对聚合结果排序。把当前会话的 work_mem 设置为 64kB 后,大型排序和哈希结构更容易出现临时磁盘使用。

sql
CREATE TEMP TABLE dim_customer AS
SELECT g AS customer_id,
       'region-' || (g % 100) AS region
FROM generate_series(1, 100000) AS g;

CREATE TEMP TABLE fact_orders AS
SELECT g AS order_id,
       (g % 100000) + 1 AS customer_id,
       (g % 5000)::numeric AS amount
FROM generate_series(1, 500000) AS g;

ANALYZE dim_customer;
ANALYZE fact_orders;

SET work_mem = '64kB';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT d.region,
       f.customer_id,
       sum(f.amount) AS total_amount
FROM fact_orders AS f
JOIN dim_customer AS d USING (customer_id)
GROUP BY d.region, f.customer_id
ORDER BY total_amount DESC, f.customer_id;

SET work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT d.region,
       f.customer_id,
       sum(f.amount) AS total_amount
FROM fact_orders AS f
JOIN dim_customer AS d USING (customer_id)
GROUP BY d.region, f.customer_id
ORDER BY total_amount DESC, f.customer_id;

预期结果

text
低 work_mem 计划的关键特征:
Sort  (...)
  Sort Key: (sum(f.amount)) DESC, f.customer_id
  Sort Method: external merge  Disk: <VALUE>kB
  ->  <AGGREGATE_NODE>  (...)
        ->  Hash Join  (...)
              Hash Cond: (f.customer_id = d.customer_id)
              ->  Seq Scan on fact_orders f  (...)
              ->  Hash  (...)
                    Buckets: <VALUE>  Batches: <VALUE>  Memory Usage: <VALUE>kB

高 work_mem 计划的关键特征:
Sort  (...)
  Sort Key: (sum(f.amount)) DESC, f.customer_id
  Sort Method: quicksort  Memory: <VALUE>kB
  ->  <AGGREGATE_NODE>  (...)
        ->  Hash Join  (...)
              Hash Cond: (f.customer_id = d.customer_id)
              ->  Seq Scan on fact_orders f  (...)
              ->  Hash  (...)
                    Buckets: <VALUE>  Batches: 1  Memory Usage: <VALUE>kB

Execution Time: <VALUE> ms

计划细节会受 PostgreSQL 版本、硬件和成本估算影响,聚合节点可能表现为 HashAggregate、GroupAggregate 或带并行阶段的变体。实验需要关注的是低内存计划中的 external merge、Disk 和大于 1 的 Batches,以及提高内存后这些指标是否消失或下降。

聚合算法也受输入与内存影响

HashAggregate 使用分组键建立哈希结构,不要求输入预先排序,在分组数量适中并且内存能够容纳哈希状态时很有效。如果执行计划显示多个 batch 或 Disk Usage,说明哈希聚合已经需要额外的分批或磁盘处理。

GroupAggregate 按有序的分组键连续处理相同组。如果上游已经通过索引、Merge Join 或 Sort 提供所需顺序,它可能很有竞争力;如果必须先对大量数据排序,则还要把排序成本和潜在的磁盘溢写一起计算。

因此不能只看到 HashAggregate 或 GroupAggregate 名称就判断好坏。需要同时检查输入行数、分组数量、排序来源、实际内存和临时磁盘使用。

正确理解 work_mem

work_mem 是排序和哈希等执行操作使用的基础工作内存参数,不是“一条 SQL 最多只能使用这么多内存”的硬性总上限。一次计划可以包含多个需要工作内存的节点,而并行执行时多个 worker 也可能同时产生内存需求。

哈希类操作的实际内存预算还会受到 hash_mem_multiplier 影响。因此发现 Hash Join 或 HashAggregate 溢写时,可以在会话级实验中调整内存参数并重新执行计划,但生产环境必须结合查询并发数评估总内存风险。

实际排查应先确认 estimated rows 与 actual rows 是否合理,再检查为何选择当前连接算法,然后寻找 Sort、Hash 和 Aggregate 节点中的磁盘使用,最后才试调 work_mem。这样可以区分“计划选择错误”和“正确计划获得的内存太少”,避免用调参掩盖统计信息或索引层面的真正问题。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

连接、排序与聚合算法

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通