SQL 如何用 EXPLAIN (ANALYZE, BUFFERS) 分析 IO 与缓存命中

能，但需解析Buffers中shared/local/temp的hit与read比例：shared read高表明缓存命中低，temp read>0提示排序/哈希溢出，local read高可能因CTE未重用；其计数非真实IO次数，需结合iostat等工具验证。

EXPLaiN (ANALYZE, BUFFERS) 能不能直接看出 IO 和缓存命中？

能，但需要理解输出中 Buffers 部分的含义。postgresql 的 EXPLAIN (ANALYZE, BUFFERS) 不会直接标出“磁盘读了多少 MB”或“缓存命中率百分比”，而是通过 shared hit/read/dirtied、local hit/read、temp read/write 这几类计数告诉你数据从哪来、是否绕过共享缓冲区、有没有触发写入。关键不是看总数，而是比对 hit 与 read 的比例——比如某节点显示 Buffers: shared hit=12345 read=89，说明绝大多数页来自内存，仅 89 次真正发起了内核读（通常对应物理 IO）。

怎么解读 Buffers 行里的 shared/local/temp 含义？

shared 对应 shared_buffers，是 PostgreSQL 主缓冲池；local 是每个 backend 自己的临时缓冲区（用于排序、哈希等私有操作）；temp 是临时表使用的磁盘文件（即使没显式建 temp table，大排序/大哈希也可能落盘）。实际观察重点：

• shared read 高 + shared hit 低 → 查询反复淘汰缓冲区内容，可能 shared_buffers 设置过小，或查询扫描范围远超缓存容量
• temp read > 0 → 排序或哈希溢出到磁盘，需检查 work_mem 是否足够，或 WHERE 条件是否缺乏索引导致中间结果过大
• local read 显著高于 shared read → 可能大量使用 CTE 或子查询生成中间结果，且未被重用，注意是否可改写为 JOIN 或物化

为什么加了 ANALYZE 还看不到真实 IO？

因为 ANALYZE 只强制执行语句并统计实际耗时与缓冲区访问，但操作系统层面的 page cache、文件系统缓存、存储设备缓存仍会掩盖真实磁盘 IO。常见干扰：

• 第二次运行同一查询时 shared read=0，不代表没 IO——可能数据已在 OS page cache 中，PostgreSQL 层面没触发 read() 系统调用
• 使用 SSD 或带电池写缓存的 RAID 卡时，temp write 计数存在，但 iostat -x 1 看不到对应写入，因写入被硬件暂存
• linux drop_caches 可清空 page cache，但无法清除 shared_buffers 或硬件缓存，想测纯磁盘路径得配合 pg_drop_replication_slot（不推荐）或重启实例后首次运行

EXPLAIN (ANALYZE, BUFFERS) 的典型误读点

最容易错把“缓冲区访问次数”当成“IO 次数”。一次 shared read 是一次缓冲区缺失后从磁盘（或 OS cache）加载一个 block（通常是 8KB），但它背后未必是一次独立磁盘 seek。现代存储栈会合并、预读、延迟写入，所以：

• Buffers: shared read=1024 ≠ 1024 次磁盘 IO，可能是 2–3 次连续读取就覆盖全部
• Buffers: temp read=10000 也不等于 10000 次磁盘 IO，因为 temp 文件是顺序写+顺序读，OS 会批量处理
• 不要只看顶层节点的 Buffers，必须逐层下钻——嵌套循环里内表扫描的 shared read 若在 loop 外部重复发生，说明索引失效或连接条件未下推

真正要定位 IO 瓶颈，得结合 EXPLAIN (ANALYZE, BUFFERS) 的结构信息 + iostat -x 1 观察 await/r/svctm + pg_stat_bgwriter 查看 checkpoints 频率。单靠 Buffers 行只能判断“数据是否常驻内存”，不能替代系统级 IO 分析。