PostgreSQL / 技术文章
PostgreSQL 索引失效怎么排查
索引已经创建,查询却仍然走 Seq Scan,是 PostgreSQL 排障中最容易被误判的问题之一。本文面向后端工程师、DBA 与 SRE,从执行计划入手,依次检查索引是否真的可用、谓词是否可索引、统计信息是否可信、成本估算是否合理,以及参数化查询是否改变了计划选择;完成后,你应该能够把“索引没生效”进一步定位为具体的计划选择原因,而不是直接重建或新增索引。
先确认:索引是真的没用,还是规划器认为不值得用
排查第一步不要看索引定义,而要看实际执行计划。EXPLAIN 只展示规划结果,EXPLAIN ANALYZE 才会实际执行语句,因此线上排障应谨慎使用,并结合业务流量和语句副作用评估风险。
先用一条简单查询建立判断基线:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status
FROM orders
WHERE customer_id = 42;预期结果
Index Scan using orders_customer_id_idx on orders
Index Cond: (customer_id = 42)如果看到 Seq Scan,不要立即得出“索引失效”的结论。规划器可能判断顺序扫描的总成本更低,例如目标行占表中比例很高、表本身很小,或者随机访问代价使索引路径不划算。
还要区分几个关键词:Index Scan 表示通过索引定位并访问堆表;Bitmap Index Scan 与 Bitmap Heap Scan 说明索引参与了访问路径,但不是逐条索引扫描;Seq Scan 则表示当前计划没有使用这个索引作为访问路径。
检查索引与查询条件是否真的匹配
确认计划后,再检查查询谓词。最常见的问题是查询条件表达式与索引列之间存在函数、计算、类型转换或其他改变可索引形式的操作。
例如索引建立在 created_at 上,下面的写法把列包进了函数:
CREATE INDEX orders_created_at_idx
ON orders (created_at);
EXPLAIN
SELECT count(*)
FROM orders
WHERE date(created_at) = DATE '2026-09-07';这种写法不应简单理解为“函数一定导致索引失效”,但它改变了规划器可以直接利用普通 B-tree 索引进行范围搜索的条件形式。更适合的写法是把范围条件落在原始列上:
EXPLAIN
SELECT count(*)
FROM orders
WHERE created_at >= TIMESTAMP '2026-09-07 00:00:00'
AND created_at < TIMESTAMP '2026-09-08 00:00:00';预期结果
Index Scan using orders_created_at_idx on orders
Index Cond: ((created_at >= '2026-09-07 00:00:00'::timestamp without time zone)
AND (created_at < '2026-09-08 00:00:00'::timestamp without time zone))还要检查数据类型是否一致。例如索引列是整数,却在应用侧长期传入不匹配的类型,可能引入隐式转换,导致计划无法按预期使用索引。实际排查时应直接查看表定义、索引定义和最终发送到 PostgreSQL 的 SQL 与参数类型,而不是只看业务代码中的变量声明。
对 B-tree 来说,LIKE '%abc' 这类前导通配符也是典型边界:普通 B-tree 无法直接利用字符串前缀来缩小搜索范围;这类需求通常需要重新评估查询形式或索引类型,而不是重复创建同一种索引。
看统计信息:规划器可能不是“不认识索引”,而是不相信结果会很少
索引是否值得使用,很大程度取决于规划器对结果集大小的估算。如果统计信息过旧、数据分布高度偏斜,或者列之间存在明显相关性而统计信息不足,估算行数就可能偏离实际值。
先对目标表更新统计信息并重新查看计划:
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE customer_id = 42
AND status = 'PAID';预期结果
Planning Time: <TIME> ms
Execution Time: <TIME> ms
rows=<N> loops=1真正需要关注的不是某个固定毫秒数,而是计划中的估算行数与实际行数是否明显偏离。若 rows=<估算值> 与 actual rows=<实际值> 差距很大,应继续检查统计信息、数据分布和谓词组合,而不是先修改 random_page_cost 等全局参数。
对于高度偏斜的列,还可以考虑提高该列的统计目标,让规划器获得更细的分布信息。例如:
ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;预期结果
ALTER TABLE
ANALYZE这里的重点是让估算更接近现实。提高统计目标会增加分析成本和统计数据量,不应把它当成所有索引未命中的通用修复方案。
判断是不是成本模型在主动放弃索引
当索引确实匹配、统计信息也基本可信时,继续看成本。PostgreSQL 的规划器会比较候选路径的估算成本,而不是贯彻“有索引就必须走索引”的规则。
一个典型场景是结果集占表中很大比例。此时即使存在索引,通过索引先定位、再回表读取大量页面,可能比顺序扫描更便宜。
可以用 enable_seqscan 做诊断实验,但不要把它当生产修复手段:
SET LOCAL enable_seqscan = off;
EXPLAIN
SELECT id
FROM orders
WHERE status = 'PAID';预期结果
Index Scan using orders_status_idx on orders这个实验回答的是“如果不让规划器优先考虑顺序扫描,现有索引路径能不能成立”,而不是“生产环境应该关闭 Seq Scan”。如果强制偏向索引后计划明显更差,通常说明问题应回到选择性、统计信息、数据物理分布或成本参数继续分析。
参数化查询与通用计划:代码里一样的 SQL,实际计划可能不同
应用使用预编译语句或参数化查询时,还要确认 PostgreSQL 最终采用的是自定义计划还是通用计划。某些参数分布高度不均匀时,同一条 SQL 对不同参数值可能存在完全不同的最优路径:一个值适合索引,另一个值更适合顺序扫描。
排查时应拿真实参数重新执行,并观察计划是否随参数值发生变化。可以在受控环境中比较不同 plan_cache_mode 行为:
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE customer_id = 42;预期结果
Index Scan using orders_customer_id_idx on orders这里的价值在于隔离“参数值导致的计划差异”和“索引根本不可用”这两类问题。不要看到某一次参数使用 Seq Scan,就直接认定应用所有请求都会走同一个计划。
用一条证据链收敛问题,而不是反复建索引
实际线上排查可以按以下顺序收敛:先确认 SQL、参数和索引定义;再执行 EXPLAIN (ANALYZE, BUFFERS) 获取真实计划;然后看谓词是否保持可索引形式;接着比较估算行数与实际行数;最后才检查选择性、成本参数、数据分布和参数化计划行为。
如果索引没有被使用,优先回答这四个问题:第一,查询条件是否能够直接利用该索引;第二,规划器估算的结果集规模是否可信;第三,索引路径的估算成本是否真的低于顺序扫描;第四,当前参数是否导致计划发生变化。
排障的正确终点不是“让 EXPLAIN 出现 Index Scan”,而是确认执行计划与业务数据分布相匹配,并通过实际执行时间、BUFFERS 和行数估算误差证明优化确实有效。只有证据表明索引设计本身不足时,才进入新增、调整或重建索引的变更阶段。