PostgreSQL / 技术文章

PostgreSQL 索引失效怎么排查

索引已经创建,查询却仍然走 Seq Scan,是 PostgreSQL 排障中最容易被误判的问题之一。本文面向后端工程师、DBA 与 SRE,从执行计划入手,依次检查索引是否真的可用、谓词是否可索引、统计信息是否可信、成本估算是否合理,以及参数化查询是否改变了计划选择;完成后,你应该能够把“索引没生效”进一步定位为具体的计划选择原因,而不是直接重建或新增索引。

先确认:索引是真的没用,还是规划器认为不值得用

排查第一步不要看索引定义,而要看实际执行计划。EXPLAIN 只展示规划结果,EXPLAIN ANALYZE 才会实际执行语句,因此线上排障应谨慎使用,并结合业务流量和语句副作用评估风险。

先用一条简单查询建立判断基线:

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status
FROM orders
WHERE customer_id = 42;

预期结果

text
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 上,下面的写法把列包进了函数:

sql
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 索引进行范围搜索的条件形式。更适合的写法是把范围条件落在原始列上:

sql
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';

预期结果

text
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 无法直接利用字符串前缀来缩小搜索范围;这类需求通常需要重新评估查询形式或索引类型,而不是重复创建同一种索引。

看统计信息:规划器可能不是“不认识索引”,而是不相信结果会很少

索引是否值得使用,很大程度取决于规划器对结果集大小的估算。如果统计信息过旧、数据分布高度偏斜,或者列之间存在明显相关性而统计信息不足,估算行数就可能偏离实际值。

先对目标表更新统计信息并重新查看计划:

sql
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE customer_id = 42
  AND status = 'PAID';

预期结果

text
Planning Time: <TIME> ms
Execution Time: <TIME> ms
rows=<N>  loops=1

真正需要关注的不是某个固定毫秒数,而是计划中的估算行数与实际行数是否明显偏离。若 rows=<估算值> 与 actual rows=<实际值> 差距很大,应继续检查统计信息、数据分布和谓词组合,而不是先修改 random_page_cost 等全局参数。

对于高度偏斜的列,还可以考虑提高该列的统计目标,让规划器获得更细的分布信息。例如:

sql
ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders;

预期结果

text
ALTER TABLE
ANALYZE

这里的重点是让估算更接近现实。提高统计目标会增加分析成本和统计数据量,不应把它当成所有索引未命中的通用修复方案。

判断是不是成本模型在主动放弃索引

当索引确实匹配、统计信息也基本可信时,继续看成本。PostgreSQL 的规划器会比较候选路径的估算成本,而不是贯彻“有索引就必须走索引”的规则。

一个典型场景是结果集占表中很大比例。此时即使存在索引,通过索引先定位、再回表读取大量页面,可能比顺序扫描更便宜。

可以用 enable_seqscan 做诊断实验,但不要把它当生产修复手段:

sql
SET LOCAL enable_seqscan = off;

EXPLAIN
SELECT id
FROM orders
WHERE status = 'PAID';

预期结果

text
Index Scan using orders_status_idx on orders

这个实验回答的是“如果不让规划器优先考虑顺序扫描,现有索引路径能不能成立”,而不是“生产环境应该关闭 Seq Scan”。如果强制偏向索引后计划明显更差,通常说明问题应回到选择性、统计信息、数据物理分布或成本参数继续分析。

参数化查询与通用计划:代码里一样的 SQL,实际计划可能不同

应用使用预编译语句或参数化查询时,还要确认 PostgreSQL 最终采用的是自定义计划还是通用计划。某些参数分布高度不均匀时,同一条 SQL 对不同参数值可能存在完全不同的最优路径:一个值适合索引,另一个值更适合顺序扫描。

排查时应拿真实参数重新执行,并观察计划是否随参数值发生变化。可以在受控环境中比较不同 plan_cache_mode 行为:

sql
SET LOCAL plan_cache_mode = force_custom_plan;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM orders
WHERE customer_id = 42;

预期结果

text
Index Scan using orders_customer_id_idx on orders

这里的价值在于隔离“参数值导致的计划差异”和“索引根本不可用”这两类问题。不要看到某一次参数使用 Seq Scan,就直接认定应用所有请求都会走同一个计划。

用一条证据链收敛问题,而不是反复建索引

实际线上排查可以按以下顺序收敛:先确认 SQL、参数和索引定义;再执行 EXPLAIN (ANALYZE, BUFFERS) 获取真实计划;然后看谓词是否保持可索引形式;接着比较估算行数与实际行数;最后才检查选择性、成本参数、数据分布和参数化计划行为。

如果索引没有被使用,优先回答这四个问题:第一,查询条件是否能够直接利用该索引;第二,规划器估算的结果集规模是否可信;第三,索引路径的估算成本是否真的低于顺序扫描;第四,当前参数是否导致计划发生变化。

排障的正确终点不是“让 EXPLAIN 出现 Index Scan”,而是确认执行计划与业务数据分布相匹配,并通过实际执行时间、BUFFERS 和行数估算误差证明优化确实有效。只有证据表明索引设计本身不足时,才进入新增、调整或重建索引的变更阶段。

目录与阅读设置

记录一个想法

PostgreSQL 索引失效怎么排查

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通