SQL work_mem 的排序 / hash join / CTE 内存使用上限与 OOM 风险防控

work_mem 设置后排序仍慢是因为它按操作节点（如order by、hash join、cte）单独分配，非共享内存池；多节点并发时内存消耗倍增，超限即落盘引发i/o瓶颈。

work_mem 设置后为什么排序还是慢？work_mem 不是“排序专用内存”，它控制的是每个操作节点（如一个 ORDER BY、一个 HASH JOIN、一个 CTE 子查询）能用的最大内存量。如果一个查询包含 3 个排序步骤，每个都可能吃掉一份 work_mem，实际内存消耗是倍数关系，不是共享池。

• 实际内存占用 = work_mem × 并发执行的操作节点数（不是并发连接数）
• ORDER BY 在数据量超过 work_mem 时会自动落盘到 pg_temp 目录，产生 I/O，速度骤降
• 查看是否落盘：开启 log_temp_files = 0，观察日志里是否有 temporary file 记录

CTE 被物化后内存翻倍的隐性代价 PostgreSQL 12+ 默认对非递归 CTE 启用“物化”（materialization），即先算完结果存进临时内存/磁盘，再供后续引用。这会让 CTE 内部的排序或聚合也单独消耗一份 work_mem，且无法与外层共享。

• 物化行为不可关闭（除非用 NOT MATERIALIZED 提示，但仅限于简单 CTE）

• 示例：

  

  Peppertype.ai

  高质量AI内容生成软件，它通过使用机器学习来理解用户的需求。

  下载

  WITH ranked AS (SELECT *, rank() OVER (ORDER BY score) FROM users)  
  SELECT * FROM ranked WHERE rank <= 10;

  这里 rank() OVER 的窗口排序会独占一份 work_mem，即使最终只取前 10 行

• 如果 CTE 只被引用一次，加 /<em>+ NOT MATERIALIZED </em>/（需 pg_hint_plan）或重写为子查询，可避免额外内存开销

hash join 溢出到磁盘的判断逻辑和监控方式HASH JOIN 使用 work_mem 构建哈希表。当输入右表（inner side）行数 × 平均行宽 > work_mem 时，PostgreSQL 会切分哈希桶并写入磁盘，后续做多次 probe，性能断崖式下降。

• 判断是否溢出：查看 EXPLAIN (ANALYZE, BUFFERS) 输出中是否有 Hash Cond 下带 disk: XkB 字样
• 常见误判：以为增大 work_mem 就能避免溢出，但若右表本身有重复值或倾斜（比如 90% 行的 join_key 都是同一个值），哈希表仍可能因桶不均而提前溢出
• 安全做法：对大表 join，优先在 join_key 上建索引 + 改用 NESTED LOOP（配合 enable_hashjoin = off 临时调试），比盲目调高 work_mem 更可控

OOM 前的典型征兆和紧急干预点 PostgreSQL 本身不会直接 OOM kill，但 Linux OOM killer 会在系统内存不足时干掉占用 RSS 最大的进程——通常是 PostgreSQL 的某个 backend，表现为连接突然断开、日志里出现 Killed process <pid></pid>。

• 关键征兆不是 work_mem 设得高，而是 shared_buffers + work_mem × max_connections + OS 缓存总和逼近物理内存
• 不要只看 work_mem 单值：一个复杂查询可能同时触发多个 hash、sort、aggregation 节点，每节点各占一份
• 紧急缓解：
  • 临时降低单个会话的 work_mem：SET LOCAL work_mem = '4MB';
  • 对已知重查询加 statement_timeout 防止卡死
  • 监控 pg_stat_progress_sort 和 pg_stat_progress_hash_join 视图，发现长时间运行且 current_pages > 0 就大概率已在刷磁盘

真正危险的不是 work_mem 设低了，而是设高了却没意识到它会被乘以操作节点数——一个看似简单的 CTE + ORDER BY + JOIN，可能暗地里申请三份内存，而你只盯着连接数去算。