PostgreSQL 索引失效与查询优化实战:先看执行计划,再谈加索引
PostgreSQL 索引失效与查询优化实战:先看执行计划,再谈加索引
很多慢查询的根因不是缺索引,而是索引建了却没被用上。本文结合一次真实排查,讲清楚索引失效的常见形态、如何用 EXPLAIN 定位,以及改写查询的先后顺序。
一、先确认:真的是索引问题吗
接到慢查询报警,第一步不是加索引,而是跑 EXPLAIN (ANALYZE, BUFFERS) SELECT ...。重点看三处:实际行数与预估行数的差距、是否走了 Seq Scan、Buffers 里命中与读盘的比例。如果预估行数差一个数量级,往往是统计信息过期,先 ANALYZE 表名 再复测;如果走了全表扫描但返回行数很少,才值得怀疑索引失效。
一个容易被忽略的点是数据分布。同样是 status = 'pending',当待处理占 80% 时全表扫描反而更快,优化器不走索引是正确选择。不要为了“走索引”而强行加 hint,优化器的选择要结合选择度判断。
二、五种典型失效形态
第一,对列做函数或运算。WHERE DATE(created_at) = '2026-10-30'、WHERE lower(email) = ...、WHERE price * 1.1 > 100 都会让普通 B-tree 索引失效。办法是改写查询去掉函数,或者建表达式索引,例如 CREATE INDEX ON users (lower(email)),但表达式索引要与查询写法严格一致。
第二,隐式类型转换。字段是 bigint,查询却传了字符串;字段是 varchar,查询却用了数字比较。PostgreSQL 会先转换列的类型,导致索引不可用。统一参数类型是最便宜的修复,ORM 里尤其要检查占位符类型。
第三,OR 与 NOT 的组合。WHERE a = 1 OR b = 2 只有当 a、b 都有索引时才可能走 BitmapOr;WHERE NOT (status = 'deleted')、WHERE col != ... 通常无法有效利用索引。改写思路是拆成 UNION,或者把否定条件转成正向枚举。
第四,前缀模糊匹配。LIKE '%关键词%' 无法走普通 B-tree,只能考虑 pg_trgm 的 GIN 索引;LIKE '关键词%' 则可以走索引,但要注意排序规则与 varchar_pattern_ops。全文检索场景直接用 tsvector,不要拿 LIKE 硬扛。
第五,联合索引列顺序错误。(user_id, created_at) 的索引可以服务 user_id = ? 和 user_id = ? AND created_at > ?,却服务不了只查 created_at > ? 的查询。建联合索引前先列出高频查询的等值列在前、范围列在后,必要时再补一个单列索引。
三、改写优先于加索引
我的排查顺序固定是:修正写法与类型、更新统计信息、调整已有索引、最后才新建索引。新建索引也有成本:写放大、占用空间、拖慢写入。生产库加索引要用 CREATE INDEX CONCURRENTLY,避免锁表;大表还要评估构建期间的 IO 压力,尽量放在低峰期。
另外注意索引膨胀。频繁更新的表会出现死元组堆积,索引体积虚胖,定期 VACUUM 与合理的 autovacuum 参数比一味加索引更重要。可以用 pgstattuple 抽查索引膨胀率,再决定是否 REINDEX。
四、一个可复用的检查清单
上线前问自己五个问题:执行计划是否符合预期、返回行数占表比例是否足够小、查询条件是否对列做了运算、参数类型是否与字段一致、联合索引的顺序是否覆盖了高频条件。把这五个问题写进 Code Review 模板,慢查询数量会明显下降。
索引优化不是玄学,执行计划会告诉你真相。先读计划,再动手,慢查询大多在改写阶段就解决了。
- 点赞
- 收藏
- 关注作者
评论(0)