你有没有遇到过这种情况：一张表数据量涨到了几百万行，查询开始变慢，你满怀自信地给查询条件创建了索引，结果线上跑起来不但没变快，反而更慢了。甚至执行计划里明明显示“索引扫描”，整条 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 根本无效，应该用 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 time=0.023..2.456 rows=5 loops=1，实际扫描花了多少毫秒，返回多少行。
• rows估算值和实际值是否相差巨大。如果估算 10 万而实际只有 5，多半是统计信息不准。
• Buffers：shared 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) = 'test@example.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看执行计划，算一下选择性，再决定建什么索引，而不是靠“这个字段经常查，一定得加索引”的感觉。我见过太多表上挂着七八个自认为“优化”过的索引，结果查询没快多少，每天的写入吞吐量反倒被拖累了。删掉那些僵尸索引后，负载能轻松降下一截。踩过这些年坑，我现在建索引的原则只有一句话：能用部分索引解决的，绝不全量索引；能用覆盖索引解决的，绝不回表。希望大家少走弯路。