PostgreSQL work_mem 过小实测：external merge Disk 9880kB，调到 4MB 后 Hash 全在内存
在 PostgreSQL 17.11 测试库、80 万行自造事件表上，把会话 work_mem 从 64kB 调到 4MB / 64MB，用 EXPLAIN (ANALYZE, BUFFERS) 对比 Sort Method 与 HashAggregate 的 Memory / Disk。说明什么时候该加 work_mem、什么时候该先改 SQL，附只读巡检 SQL 与可复跑脚本。
串行关闭并行后，同一条 GROUP BY：work_mem=64kB 走 GroupAggregate + external merge Disk: 9880kB，两轮执行 274.9ms / 279.1ms；调到 4MB 后改成 HashAggregate，Memory Usage: 4369kB、Batches: 1，两轮 137.0ms / 131.4ms。DISTINCT user_id 更夸张：64kB 时 Hash 落盘约 19MB 且排序再 Disk 280kB，427ms / 383ms；4MB 后 quicksort Memory 769kB，150ms / 144ms。work_mem 是「每操作」限额，不是「每连接只有一份」。
适用对象
- Sort Method: external merge Disk: NNNNkB 就是排序工作区不够，溢出到临时文件；HashAggregate 的 Disk Usage / Batches>1 是同一类问题。
- 本次合成 80 万行、关掉并行后：GROUP BY 从 64kB→4MB，执行时间大约减半（~275ms→~134ms）；DISTINCT 从 ~400ms 降到 ~147ms。
- 再把 work_mem 从 4MB 提到 64MB，这两条语句几乎不再变快——够用就停，别把全局 work_mem 一次拉到几百 MB。
- work_mem 按「每个排序/哈希操作」计，复杂查询里可能同时有多个；再乘连接数和并行 worker，内存是乘出来的。
GROUP BY + ORDER BY 聚合：64kB 落盘，4MB 起全在内存
语句：三个月窗口内按 (user_id, event_type) 聚合，再按 SUM(payload_score) 取 Top 50。扫描约 36.2 万行。
work_mem=64kB 时计划是 GroupAggregate，底下 Sort Method: external merge Disk: 9880kB。两轮 Execution Time 274.858ms / 279.053ms。
work_mem=4MB 时优化器改选 HashAggregate，Planned Partitions: 4、Batches: 1、Memory Usage: 4369kB，没有 Disk。两轮 137.026ms / 131.366ms——大约快一倍。
再提到 64MB：HashAggregate Memory Usage 提到 7185kB，执行 146.647ms / 132.240ms，和 4MB 同一量级。对这条语句，4MB 已经够用。
本节判断
- 开着并行时，每个 worker 各自一份 work_mem，Disk 数字会拆到多个 worker 上；对比参数时建议先关掉并行，数字更好读。
- SSD 上偶尔会出现「落盘也不慢」的错觉；并发一高，临时文件会跟业务 I/O 抢带宽，还是应该消掉不必要的 spill。
| work_mem | 方法 | 内存 / 磁盘 | 第 1 轮 | 第 2 轮 |
|---|---|---|---|---|
| 64kB | GroupAggregate + Sort | external merge Disk 9880kB | 274.9 | 279.1 |
| 4MB | HashAggregate | Memory 4369kB，Batches:1 | 137.0 | 131.4 |
| 64MB | HashAggregate | Memory 7185kB，Batches:1 | 146.6 | 132.2 |
SELECT DISTINCT user_id：Hash 落盘 19MB 时更疼
语句：SELECT DISTINCT user_id FROM events ORDER BY user_id（2 万个 distinct）。
64kB：HashAggregate Planned Partitions: 4、Batches: 21、Disk Usage: 19648kB，外加 Sort Method: external merge Disk: 280kB。两轮 427.437ms / 382.614ms。
4MB：HashAggregate Batches: 1、Memory Usage: 1809kB，Sort Method: quicksort Memory: 769kB。两轮 149.689ms / 144.129ms。
这条比 GROUP BY 更能说明问题：不够用的 work_mem 会同时让哈希和排序都去写临时文件，延迟和 I/O 一起涨。
本节判断
- pg_stat_database.temp_files / temp_bytes 是旁证：会话级加大 work_mem 后，若对应库的临时文件增速下来，说明方向对了。
- 只读巡检 SQL 里把这两个计数和当前 work_mem 放在一起看，比凭感觉调参靠谱。
| work_mem | Hash | Sort | 第 1 轮 | 第 2 轮 |
|---|---|---|---|---|
| 64kB | Disk ~19648kB，Batches:21 | external merge Disk 280kB | 427.4 | 382.6 |
| 4MB | Memory 1809kB，Batches:1 | quicksort Memory 769kB | 149.7 | 144.1 |
我实际怎么改 work_mem
先确认计划里真有 Disk / Batches>1，再动参数。没有 spill 却把全局 work_mem 拉大，只是给每个操作预留更多上限，收益接近零、风险在乘法。
报表、ETL、偶发大查询：用 SET work_mem = '32MB'（或 SET LOCAL 包在事务里）只加大当前会话，结束后恢复默认。
OLTP 短查询大面积 spill：先查有没有缺索引、是否 SELECT * 排序、是否在应用层能分页。全局默认我一般只在确认「多数会话都不够」时才小步上调（例如 4MB→8MB），并盯内存与 temp_files。
从 Disk 到改法
- 01
1
EXPLAIN (ANALYZE, BUFFERS)
找 Sort Method / HashAggregate 行，记下 Disk 与 Memory、Batches。
- 02
2
会话级试一个更大的 work_mem
只对这条 SQL 试 4MB→16MB→64MB，看 Disk 是否消失、时间是否还降。够用就停。
- 03
3
能改 SQL 就先改 SQL
缩小排序列、加过滤、用索引顺序、避免无必要的 DISTINCT。参数是后备，不是第一选择。
- 04
4
再决定要不要改全局默认
用只读 SQL 看 temp_files 是否普遍偏高；改 postgresql.conf 前按连接数 × 并行 × 每查询操作数估算内存。
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
work_mem 是每个连接一份吗？
不是。它是每个排序或哈希操作的上限。一条复杂 SQL 里可能同时有多个操作，并行 worker 各自再算一份，所以不能按「连接数 × work_mem」低估，而要按「连接 × 操作数 × 并行」粗估。
把 work_mem 调到 1GB 是不是更快？
对单条大查询可能更快，但对整机很危险：高峰时多个会话一起申请，容易把内存打满甚至触发 OOM。本次 4MB 已经消掉 Disk，再加到 64MB 几乎没更快。
external merge 一定比内存排序慢吗？
多数情况更慢，尤其并发高、磁盘忙的时候。单机空闲 SSD 上有时差距不大，所以要以 ANALYZE 的实际时间和 temp_files 为准，不要只看方法名称吓人。
这些数字能代表生产吗？
不能。这是本机 17.11、80 万行合成数据、关掉并行后的对比。结论看相对关系：不够用会 Disk，够用后继续加收益递减。生产请在只读库用同一套 EXPLAIN 复测。
下一步
推荐动作
你提供 EXPLAIN (ANALYZE, BUFFERS) 和只读 SQL 输出（可脱敏），我们判断该会话级加大 work_mem、改 SQL，还是调整全局默认，并估算内存风险；生产变更由你方授权执行。