bigywater.tech
PostgreSQL · 11

Sort Method: external merge Disk 是什么意思:PostgreSQL work_mem 与排序、top-N heapsort、临时文件日志实测

“PostgreSQL 的 Sort Method: external merge Disk 是什么意思、work_mem 该调多大” 是慢查询里经常出现的疑问。这篇在本机自己起一个 PostgreSQL 16.15,用 100 万行的表对比 work_mem 对排序和哈希聚合的影响,验证 top-N 排序和索引能省掉什么,并演示怎么从日志里找到写临时文件的 SQL。

17672kB
work_mem = 4MB 时 100 万行排序写到磁盘的量(external merge);64MB 时 quicksort 只用 55827kB 内存
27kB
order by … limit 10 的 top-N heapsort 用的内存,work_mem 64kB 也不写磁盘
28.64 ms 对 258.66 ms
order by r limit 100000 有索引和没有索引(要排序)的耗时

排序放不放得下

实测 · 本机执行输出 
  work_mem =    4MB:Sort Method: external merge  Disk: 17672kB;耗时 258.80 ms;Buffers: shared hit=12352 dirtied=1777, temp read=2209 written=2223
  work_mem =   64MB:Sort Method: quicksort  Memory: 55827kB;耗时 221.52 ms;没有临时文件
  work_mem =  256MB:Sort Method: quicksort  Memory: 55827kB;耗时 219.56 ms;没有临时文件

放得下是 quicksort(内存),放不下是 external merge(写临时文件)。

top-N

实测 · 本机执行输出 
  work_mem =   64kB:Sort Method: top-N heapsort  Memory: 27kB;耗时 102.39 ms
  work_mem =    4MB:Sort Method: top-N heapsort  Memory: 27kB;耗时 100.11 ms

order by … limit N 只维护 N 行,work_mem 很小也不写盘。

索引省掉排序

实测 · 本机执行输出 
  有索引可用:->  Index Scan using t_r on t  (cost=0.42..74038.74 rows=1000000 width=8) (actual time=0.006..22.989 rows=100000 loops=1) → 没有 Sort 节点: True;耗时 28.64 ms
  关掉索引扫描:Sort Method: external merge  Disk: 17672kB;耗时 258.66 ms

ORDER BY 的列上有索引,计划里没有 Sort 节点,也没有临时文件。

找出写盘的 SQL

实测 · 本机执行输出 
      日志:LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp<pid>.<n>", size 18096128
      日志:STATEMENT:  select r, id from t order by r
  pg_stat_database(postgres):temp_files = 1,temp_bytes = 18096128(17.3 MB)

log_temp_files 把每个临时文件连同 SQL 记进日志;pg_stat_database 累计。

哈希聚合

实测 · 本机执行输出 
  work_mem =    4MB:Planned Partitions: 8  Batches: 9  Memory Usage: 8273kB  Disk Usage: 23536kB;耗时 284.28 ms
  work_mem =   64MB:Batches: 1  Memory Usage: 65553kB;耗时 255.31 ms
  work_mem =  256MB:Batches: 1  Memory Usage: 65553kB;耗时 255.65 ms

HashAggregate 放不下会分批溢出到磁盘;work_mem 是每个节点、每个会话各用一份。

分步演示

work_mem 实测
本步的对象

    本文用到的工具与边界

    • 本机 macOS 27 arm64 / PostgreSQL 16.15(Homebrew);每个实验自己 initdb 一个数据目录,端口 9261 起,只监听 127.0.0.1,不碰本机已有的 5432 上的实例;命令行工具统一用 LC_ALL=C 取英文原文,路径换成 ;计时用 time.perf_counter 或 EXPLAIN ANALYZE 的 Execution Time,数字每次运行会有小幅抖动;实验结束后整个数据目录删除。关掉并行查询,shared_buffers = 512MB,100 万行的表全在内存里,临时文件在本机 SSD 上并有系统缓存,所以 external merge 只比 quicksort 慢约 40 ms;每条语句 EXPLAIN ANALYZE 取 3 次里 Execution Time 最小的一次
    • 没有验证:慢盘或网络盘上临时文件的真实代价;并行排序和并行哈希;hash_mem_multiplier 的作用;同一个查询里多个排序/哈希节点各占一份 work_mem 的总内存;PostgreSQL 13 之前哈希聚合不溢出的行为(本机只有 16.15);maintenance_work_mem 对建索引的影响
    京ICP备2026067502号-1京公网安备11010502063487号