为何执行计划成本差异极大的两个查询实际耗时相近？

可能的核心原因 #

• 内存缓存抵消IO差异：如果表数据或索引已被加载到PostgreSQL的shared_buffers内存缓存中，Index Scan的回表操作就无需额外磁盘IO，Index Only Scan原本的无回表优势被抹平，两者实际执行效率趋近。
• 统计信息失真：PostgreSQL的成本估算依赖ANALYZE生成的统计数据，若统计信息过时、样本量不足或数据分布发生变化，会导致成本计算严重偏离实际——比如index_b的成本被高估，但实际执行开销远低于估算值。
• 索引与数据的物理特性：
  • index_a作为覆盖索引，若自身体积过大或页面碎片化严重，遍历索引的实际开销会高于预期；
  • index_b虽需回表，但如果目标行在表中物理连续存储（如聚簇表、数据顺序与索引高度匹配），回表时的IO是连续的，实际开销远低于估算的随机IO成本。
• 实际返回行数与估算偏差：计划中估算行数均为400,000，但如果实际返回行数远小于该值，两种扫描的核心开销都集中在索引定位阶段，回表的额外成本可忽略，耗时自然接近。

关于行数的影响 #

行数是间接影响因素，但不是直接导致耗时接近的主因：

• 若实际返回行数极少，两种扫描的核心操作都是索引定位，回表开销可以忽略，耗时会趋近；
• 若实际行数真为400,000，那缓存或统计信息失效大概率是主因——正常场景下Index Only Scan针对大结果集的性能应该远优于Index Scan。

验证建议 #

• 用EXPLAIN ANALYZE替代EXPLAIN，查看实际执行的循环次数、磁盘读取量等真实指标，对比两者差异；
• 清理缓存后重新测试（如重启数据库、调整shared_buffers），观察未缓存状态下的耗时变化；
• 执行ANALYZE table;更新统计信息，再检查成本估算是否更贴合实际。

内容的提问来源于stack exchange，提问作者Franco Piccolo