PostgreSQL 13.6升级后count查询走顺序扫描未走索引仅扫描咨询

核心差异点先排查 #

• 测试SQL不一致：你贴的13版本执行的是select count(id) from jobs，10版本执行的是select count(*) from jobs。即使id是主键非空、两者语义完全等价，写法差异也可能在统计信息不准时干扰成本计算，先统一用完全相同的SQL（建议用count(*)）重跑执行计划，排除无关变量。
• 统计信息严重失真：从执行计划的估算行数就能看出问题：13版本优化器估算jobs表总行数约165万（2个并行worker各处理82.5万行），10版本估算值约483万，两者差了近3倍。如果是通过pg_upgrade做的大版本升级，默认不会迁移旧版本的统计信息，优化器拿不到准确的表大小、列分布、索引可见性数据，选错执行计划是非常普遍的现象。

常见触发原因 #

1. 可见性映射(VM)未更新
   Index Only Scan不需要回表的前提是，对应堆表页面的所有元组都对当前事务可见，这个信息存在VM文件里。升级后如果没做过vacuum，VM信息缺失或过期，优化器会给Index Only Scan加上大量回表查询的额外成本，最终估算成本会高于并行顺序扫描。
2. 版本默认成本参数调整
   PostgreSQL 12之后对并行顺序扫描的成本计算逻辑做了优化，13版本默认的并行扫描触发阈值更低、并行成本折算系数更合理，当表大小达到并行扫描阈值时，并行顺序扫描的估算成本很容易压过需要扫描大量索引页、甚至需要回表的Index Only Scan。
3. 索引/表膨胀
   如果主键索引jobs_pkey存在严重膨胀，扫描索引需要读取的页面数比直接顺序扫描堆表还多，优化器自然会选择成本更低的顺序扫描。

排查与优化步骤 #

1. 补齐统计信息与可见性映射
   先对目标表执行vacuum analyze jobs;，语句会同时更新表的统计信息、刷新VM文件，跑完之后重新查看执行计划。大版本升级完成后，建议对全库执行vacuumdb -a -z（全库analyze），避免所有表出现统计信息不准的问题。
2. 对齐跨版本参数配置
   对比两个版本的以下参数配置，确保值一致，排除参数差异导致的成本计算偏差：
   • random_page_cost：值越高越倾向顺序扫描，SSD场景建议设为1.1
   • effective_cache_size：值越低越认为索引无法命中缓存，倾向顺序扫描，建议设为系统可用内存的3/4
   • parallel_tuple_cost、parallel_setup_cost：值越低越倾向选择并行执行路径
   • min_parallel_table_scan_size、min_parallel_index_scan_size：控制并行扫描的触发阈值
3. 检查对象膨胀情况
   执行以下语句检查主键索引的膨胀率：

   SELECT * FROM pgstatindex('jobs_pkey');
   

   如果返回的leaf_fragmentation超过30%，说明索引碎片较多，执行reindex index concurrently jobs_pkey;重建索引即可。
4. 验证真实执行性能
   执行带实际运行指标的执行计划，对比两个扫描路径的真实耗时：

   explain (analyze, buffers, verbose) select count(*) from jobs;
   

   如果确实是优化器误判，顺序扫描实际耗时远高于索引仅扫描，可以通过调整上述成本参数矫正选择，也可以通过pg_hint_plan扩展加IndexOnlyScan提示强制走索引。
   注意：大表全量count本身就是重操作，不管走哪种扫描方式性能上限都不高，如果这类查询调用频繁，建议单独维护计数表做实时更新，不要每次全表扫描统计。

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