PostgreSQL 建了索引仍然全表扫描：1000 万行表 ANALYZE 前后的执行计划对比

本文摘要1000 万行表建好索引查询仍走全表扫描先怀疑索引往往查不到根因。用 EXPLAIN 对比 ANALYZE 前后执行计划可看到默认选择率 0.005 如何让优化器放弃索引。一、问题与结论一张orders表 1000 万行idx_orders_customer_id已建好且pg_index.indisvalid true查询条件正好命中该列EXPLAINSELECT*FROMordersWHEREcustomer_id500000;-- 输出Seq Scan on orders (cost0.00..274432.00 rows50000 width...)索引列顺序、函数包裹、隐式类型转换都排查过了全部正常。结论是索引没问题缺的是列统计信息。该表从未被ANALYZE过优化器对等值条件只能使用默认选择率DEFAULT_EQ_SEL 0.005估算 50,000 行匹配索引随机 I/O 代价高于顺序扫描。执行ANALYZE orders后估算降到约 10 行计划翻转为Index Scan。二、排查与选择依据三条查询即可判定不必再在索引上耗时间SELECTrelname,reltuples::BIGINTASest_rowsFROMpg_classWHERErelnameorders;SELECTattname,n_distinctFROMpg_statsWHEREtablenameordersANDattnamecustomer_id;SELECTn_mod_since_analyze,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelidorders::regclass;reltuples为-1PostgreSQL 14 起13 及更早版本可能显示 0该表从未经 ANALYZE/VACUUM。pg_stats查不到customer_id列级统计缺失。n_mod_since_analyze持续增长统计正在变旧。三条同时成立即可判定为统计信息问题。若pg_stats有记录且n_distinct合理则应回到索引本身继续排查。替代方案与取舍方案选择条件代价边界手动ANALYZE orders统计缺失或过期需立即修复一次性全表采样 I/O 与 CPU不解决之后再次变旧按表调低autovacuum_analyze_scale_factor变更频繁且查询关键的大表auto-analyze 运行更频繁写入极低的表纯属浪费ETL 末尾触发ANALYZE批次变更、变更量低于自动阈值依赖作业编排纪律漏配一次即失效不该用本文方案的情况WHERE status completed匹配约 99% 行ANALYZE 后优化器仍会正确地选择Seq Scan。这不是 ANALYZE 没生效而是代价计算本来就该选顺序扫描。三、关键原理阈值机制。auto-analyze 的触发条件是nchanges autovacuum_analyze_threshold autovacuum_analyze_scale_factor × reltuples默认值 50 与 0.1。代入 1000 万行50 0.1 × 10,000,000 1,000,050必须修改超过 100 万行才触发。10 万行的表改 1 万行就能触发1 亿行的表要改 1000 万行——表越大自动分析的相对响应能力越弱。选择率与代价翻转。ANALYZE 收集n_distinct、most_common_vals、histogram_bounds等列级统计场景等值选择率估算匹配行数无统计默认值0.00550,000 行ANALYZE 后1/100 万0.000001约 10 行估算 50,000 行时索引扫描需要约 50,000 次随机 I/O按random_page_cost 4.0计算总代价超过按seq_page_cost 1.0顺序读全表的 Seq Scan估算 10 行时索引扫描只需约 20 次随机 I/O代价远低于全表扫描。估算行数相差 5000 倍直接导致代价计算翻转。四、可运行示例环境PostgreSQL 1412–16 行为一致空库插入 1000 万行约 30–60 秒取决于硬件。-- 建表并关闭 autovacuum模拟 auto-analyze 尚未运行CREATETABLEorders(idBIGINTGENERATED ALWAYSASIDENTITY,customer_idINTNOTNULL,statusVARCHAR(20)NOTNULL,amountNUMERIC(12,2)NOTNULL,created_atTIMESTAMPNOTNULLDEFAULTNOW())WITH(autovacuum_enabledfalse);INSERTINTOorders(customer_id,status,amount,created_at)SELECTfloor(random()*1000000)::INT1,CASEWHENrandom()0.01THENcancelledELSEcompletedEND,round(random()*10000,2)::NUMERIC(12,2),NOW()-(random()*INTERVAL365 days)FROMgenerate_series(1,10000000);CREATEINDEXidx_orders_customer_idONorders(customer_id);-- ANALYZE 前EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcustomer_id500000;ANALYZEorders;-- ANALYZE 后EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcustomer_id500000;ALTERTABLEorders RESET(autovacuum_enabled);预期输出ANALYZE 前Estimated Rows ≈ 50000、扫描方式为Seq ScanANALYZE 后Estimated Rows ≈ 10、扫描方式为Index Scan。实际输出运行上述脚本第一条EXPLAIN返回Seq Scan on orders第二条返回Index Scan using idx_orders_customer_idpg_class.reltuples从-1变为约 10,000,000。共享读页数随硬件与填充因子变化本文未在写作环境中复测具体页数。常见失败ANALYZE 后计划仍为Seq Scan。原因通常是查询写成了WHERE customer_id 500000字符串字面量触发隐式转换列被表达式包裹而无法使用索引。修复方法改用整数字面量或让绑定参数类型与列一致同时用EXPLAIN确认过滤条件显示为customer_id $1而非customer_id::text $1。五、验证结果与边界指标ANALYZE 前ANALYZE 后reltuples-1~10,000,000n_distinctcustomer_id无记录~1,000,000Estimated Rows~50,000~10扫描方式Seq ScanIndex Scan失效边界同样需要记录假设 ETL 更新了 80 万行的customer_id低于 100 万行的自动阈值统计信息仍停留在旧分布。此时查WHERE customer_id 42优化器按旧选择率估算约 10 行而选择 Index Scan实际返回约 80 万行随机 I/O 被放大数万倍——统计过期时走索引反而比全表扫描更慢。运维上的判断信号是n_mod_since_analyze / reltuples接近 10% 即到达自动阈值边界可据此在变更量达到 7% 时提前告警在自动分析运行前介入。对customer_id这类高偏度列可用ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;提高采样精度代价是 ANALYZE 耗时与pg_statistic存储增加。思考把autovacuum_analyze_scale_factor降到 0.01 后大表上 auto-analyze 的 I/O 开销是否会成为新的抖动源ANALYZE修复后仍有查询走 Seq Scan如何区分正确代价判断与统计再次过期参考资料PostgreSQL 自动清理与 auto-analyze 配置文档12–16PostgreSQL ANALYZE 命令文档PostgreSQL 统计信息与监控视图文档