你有没有遇到过这种情况一张表数据量涨到了几百万行查询开始变慢你满怀自信地给查询条件创建了索引结果线上跑起来不但没变快反而更慢了。甚至执行计划里明明显示“索引扫描”整条 SQL 却比之前的“全表扫描”还要久。这不是你的错觉——在 PostgreSQL 的性能调优里“加了索引反而变慢”是新手最容易踩的坑也是老手也会偶尔翻车的地方。我做了十几年的数据库相关工作自己栽过跟头也帮人填过大量类似的坑。这篇文章我不讲虚的就以最典型的 PostgreSQL 场景为例把索引变慢的底层原因、判断方法和建索引的正确姿势完整拆开给后端开发、DBA 和运维同学一份能直接照着排查的避坑手册。1. 索引原理没吃透很容易把“加速器”当成“保险”1.1 PostgreSQL索引的底层逻辑目录和指针的代价先回到最基础的问题索引到底是什么打个比方全表扫描就像你在一本没有目录的书里从头到尾翻一页一页找某个关键词索引则像书末尾的目录页告诉你某个词在第几章、第几节直接翻过去就行。数据库里的 B-tree 索引就是一种“带指针的目录”它的叶子节点保存着索引键值和指向数据行的物理位置ctid查询时先查索引拿到 ctid再回到主表Heap里去取这一行的完整数据这个过程叫回表。但很多人忽略了目录本身也有成本。首先索引是要占磁盘空间的其次表里每次插入、更新、删除数据索引都要同步维护这相当于书的内容每改一次目录也要跟着重编一遍。而回表的性能代价和全表扫描完全不同全表扫描是连续的顺序 IO一次读一大块通过索引回表则像查完目录后一页页跳着去翻典型的是随机 IO。随机 IO 比顺序 IO 慢得多尤其是在机械硬盘上。这就是“索引会让查询变慢”的第一个潜在原因——它把原本的顺序读变成了离散的随机读。只有当通过索引能过滤掉绝大多数数据、回表次数非常少时随机读的总开销才会低于全表扫描。如果筛选后返回的数据仍然占据表的很大比例那索引反而会成为累赘。1.2 索引类型这么多选错类型比不建索引更尴尬PostgreSQL 里最常见的索引类型是 B-tree但它并不是唯一选择。很多人只会用默认创建的 B-tree导致一些特殊场景用错索引或者干脆用了不合适的索引类型拖慢性能。下面这张表是我常用的选型对照索引类型典型适用场景不适用场景B-tree等值、范围、排序、去重、唯一约束全文检索、数组包含Hash简单的等值比较范围查询、排序基本不能用GIN全文检索、JSONB、数组、pg_trgm范围扫描、普通等值GiST地理空间数据、范围类型普通业务精确查询BRIN数据物理顺序和逻辑顺序一致的超大表时间序列小表、数据随机分布举个例子如果字段是数组类型你想查“数组里包含某个元素”用默认的 B-tree 根本无效应该用 GIN如果字段是经纬度坐标做“周围多少米”查询B-tree 使不上劲GiST 才合适。另外Hash 索引在 PG 里只支持等值查询如果查询里带了或ORDER BY优化器只能绕过它。有些 DBA 习惯于把所有索引都建成 B-tree结果遇到特殊类型查询时索引要么不被使用要么查询性能极差。选错类型虽然不是“加了索引变慢”的最高频原因但一旦发生往往排查很久都找不到问题所在。在建任何索引前先花一分钟确认字段类型和查询模式再决定用什么索引访问方式。2. 加了索引反而变慢的四个核心原因2.1 选择性太差回表代价高到超过全表扫描这是我在实际项目中遇到最多的原因。有一段时间我接手一个订单系统查询超时严重我看到 SQL 是WHERE status paid而 status 只有“待支付、已支付、已取消”三个值。当时我第一反应就是给 status 建索引。建完之后单条查询确实走了索引但整个数据库的负载反而更高了接口 RT 变得更不稳定。原因是 status 字段的区分度太低了。一张表有 100 万行符合条件的记录可能有 80 万行走索引意味着要回表 80 万次每次都是随机 IO而全表扫描顺序读一遍 100 万行反而更快。优化器并不是“看到索引就一定要用”它会基于统计信息估算成本如果索引回表代价大于顺序扫描它会果断选择全表扫描。所以表面上你建了索引实际上只是增加了磁盘占用和写入负担查询路径根本没变。判断区分度有一个简单方法执行SELECT count(*) AS total_rows, count(DISTINCT status) AS distinct_values, count(DISTINCT status) * 1.0 / count(*) AS selectivity FROM orders;区分度接近 1 的列适合建索引如果区分度低于 0.1甚至几个固定值就要慎重。对于低区分度字段需要加速的场景可以考虑“部分索引”比如只针对高频的“已支付”状态创建一个WHERE status paid的索引这个索引体积小得多优化器也更倾向使用。这一点后面会详细讲。2.2 写放大一次数据修改要“连坐”所有索引很多人只看查询变快忽略了写入开销。索引本质上是以写换读查询性能提升了但每次INSERT、UPDATE、DELETE都多了一堆索引维护工作。给一张高频写入的表加一个索引可能让插入性能下降 20% 到 50%如果一张表上挂着五六个索引写入时的负担会成倍增加。我之前帮客户优化过一个订单流水表业务方为了各种报表查询加了七个索引。每天深夜批量跑数时插入速度慢得像蜗牛。后来我把索引从七个砍到三个专门替代那些低频查询的索引批量导入直接从一小时缩短到十几分钟。批量导入的正确姿势也值得一说如果是一次性初始化数据可以先DROP INDEX再COPY完成后再一次性重建索引这一步往往比带着索引导入快数倍。对于在线交易系统原则是“少而精”能用复合索引解决的就不要搞多个单列索引写多读少的日志表宁愿牺牲部分查询速度也要控制索引数量。索引不是免费的优化它每时每刻都在从你的写入性能里抽税。2.3 统计信息过期优化器“瞎”了PostgreSQL 的查询规划器不是靠硬写规则决定走不走索引而是依赖pg_statistic里关于表行数、列唯一值数、数据分布、相关性等统计信息进行代价估算。如果这些统计信息过期了规划器估算出来的行数和实际值相差很大就会选错执行计划。我遇到过的一个典型现象是某张表每天新增几十万行数据某条 SQL 上午执行计划是全表扫描下午变成索引扫描晚上又变回全表扫描。我们查了表发现 autovacuum 因为某些原因没有及时触发ANALYZE统计信息一直停留在一个很老的状态。后来手动执行ANALYZE orders;后执行计划恢复正常。避免这个问题首先不要关闭 autovacuum。其次对于数据变化剧烈的大表可以调整阈值ALTER TABLE orders SET (autovacuum_analyze_threshold 10000); ALTER TABLE orders SET (autovacuum_analyze_scale_factor 0.05);这样当表里插入或删除超过 5% 的行数时后台自动做统计信息分析。另外每次大批量数据变更后也建议手动跑一次ANALYZE。统计信息不准再好的索引设计也白搭。2.4 复合索引顺序不对、重复索引带来额外负担热搜词里有个“mysql where条件a and b应该怎么建索引”这在 PostgreSQL 里同样常见。很多人面对WHERE a ? AND b ?习惯于给 a 和 b 分别建单列索引觉得“两个索引总能覆盖所有组合”。但对大多数数据库来说优化器很难高效地把两个单列索引合并起来服务这种查询它通常只会选其中一个然后对另一列做过滤。更合理的做法是建一个复合索引。但复合索引的列顺序非常重要如果把范围条件放前面等值条件放后面很可能无法高效使用。比如查询是SELECT * FROM orders WHERE status shipped AND created_at 2024-01-01;建议建(status, created_at)因为 status 是等值条件放在前面可以精确定位一个“扇区”再在这个扇区内对 created_at 做范围扫描。如果你建的是(created_at, status)优化器无法先利用 status 进行过滤效果就差很多。另外我见过很多表上同时存在(a)和(a,b)两个索引。这种属于典型的重复索引(a,b)已经能作为a的前缀索引使用单独建(a)只会白增加写入成本和磁盘空间。这种多余索引时间长了不仅拖慢写入还会让优化器在选择时多一层计算虽然影响不大但属于典型的“负资产”。3. 实战一条慢SQL从定位到索引设计到底怎么一步步来3.1 开启慢日志把“罪魁祸首”捞出来线上出现性能问题时不要坐在那猜哪条查询慢。先把慢查询日志打开。在postgresql.conf里设置log_min_duration_statement 1000 log_statement none log_line_prefix %t [%p] 意思是只记录执行时间超过 1000 毫秒的语句。改完后pg_ctl reload生效。观察半小时找出耗时最高的几条 SQL然后针对它们做EXPLAIN。这里有个经验很多“索引没生效”的困惑其实都是因为定位错了 SQL——你在分析 A 语句真正慢的是 B 语句。所以让数据说话别凭直觉。3.2 EXPLAIN ANALYZE 输出的几个关键指标拿到目标 SQL 后用真实参数跑一次执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id 42 AND status shipped;重点看几个地方第一行扫描方式是Seq Scan还是Index Scan。actual time和rows比如actual time0.023..2.456 rows5 loops1实际扫描花了多少毫秒返回多少行。rows估算值和实际值是否相差巨大。如果估算 10 万而实际只有 5多半是统计信息不准。Buffersshared hit/read的数量可以判断缓存命中率。我曾经帮人排查过一个“走了索引反而慢”的问题用EXPLAIN看到 Index Scan但actual rows是 60 万回表次数极多导致执行时间比 Seq Scan 还长。所以不要迷信“走索引”三个字重点要看访问了多少行、回表了多少次。如果过滤器能把 90% 的行筛掉索引收益才明显否则优化器选择全表扫描不是 bug反而是合理判断。3.3 建索引前用这个SQL评估字段选择性决定给一张表的某个字段建索引前至少执行一次这个评估查询SELECT count(*) AS total_rows, count(DISTINCT col_a) AS distinct_a, count(DISTINCT col_b) AS distinct_b, round(count(DISTINCT col_a) * 1.0 / count(*), 6) AS selectivity_a FROM table_name;如果selectivity_a很低说明该字段不同值很少。这个时候建普通索引的收益通常不大。再看pg_stats里的相关性和唯一值数量SELECT attname, n_distinct, correlation FROM pg_stats WHERE tablename orders;correlation接近 1 表示列数据的物理存储顺序与逻辑顺序一致这种列甚至可以考虑 BRIN 索引相关性很低则说明随机分布BRIN 不合适B-tree 仍是首选。选型不是拍脑袋这些小查询花不了几秒钟却能避免后续几个星期的问题。3.4 覆盖索引、部分索引、表达式索引让索引跑得更快覆盖索引如果高频查询只需要少数几列比如SELECT status, created_at FROM orders WHERE customer_id 42可以建一个覆盖索引CREATE INDEX idx_orders_customer_include ON orders (customer_id) INCLUDE (status, created_at);这样查询直接从索引叶子页返回数据完全不需要回表速度会有质的提升。注意 INCLUDE 列不宜过大否则索引体积膨胀反而影响扫描效率。部分索引如果业务查询总是限定某个状态比如只查status shipped可以创建一个部分索引CREATE INDEX idx_orders_shipped ON orders (customer_id) WHERE status shipped;这个索引里的数据量会小很多维护成本低查询时优化器也更愿意选择它。部分索引特别适合低选择性字段上的“定点加速”。表达式索引当查询对列做了函数操作时普通索引会失效。比如WHERE lower(email) testexample.com需要建CREATE INDEX idx_users_lower_email ON users (lower(email));类似地对时间字段做::date转换时也应该改写查询条件而不是盲目建函数索引。建表达式索引前要仔细确认函数是immutable的否则索引要么建不出来要么不被使用。4. 索引失效的隐藏场景和排查速查表4.1 对索引列做运算或函数处理这是最容易被忽视的“索引杀手”。B-tree 索引依赖于“原始列值”的排序一旦查询条件里对列做了运算比如WHERE price * 0.9 100或者WHERE created_at interval 1 day now()优化器无法利用price或created_at上的普通索引因为每一行都得先算出表达式结果才能比较。正确写法是改写不等式让列保持原样WHERE price 100 / 0.9 WHERE created_at now() - interval 1 day这条规则几乎是所有数据库的通用准则PostgreSQL 也一样。每次写完查询条件先自查一遍条件列是不是被函数或加减乘除包住了4.2 隐式类型转换还有一类索引失效来自隐式类型转换。比如phone字段是varchar(20)查询写成WHERE phone 13800001111等号右边是数字字面量PostgreSQL 可能需要将phone转换为数字再比较转换过程中索引就无法高效匹配或者产生Index Cond: (phone 13800001111::bigint)这样的隐式 cast。所以绑定参数时一定要保持类型一致WHERE phone 13800001111。这类问题在从 MySQL 迁移到 PostgreSQL 的团队里尤其常见因为两个数据库对类型转换的处理策略不同我会在排查时特别留意EXPLAIN输出里的条件部分。4.3 LIKE 前置通配符和无序数据的索引失效LIKE abc%可以利用 B-tree 索引因为字符串排序后前缀相同的行在索引中是连续存放的。但LIKE %abc或LIKE %abc%是模糊查找普通索引失效。如果业务确实需要包含式模糊搜索可以考虑 pg_trgm 扩展和 GIN 索引CREATE EXTENSION pg_trgm; CREATE INDEX idx_orders_remark_trgm ON orders USING gin (remark gin_trgm_ops);注意pg_trgm 对中文分词支持一般按字符拆分的效果不一定好。另外LIKE 前缀匹配在默认 C locale 或非 deterministic collation 下也可能有兼容问题必要时可以使用text_pattern_ops操作符类。这个坑比较深通常会在 collation 设置特殊的 PostgreSQL 集群里暴露出来。4.4 OR 条件、不等值条件和 NULL 的影响OR是另一个容易让优化器“放弃治疗”的写法。如果 SQL 是WHERE customer_id 42 OR status shipped两个条件都能各自走索引时优化器可能使用 BitmapOr但如果其中一个条件特别宽泛或者其中一个字段没有索引整体很可能退化为 Seq Scan。所以我一般建议把OR改写为UNION ALL或者将高频分支单独查出来再合并。NOT IN、这类不等值条件也很难高效使用索引因为它要扫描所有不匹配的值和范围查询的代价差不多。IS NULL判断在普通 B-tree 中通常也无法走到索引除非创建部分索引CREATE INDEX idx_orders_comment_missing ON orders (id) WHERE comment IS NULL;这种部分索引专门服务“查空值”的场景比全字段索引小得多。所以看到 NULL 判断导致慢查询时不要无脑加索引先看查询模式能不能用部分索引覆盖。4.5 统计信息又被坑了手动 ANALYZE 的节点排除掉上面所有情况后如果查询还是不走索引先手动更新一下统计信息ANALYZE orders;然后再跑一次执行计划。有些时候只是统计信息长期没有刷新导致估算严重失真ANALYZE 之后优化器看到了真实的数据分布自然会更换计划。如果你手动 ANALYZE 之后还是全表扫描那就说明在这种数据量级和选择条件下全表扫描确实是更优解不必强求走索引。这一点在给客户做调优时特别重要——很多开发同学非要“强制走索引”其实只是心理安慰反而会让整体性能更差。5. 长期维护怎么判断哪些索引是“负资产”5.1 用系统视图找出从未被使用过的索引优化完一个阶段后还需要定期清理“僵尸索引”。PostgreSQL 的统计视图pg_stat_user_indexes会记录每个索引的扫描次数执行这个查询SELECT schemaname, tablename, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC;前面几行就是使用率极低的索引。如果一张表上有多条索引其中几条idx_scan长期为 0说明业务根本没有使用它们。这些索引唯一的贡献就是拖慢写入、占用磁盘可以评估后删除。但有一个例外唯一约束或主键对应的索引不能用idx_scan判断因为它们即使没被查询扫描也在执行约束校验删不得。另外删除索引前最好在测试环境模拟真实查询确认没有 SQL 因为统计视图的滞后而被影响。5.2 索引膨胀看大小比和选REINDEX时机PostgreSQL 的 MVCC 机制让表在频繁更新和删除时产生大量死元组索引里的对应条目不会立即清理随之而来的是索引膨胀——逻辑上没那么多数据物理文件却越来越大扫描路径变长性能下降。要检查索引膨胀可以使用pgstattuple扩展CREATE EXTENSION pgstattuple; SELECT * FROM pgstatindex(idx_orders_customer_id);重点关注avg_leaf_density和dead_items。如果avg_leaf_density低于 30%或者dead_items数量很大就该重建索引了。安全的重建方式是REINDEX INDEX CONCURRENTLY idx_orders_customer_id;使用CONCURRENTLY不会阻塞读写对在线业务更友好但它占用的资源更高也不要在事务块里执行。我通常在业务低峰期跑并且关注系统 IO 压力。如果索引膨胀持续出现要回头检查 autovacuum 参数是否合理否则今天重建完过几周又膨胀了。5.3 索引表空间把索引搬到独立磁盘热搜词里出现“索引表空间”估计有朋友在研究能不能通过表空间来优化性能。PostgreSQL 确实支持CREATE TABLESPACE idx_ts OWNER postgres LOCATION /data/pgidx; CREATE INDEX idx_orders_customer ON orders (customer_id) TABLESPACE idx_ts;把索引放到单独的磁盘或 SSD 上可以减少主表 IO 与索引 IO 的竞争。但这个方法存在局限查询不仅要读索引还要回表如果主表所在的磁盘仍然繁忙那么索引搬走的效果可能不明显。我在实战中只在主表满负载、有富余 IO 资源的环境下使用过效果确实有但不建议作为常规优化手段。绝大多数性能问题还是回到索引本身的设计是否合理表空间属于锦上添花。5.4 PostgreSQL版本选择的附带建议顺带回应一下热搜词里很多人关心的“postgresql下载哪个版本”“postgresql 16便携版”“postgresql 17”。如果你是从零开始搭建生产环境我建议直接用最新的稳定版本比如 17 或 16这两个版本在优化器、索引维护和并行查询方面都有改进。但如果你的线上环境已经运行了很久遇到了索引变慢的问题不要轻易用“换版本”来解——版本差异导致的索引性能问题很少绝大多数问题来自索引设计、统计信息和配置。先按前面的排查流程走找到根因再动版本。升级有升级的成本临时环境测试要跟上否则很容易引入新问题。最后再分享一个我个人的判断标准给表加索引之前先拿到真实的慢 SQL用EXPLAIN ANALYZE看执行计划算一下选择性再决定建什么索引而不是靠“这个字段经常查一定得加索引”的感觉。我见过太多表上挂着七八个自认为“优化”过的索引结果查询没快多少每天的写入吞吐量反倒被拖累了。删掉那些僵尸索引后负载能轻松降下一截。踩过这些年坑我现在建索引的原则只有一句话能用部分索引解决的绝不全量索引能用覆盖索引解决的绝不回表。希望大家少走弯路。