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）
-
示例：
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 × 并发执行的操作节点数（不是并发连接数）ORDER BY 在数据量超过 work_mem 时会自动落盘到 pg_temp 目录，产生 I/O，速度骤降log_temp_files = 0，观察日志里是否有 temporary file 记录work_mem，且无法与外层共享。
物化行为不可关闭（除非用
NOT MATERIALIZED提示，但仅限于简单 CTE）-
示例：
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 就大概率已在刷磁盘
EXPLAIN (ANALYZE, BUFFERS) 输出中是否有 Hash Cond 下带 disk: XkB 字样work_mem 就能避免溢出，但若右表本身有重复值或倾斜（比如 90% 行的 join_key 都是同一个值），哈希表仍可能因桶不均而提前溢出join_key 上建索引 + 改用 NESTED LOOP（配合 enable_hashjoin = off 临时调试），比盲目调高 work_mem 更可控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，可能暗地里申请三份内存，而你只盯着连接数去算。
