SQL EXPLAIN (ANALYZE, TIMING, BUFFERS) 的共享缓冲区命中率检查要点

shared hit 占比需用 shared hit / (shared hit + shared read) 计算，排除 dirtied 和 local 计数；低于 90% 需结合查询模式判断，长期 high shared read 会挤占缓存并加剧检查点压力。

怎么看 shared hit 在 BUFFERS 输出里的占比

共享缓冲区命中率不是直接给的百分比，得自己算：用 shared hit 除以 shared hit + shared read。注意别把 shared dirtied 或 local 类计数混进来——它们和命中率无关。

常见错误是看到 shared hit: 1234 就觉得“很高”，但没看 shared read: 89，实际命中率才 93%；而如果 read 是 500，命中率就掉到 71%，说明大量数据没被缓存住。

• EXPLAIN (ANALYZE, TIMING, BUFFERS) 必须加 BUFFERS，否则压根不显示缓存统计
• 只有超级用户或有 pg_read_all_stats 权限的用户才能看到 BUFFERS 输出
• 命中率低于 90% 时，得结合查询模式判断：是单次大扫描（合理偏低），还是高频小查询反复读磁盘（有问题）

shared read 高但查询快，是不是可以不管？

不能。快是因为磁盘 I/O 比预想中快（比如 SSD、预读、OS 缓存），不代表数据库层缓存效率高。长期 shared read 高会挤占 shared_buffers 空间，影响其他查询，并放大检查点压力。

典型场景：一个 SELECT COUNT(*) FROM logs WHERE created_at > '2024-01-01' 扫了 200 万行，shared read: 1500，但执行只用了 120ms——这容易让人忽略它每跑一次就刷掉几百页缓存。

• 用 pg_stat_bgwriter 查 buffers_checkpoint 和 buffers_clean 是否异常升高
• 对比同一查询在空缓存（pg_drop_replication_slot 不适用，改用 pg_reload_conf() + 等待 cache 清理）和热缓存下的 shared read 差值
• shared read 高 + temp read 也高，可能是 work_mem 不足导致落盘，优先调 work_mem

为什么 shared hit 有时为 0，即使表很小？

不是缓存失效，而是查询根本没走缓冲区路径。最常见两种情况：一是用了 UNLOGGED 表（不写 WAL，也不进 shared_buffers），二是启用了 temp_buffers 且查询涉及临时表或 CTE 物化——这部分计入 local 而非 shared。

另一个隐蔽原因是并行查询：主进程可能只做协调，真正扫描由 worker 进程完成，而 BUFFERS 默认只显示主进程统计（除非加 VERBOSE 并解析 JSON 输出）。

• 检查表定义：SELECT relpersistence FROM pg_class WHERE relname = 'your_table';，u 表示 UNLOGGED
• 确认是否用了 MATERIALIZED CTE 或显式 CREATE TEMP TABLE
• 并行查询下，命中率得看每个 worker 的 shared 子项，主节点的 shared hit 为 0 很正常

shared_buffers 调大一定能提高命中率？

不一定。PostgreSQL 的 LRU-based 缓存淘汰策略对访问模式敏感：如果查询总在扫新数据（比如时间分区表里查最新分区），增大 shared_buffers 只是让旧页多留一会儿，不改变命中率本质。

更关键的是“缓存友好型访问”：顺序扫描、索引覆盖、减少不必要的列投影。一个 SELECT * 即使缓存够大，也可能因页面内数据密度低导致更多页加载。

• 先用 pg_statio_user_tables 看 heap_blks_read / heap_blks_hit 的长期趋势，比单次 EXPLAIN 更可靠
• shared_buffers 超过主机内存 25% 后收益通常急剧下降，尤其在有大量并发连接时
• 比起盲目调大，优先优化 effective_cache_size（影响规划器成本估算），它不分配内存但决定是否选索引扫描