在PostgreSQL（简称PG）中，索引失效会导致查询性能急剧下降，其原因主要分为索引本身无法被优化器使用、数据或统计信息异常、索引设计不合理等几类。以下是索引失效的常见场景及对应的处理方式，结合PG的特性展开说明：
一、索引失效的常见场景
（一）查询条件导致索引无法被使用
这是最常见的场景，查询语句的写法直接让优化器放弃使用索引。
-
索引列参与函数/表达式运算
若在WHERE子句中对索引列使用函数、算术运算或表达式，PG优化器无法直接匹配索引的键值，会触发全表扫描（Seq Scan）。
示例：-- 创建了idx_user_create_time索引（create_time为timestamp类型） SELECT * FROM "user" WHERE DATE(create_time) = '2024-01-01'; -- 函数运算导致索引失效 SELECT * FROM "user" WHERE id + 1 = 100; -- 算术运算导致索引失效 -
索引列使用隐式类型转换
PG对数据类型严格，若查询条件中值的类型与索引列类型不匹配，会触发隐式转换（本质也是函数运算），导致索引失效。
示例：-- id为bigint类型，创建了idx_user_id索引 SELECT * FROM "user" WHERE id = '100'; -- 字符串转数字，隐式转换导致索引失效 -- mobile为varchar类型，创建了idx_user_mobile索引 SELECT * FROM "user" WHERE mobile = 13800138000; -- 数字转字符串，隐式转换导致索引失效 -
使用
NOT IN/<>``NOT EXISTS（非等值查询）
对于B树索引（PG默认索引类型），NOT IN、<>、NOT EXISTS等非等值查询通常无法高效利用索引，优化器可能选择全表扫描（除非索引列的基数极低）。
示例：SELECT * FROM "user" WHERE id <> 100; -- <>导致B树索引失效 SELECT * FROM "user" WHERE id NOT IN (1,2,3); -- NOT IN导致索引失效 -
模糊查询以
%开头
对于varchar/text类型的索引列，LIKE '%xxx'或LIKE '%xxx%'的模糊查询无法使用B树索引（B树索引是按前缀排序的，后缀/中间模糊匹配无法定位）。
示例：-- 创建了idx_user_name索引（name为varchar类型） SELECT * FROM "user" WHERE name LIKE '%张三'; -- %开头导致B树索引失效 -
多列索引（复合索引）不满足最左前缀原则
复合索引的使用需遵循最左前缀原则，即查询条件中必须包含索引的第一个列，否则索引无法被使用。
示例：-- 创建了复合索引idx_user_age_gender (age, gender) SELECT * FROM "user" WHERE gender = '男'; -- 未使用第一个列age，索引失效 -
OR条件中包含非索引列
若OR连接的条件中，有一个列未建立索引，优化器可能会放弃使用索引（PG对OR的优化较弱，除非所有列都有独立索引且能触发位图索引扫描）。
示例：-- 仅创建了idx_user_id索引，未创建idx_user_name索引 SELECT * FROM "user" WHERE id = 100 OR name = '张三'; -- OR包含非索引列，索引失效
（二）数据或统计信息异常导致索引被放弃
即使索引本身有效，数据分布或统计信息的问题也会让优化器认为全表扫描更高效。
-
表数据量过小
当表的行数极少（如几十行），PG优化器会认为全表扫描的开销比索引扫描更低，因此不会使用索引。 -
索引列的基数极低
基数（Cardinality）指索引列的唯一值数量。若索引列的基数极低（如性别列，只有“男”“女”两个值），使用索引的收益远低于全表扫描，优化器会放弃索引。 -
统计信息过期或不准确
PG的查询优化器依赖统计信息（存储在pg_statistic系统表中）来判断是否使用索引。若统计信息过期（如表经过大量插入/更新/删除后未分析），优化器可能做出错误的判断。
示例：-- 对表进行大批量删除后，统计信息未更新 DELETE FROM "user" WHERE create_time < '2020-01-01'; -- 优化器仍认为表数据量很大，可能错误选择索引扫描（或反之） -
索引列存在大量NULL值
若索引列中NULL值占比极高，而查询条件为WHERE col IS NOT NULL，优化器可能认为全表扫描更高效（B树索引会存储NULL值，但大量NULL时索引效率低）。
（三）索引本身的问题
索引的物理结构或状态异常也会导致其无法被使用。
-
索引被标记为无效（INVALID）
若创建索引时使用CONCURRENTLY但中途失败，或手动执行ALTER INDEX ... INVALIDATE，索引会被标记为INVALID，无法被使用。
可通过\d 表名或查询pg_index查看索引状态：SELECT indexrelname, indisvalid FROM pg_index WHERE indrelid = 'user'::regclass; -
索引碎片过多
表经过大量的更新、删除操作后，索引会产生大量碎片（空洞），导致索引扫描的效率下降，甚至优化器会放弃使用索引。 -
索引类型选择错误
选择了不适合场景的索引类型，例如用B树索引处理全文模糊查询，用哈希索引处理范围查询（PG的哈希索引不支持范围查询）。
二、索引失效的处理方式
针对上述场景，可采取对应的优化措施，核心原则是让优化器能高效匹配索引、保证统计信息准确、优化索引设计。
（一）优化查询语句，让索引可被使用
-
避免索引列参与函数/表达式运算
将函数运算转移到查询值上，而非索引列上。
示例：-- 优化前：DATE(create_time) = '2024-01-01' SELECT * FROM "user" WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';-- 优化前：id + 1 = 100 SELECT * FROM "user" WHERE id = 99; -
避免隐式类型转换
保证查询值的类型与索引列类型一致。
示例：-- 优化前：id = '100'（id为bigint） SELECT * FROM "user" WHERE id = 100;-- 优化前：mobile = 13800138000（mobile为varchar） SELECT * FROM "user" WHERE mobile = '13800138000'; -
替换非等值查询为等值/范围查询
- 用
LEFT JOIN + IS NULL替代NOT IN/NOT EXISTS（更高效且能利用索引）； - 若业务允许，将
<>转换为范围查询（如id <> 100转换为id < 100 OR id > 100，可利用B树索引）。
示例：
-- 优化前：NOT IN SELECT * FROM "user" u WHERE u.id NOT IN (SELECT id FROM user_blacklist); -- 优化后：LEFT JOIN + IS NULL SELECT u.* FROM "user" u LEFT JOIN user_blacklist b ON u.id = b.id WHERE b.id IS NULL; - 用
-
优化模糊查询
- 若需后缀模糊匹配（
%xxx）或全模糊匹配（%xxx%），可使用GIN索引+pg_trgm扩展（trigram索引）； - 若业务允许，改为前缀模糊匹配（
xxx%），可直接使用B树索引。
示例：
-- 安装pg_trgm扩展 CREATE EXTENSION IF NOT EXISTS pg_trgm; -- 为name列创建GIN索引（支持任意模糊匹配） CREATE INDEX idx_user_name_trgm ON "user" USING GIN (name gin_trgm_ops); -- 此时LIKE '%张三%'可使用索引 SELECT * FROM "user" WHERE name LIKE '%张三%'; - 若需后缀模糊匹配（
-
复合索引遵循最左前缀原则
- 查询条件中必须包含复合索引的最左列；
- 若经常需要单独查询某列，可为该列创建独立索引。
示例：
-- 复合索引idx_user_age_gender (age, gender) -- 优化前：WHERE gender = '男'（索引失效） -- 优化后：要么添加age条件，要么为gender创建独立索引 CREATE INDEX idx_user_gender ON "user" (gender); -
替换OR为UNION ALL
若OR条件中包含不同的索引列，可使用UNION ALL替代OR，让每个子查询使用各自的索引。
示例：-- 优化前：WHERE id = 100 OR name = '张三'（索引失效） -- 优化后：UNION ALL（id和name分别使用索引） SELECT * FROM "user" WHERE id = 100 UNION ALL SELECT * FROM "user" WHERE name = '张三' AND id <> 100; -- 避免重复
（二）保证数据和统计信息的准确性
-
更新统计信息
当表数据发生大量变化后，手动执行ANALYZE更新统计信息，让优化器做出正确的判断。-- 分析指定表 ANALYZE "user"; -- 分析整个数据库（生产环境慎用，耗时较长） ANALYZE;PG也支持自动分析（通过
autovacuum进程），可通过以下参数调整：autovacuum_analyze_scale_factor：表数据变化比例达到该值时触发自动分析（默认0.1，即10%）；autovacuum_analyze_threshold：表数据变化行数达到该值时触发自动分析（默认50行）。
-
处理低基数列
- 若低基数列需经常查询，可结合其他列创建复合索引（提高基数）；
- 对于极低频的查询，接受全表扫描即可。
-
处理大量NULL值的列
- 若查询条件为
col IS NOT NULL，可使用部分索引（Partial Index），只索引非NULL值，减少索引体积。
示例：
-- 为create_time非NULL的行创建部分索引 CREATE INDEX idx_user_create_time_not_null ON "user" (create_time) WHERE create_time IS NOT NULL; - 若查询条件为
（三）修复和优化索引本身
-
重建无效索引
若索引被标记为INVALID，需重建索引：-- 重建索引（锁表，适合离线场景） REINDEX INDEX idx_user_create_time; -- 并发重建索引（不锁表，适合生产环境） REINDEX INDEX CONCURRENTLY idx_user_create_time; -
清理索引碎片
可通过REINDEX或VACUUM FULL清理索引碎片（VACUUM FULL会重写表和索引，锁表，需谨慎）：-- 并发重建索引（推荐，不锁表） REINDEX TABLE CONCURRENTLY "user"; -- 或VACUUM FULL（锁表，适合碎片极多的情况） VACUUM FULL "user"; -
选择合适的索引类型
根据查询场景选择索引类型，常见索引类型的适用场景：索引类型 适用场景 B树 等值查询、范围查询、排序 GIN 数组、JSONB、trigram模糊查询 GIST 地理数据（如PostGIS）、范围类型 哈希 仅等值查询（PG10+后B树已优化，哈希索引使用较少） -
删除无用索引
多余的索引会增加写入开销（插入/更新/删除时需维护索引），可通过pg_stat_user_indexes查看索引的使用情况，删除未被使用的索引：-- 查看索引的使用统计（需开启track_io_timing等参数） SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'user' ORDER BY idx_scan ASC;
三、索引失效的排查方法
当怀疑索引失效时，可通过以下步骤定位问题：
-
使用EXPLAIN分析执行计划
这是最核心的方法，通过EXPLAIN（或EXPLAIN ANALYZE）查看查询是否使用了索引：-- 查看执行计划（不执行查询） EXPLAIN SELECT * FROM "user" WHERE create_time >= '2024-01-01'; -- 执行查询并输出实际执行计划（生产环境慎用，耗时查询会执行） EXPLAIN ANALYZE SELECT * FROM "user" WHERE create_time >= '2024-01-01';执行计划中若出现
Seq Scan（全表扫描）则说明索引未被使用；若出现Index Scan/Bitmap Index Scan则说明索引被使用。 -
检查索引状态
查询pg_index确认索引是否有效：SELECT indexrelname, indisvalid, indisready FROM pg_index WHERE indrelid = '表名'::regclass; -
检查统计信息
查看表的统计信息是否最新：SELECT relname, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE relname = 'user';
四、总结
PG中索引失效的核心原因是查询语句无法匹配索引、优化器基于统计信息的判断、索引本身的问题。处理的关键是：
- 优化查询写法，让索引能被高效匹配；
- 保证统计信息准确，让优化器做出正确选择；
- 合理设计索引，选择合适的索引类型并维护索引状态。
通过EXPLAIN分析执行计划是排查索引失效的首要手段，结合PG的系统表和扩展工具（如pg_stat_statements）可更全面地定位和解决问题。
- 公众号
行走之飞鱼