PostgreSQL 索引失效与查询优化实战:先看执行计划,再谈加索引

举报
yd_277355409 发表于 2026/10/31 07:14:19 2026/10/31
【摘要】 PostgreSQL 索引失效与查询优化实战:先看执行计划,再谈加索引很多慢查询的根因不是缺索引,而是索引建了却没被用上。本文结合一次真实排查,讲清楚索引失效的常见形态、如何用 EXPLAIN 定位,以及改写查询的先后顺序。 一、先确认:真的是索引问题吗接到慢查询报警,第一步不是加索引,而是跑 EXPLAIN (ANALYZE, BUFFERS) SELECT ...。重点看三处:实际行数...

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 模板,慢查询数量会明显下降。

索引优化不是玄学,执行计划会告诉你真相。先读计划,再动手,慢查询大多在改写阶段就解决了。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。