PostgreSQL索引失效解析：为什么加了索引反而变慢？

1. 索引失效的真相：为什么索引不是越多越好

很多刚接触 PostgreSQL 的开发者，都会把索引当成数据库性能优化的“万能钥匙”。表查询慢了，加索引；排序慢了，加索引；甚至字段出现在 WHERE 子句里，也习惯性地给加上。这个思路本身没有错，索引确实是关系型数据库提升查询性能最直接有效的手段之一，但它绝对不是一个无成本的免费工具。

我在实际项目里遇到过太多类似的情况：开发环境里跑得好好的 SQL，加上某个索引之后，生产环境反而慢了一倍；明明 WHERE 条件里的字段已经建了索引，执行计划却走全表扫描；有时候刚刚建完索引，查询变快了，但跑了一段时间之后，又慢慢变回去了。这些问题本质上都指向同一个事实——索引是一把双刃剑。它能加速查询，就会带来写入代价；它能帮优化器缩小数据范围，就会让优化器做出错误判断。

索引的本质是拿空间换时间，用额外的存储结构来减少需要扫描的数据量。但 PostgreSQL 的查询优化器非常“理性”，它不会因为你建了索引就无条件使用。优化器会根据表的统计信息、数据分布、系统资源等因素，估算不同执行计划的成本，然后选出它认为代价最低的那一个。如果你的索引设计不合理，导致优化器判断走索引反而更贵，那么索引自然不会被使用。或者更糟的情况是，索引被用上了，但代价比全表扫描还高，这种时候数据库就会陷入“看似优化、实则劣化”的陷阱。

这种情况在数据库领域有个专门术语叫“索引失效”，但在 PostgreSQL 里，更准确的说法是“优化器认为索引不可取”。两种说法的差异很重要：前者听起来像是索引本身出了问题，后者才是本质——不是索引坏了，而是你的建索引方式、统计信息、或者数据分布导致优化器做出了错误决策。

我们经常接触的 MySQL，和 PostgreSQL 在索引实现和优化器行为上有一些显著差异。最典型的一个区别是 PostgreSQL 的优化器是基于成本的，会对每个可选执行计划做完整的代价评估。这意味着同样一条 SQL，同样一张表上的索引，可能在一种数据分布下飞快，换一种数据分布就变成灾难。这是 PostgreSQL 灵活性的体现，但也意味着使用者需要更深刻地理解索引背后的机制。接下来，我会逐层剖析这个问题，从索引为什么变慢，到如何判断是不是索引的问题，再到怎么针对性地优化，把这条路的坑一步一步踩平。

我自己的体会是：凡是把数据库性能问题简单地归结为“加索引”的人，最后都会被“加了索引反而变慢”的现实打脸。真正的高手，先看执行计划，再分析数据分布，最后才决定要不要建索引，以及建一个什么样的索引。希望通过这篇文章，能让更多打算给 PostgreSQL 加索引的同学，把自己的思维模式调整到这个正道上来。

2. 索引为什么会拖慢查询：五层原因逐层拆解

要搞清楚“加了索引反而变慢”这件事，得先从 PostgreSQL 索引的执行路径说起。一个普通的 B-Tree 索引查询，在理想情况下需要经历几个阶段：优化器根据条件估算目标行数，决定走索引扫描；然后从索引根节点逐层向下定位到叶子节点；找到叶子节点上的条目后，拿到行指针（TID），再到堆表（Heap）中去取具体的数据行。整个过程看上去干脆利落，比全表扫描一个一个比对高效得多。但问题恰恰藏在这些步骤的细节里。

2.1 选择性过低导致索引优势荡然无存

索引扫描的意义，在于它能大幅减少数据库必须读取的数据量。如果某个查询条件能过滤掉 99% 的行，那么通过索引只读取那 1% 的行，显然是划算的。但如果这个条件只能过滤掉 20% 甚至 5% 的行，情况就微妙了。因为走索引同样需要付出定位、读取索引页、回表取数据的成本，反而是全表扫描配合顺序读取更快。

这就是查询优化器中“选择性”这个概念的核心。选择性 = 满足条件行数 / 总行数，这个值越小，索引越有价值。比如性别字段，只有“男”“女”两个取值，选择性极低；而订单号每个值几乎唯一，选择性极高。在低选择性的字段上建索引，最大问题不是没用，而是优化器经常会在“走索引”和“全表扫描”之间摇摆，评估结果往往是全表扫描代价更低。

举一个实际项目里的例子。一张订单表，总共 200 万行数据，我在这张表的 status 字段上建了索引，这个字段只有 4 个枚举值。某个业务查询是 `SELECT * FROM orders WHERE status='COMPLETED'`，而这个状态在表中占了 45% 的行。当优化器计算成本时，走索引扫描意味着将近 90 万次回表，每一次回表都是随机 I/O。反观全表扫描，顺序读 200 万行其实非常快。最终的表现就是，建了索引之后的查询时间不降反升，而 EXPLAIN 的结果也证实了优化器直接放弃了索引。

这里我要重点提醒一句：低选择性字段建普通 B-Tree 索引，价值非常有限，甚至有害。如果你真需要优化这类查询，PostgreSQL 提供了更合适的方案——部分索引（Partial Index）或者覆盖索引（Covering Index）。部分索引只索引满足特定条件的行，把索引体积大幅压缩；覆盖索引则让查询走索引就能拿到全部数据，跳过回表步骤。后面我会专门演示这两种做法。

2.2 回表开销被低估：随机 I/O 是隐形杀手

大多数人对“索引加速查询”的理解，停留在“先找索引，再找数据”的层面。但这两个步骤之间的成本差异，往往被严重低估。索引本身是按 B-Tree 结构组织的，查找索引条目是一个树形结构的搜索过程，无论如何可以接受。但通过索引条目中的行指针（TID）去堆表中读取实际行数据，是纯粹的随机访问。

想象你在一本字典里查一个字，索引就像目录，告诉你这个字在第 350 页。但当你翻到第 350 页时，操作系统大概率不是从磁盘顺序读这一页，而是先要把这一页从磁盘上按位置跳读过来。如果你要查 100 个字，分布在字典的各个角落，那你就得来回翻 100 次，每次都需要磁盘寻道和旋转延迟。这个类比就是回表的随机 I/O 开销。

更重要的是，当查询需要返回大量行时，回表带来的开销甚至会超过索引查找节约的代价。PostgreSQL 的优化器对这一点非常敏感，它会估算回表次数，把随机 I/O 的代价乘一个系数，然后跟全表扫描对比。如果你的索引命中大量行，但优化器又不得不选择走索引，那这个查询可能就会成为“性能黑洞”。

低选择性之外，还有另一种常见情况会导致大量回表——索引字段上做了函数操作或者隐式类型转换。比如你在 created_at 字段上建了索引，但查询条件是 `WHERE DATE(created_at) = '2024-01-01'`。这种写法会让索引无法被使用，因为索引里面存储的是原始 `created_at` 的值，而不是 `DATE(created_at)` 的结果。你可能会去建一个表达式索引来救场，但那就是另一个维度的问题了。

我推荐的做法是，在多列查询场景下，优先考虑“索引覆盖”。如果查询字段已经全部包含在索引里，PostgreSQL 可以只扫描索引而不回表，这种扫描方式称为 Index Only Scan。它绕开了随机 I/O，性能提升非常明显。但覆盖索引会显著增加存储成本和写入开销，绝对不是无脑能为所欲为的。

提示：在写查询时，尽量避免在索引字段上套函数或做运算。非要用表达式索引，也一定要确保查询写法与索引定义完全一致。

2.3 写放大与数据页分裂：索引维护带来的反向代价

索引不仅服务于查询，它在每次 INSERT、UPDATE、DELETE 操作中，也需要同步维护。如果一张表同时存在 5 个索引，那么每次写入一行数据，就要在 5 棵 B-Tree 中插入对应的条目。写入次数从 1 直接变成 6，这就是“写放大”。对于写入频繁的业务表，索引数量过多带来的负面影响是实打实的，直接反映在 TPS（每秒事务数）下降和响应时间变长上。

比写放大更隐蔽的是“数据页分裂”。B-Tree 的每个索引页能装载的条目数量是有限的，当新插入的数据页满时，为了维持树的平衡，就需要把页面一分为二。这个过程涉及为新页分配空间、调整指针、移动相关数据。对于按单调递增或递减顺序插入数据的场景（比如自增主键，或者时间序列字段），数据页分裂尤其容易发生。因为所有新数据都往同一个页面上挤，几乎每插入一批数据就要触发一次分裂。

有一次我处理过一个跑批入库的场景。某业务每天凌晨批量导入约 100 万条记录到一个已有 500 万行的表中。最初表上只有主键索引，导入大约耗时 5 分钟。后来为了方便查询，在另外两个业务字段上各加了一个索引。第二次跑相同批量的导入，耗时直接涨到 28 分钟。原因就是每条新记录的插入，都要往三个索引里各插入一条，而且由于这些字段的分布没有规律，频繁触发索引页分裂，大量 I/O 消耗在这个维护过程中。

对于这类场景，有几个行之有效的优化方向。一是如果批量导入可以在事务中完成，并且业务允许短暂锁表，可以考虑在导入期间临时删除非必要索引，导完数据后再重建。二是尽量保持索引的写入模式是“顺序的”，比如在低基数枚举值字段上不要频繁更新，尽量用追加式写入。三是对时间序列数据，考虑使用 BRIN 索引替代 B-Tree，BRIN 只记录块级统计信息，体积小维护成本极低，对大量追加写入的场景非常友好。

2.4 统计信息过期导致的错误优化决策

PostgreSQL 的优化器在做成本估算时，依赖的是表上的统计信息，这些信息存储在 `pg_statistic` 系统表中。一般来说，在表上执行 ANALYZE 命令或者 VACUUM 时，数据库会自动更新统计信息。但这些机制都不是实时同步的，在大批量数据更新、数据分布剧烈变化之后，统计信息可能严重滞后。

优化器的误判，很多就发生在这个阶段。举个例子，一张商品表里有一个上架时间字段 shelf_time，当天新上架的商品只有 300 条。优化器根据统计信息，判断查询 `WHERE shelf_time > now() - interval '1 hour'` 的结果非常少，于是选择了走这个字段的索引。但实际上，因为某次定时任务一次性批量修改了 20 万条数据的 shelf_time，这 20 万条记录都符合该条件，而统计信息并没有立刻更新，优化器依然以为只有几百行。结果就是，索引扫描全速运行，回表 20 万次，查询耗时从预期的毫秒级暴涨到了秒级。

要解决这个问题，并不只是在发现问题之后执行一次 ANALYZE 那么简单。你需要建立起对统计信息时效性的敏感度。在 PostgreSQL 的 autovacuum 默认配置下，每当表累计更新的行数超过一定的阈值（默认是表行数的 20%），后台的 autovacuum 进程会触发 ANALYZE。但如果你的业务会定期产生大批量更新，并且这类更新对于查询模式有决定性影响，那就应该在这些批任务结束后，手动执行一次 ANALYZE。

注意：ANALYZE 只是更新统计信息，不会重建索引。如果建索引以来都没执行过 VACUUM，索引膨胀问题依旧存在。

2.5 索引膨胀与空页面：被忽略的存储陷阱

PostgreSQL 的索引是基于堆存储模型实现的。当表数据通过 UPDATE 或者 DELETE 被修改时，旧版本的行不会被物理立即删除，而是被标记为可见性待清理的状态，由 VACUUM 后台进程后续清理。这个机制给索引带来的影响是：索引中的条目不会同步随数据删除而删除，而是在清理时统一处理。

如果 VACUUM 运行不及时，或者表频繁更新，索引中就会出现大量“死元组”占用的页面，而且部分页面可能出现严重空置。这种状态就是索引膨胀。膨胀的索引会让同一个索引扫描遍历远超必要的页面数量，内存缓存命中率降低，I/O 量上升。而且更麻烦的是，索引膨胀不像数据表膨胀那样直观，它需要用查询 `pgstatindex` 等渠道来检查。

实际工作中我见到过一个很典型的索引膨胀案例。一张日志表，按月分区，其中一个分区的某索引膨胀率达到 180%——索引实际占用的空间几乎是有效数据的一倍以上。当对这个分区做范围查询时，扫描的索引页面数量翻倍，慢查询日志里频繁出现高延迟记录。而执行一次 `REINDEX INDEX table_index` 之后，索引体积缩小了一半多，查询时间直接下降了约 40%。

定期维护索引，是避免膨胀问题的关键功课。根据表的写入频率不同，我通常建议每周或者每月执行一次 REINDEX，或者更简单地，在 VACUUM 全库时加上 VERBOSE 观察哪个索引膨胀明显，再针对性重建。在 PostgreSQL 12 之后，REINDEX CONCURRENTLY 提供了在线重建索引的能力，可以在不阻塞读写的情况下完成索引整理，为生产环境的维护提供了很大的便利。

3. 从执行计划到诊断命令：如何精准定位索引问题

很多人在数据库性能出问题时，第一反应是凭感觉改 SQL 或者加索引。但真正的排障流程，应该先从客观证据出发。PostgreSQL 提供了非常完整的诊断工具链，其中最重要、最基础的就是 EXPLAIN。学会看懂执行计划，是每一个跟数据库打交道的人的必修课。执行计划会明确告诉你：优化器选择走全表扫描还是索引扫描，估算行数是多少，实际访问的块数是多少，有没有回表，有没有排序，每一步成本多大。

3.1 用 EXPLAIN 拆解真实执行计划

在排查“索引加了反而变慢”的时候，第一件事就是跑 `EXPLAIN (ANALYZE, BUFFERS)`。ANALYZE 关键字会真实执行 SQL，而不是只给出估算计划；BUFFERS 则会统计每一步访问了多少数据块。这两项结合起来，就能拿到一手现场数据。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'COMPLETED' AND created_at >= '2024-01-01';

下面是一个典型的执行计划输出片段，我在注释里给了关键信息：

Seq Scan on orders (cost=0.00..183254.00 rows=905123 width=118) Filter: ((status = 'COMPLETED'::text) AND (created_at >= '2024-01-01')) Rows Removed by Filter: 1094877 Buffers: shared hit=18234

注意这里的关键信息是“Seq Scan”，也就是全表扫描。它告诉我们这个 SELECT 没有走你建的索引。为什么？因为优化器估算出符合条件的行数大约是 90 万行，超过了全表一半，它判定全表扫描的顺序读比索引随机读更划算。如果你建立了索引但没有看到预期效果，第一反应不是质疑优化器，而是用 EXPLAIN 确认数据分布导致了这样的判断——90 万行确实不是索引能救的典型场景。

如果执行计划里出现 `Index Scan` 或 `Index Only Scan`，就说明索引确实被用上了。这时要看的是 `Rows Removed by Filter` 和 `Buffers` 这两项。前者表示虽然走了索引，但优化器在获取后还要过滤掉多少行；后者则告诉你实际访问的数据块数量。如果 Buffers 数值巨大，比如几万甚至几十万，说明这个索引扫描实际付出的 I/O 代价非常高，即使执行计划看着“用了索引”，也是失败的索引使用。

一些查询工具如 pgAdmin、DataGrip 都支持可视化执行计划和图形化 EXPLAIN，但可视化只是在格式上友好，判断的核心仍然落在关键数字和节点类型上。我建议初学者至少能手写、能口头解释一条执行计划的每一个步骤大概做什么，这样碰到任何前端工具，都不会被信息淹没。

3.2 统计信息与膨胀率检查

如果 EXPLAIN 已经确认索引那条路有问题，下一步是升级排查——看看统计信息是否过期了。直接执行 ANALYZE table_name，然后重新 EXPLAIN，如果执行计划发生了明显变化，说明之前的不理想表现大概率就是统计信息滞后导致的。这里没有太多玄学，就是优化器“低估”或者“高估”了某些条件的选择性。

而对于索引膨胀的判断，需要用到 PostgreSQL 提供的扩展模块。先安装扩展：

CREATE EXTENSION IF NOT EXISTS pgstatindex;

然后查询索引的健康度：

SELECT * FROM pgstatindex('orders_status_idx');

输出的关键字段包括 `leaf_pages`（叶子页数）、`dead_pages`（死页数）、`free_space`（页内空闲空间）。如果 `dead_pages` 数量占比较高，或者 `free_space` 比例长期很大，那么膨胀问题就是明显的。合理的做法是执行 `REINDEX INDEX CONCURRENTLY orders_status_idx`，然后再对比查询延迟。

做膨胀判断时，不要只看绝对值，要结合表的写入和删除频率来看。比如每天有大量 UPDATE 业务，那么索引膨胀可能每天都在发生，定期 REINDEX 就是必要的运维任务；而一个只读报表表，索引膨胀的概率就低很多。

3.3 用 `auto_explain` 捕获慢查询现场

在实际生产环境中，你通常不是事后才能发现一条 SQL 很慢，而是通过慢查询日志或者监控面板收到的告警。PostgreSQL 的默认配置里并没有记录所有执行时间超过阈值的查询，但有一个内置插件 `auto_explain` 可以做到这一点。开启它之后，数据库会自动为超过指定时间阈值的 SQL 记录执行计划，这对定位偶发的“加了索引反而变慢”的问题非常有价值。

在 postgresql.conf 中配置：

shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '1s' auto_explain.log_analyze = on auto_explain.log_buffers = on auto_explain.log_nested_statements = on

配置完成后，所有执行时间超过 1 秒的 SQL 都会带着完整执行计划和缓存信息被写进日志。排查时不再需要守株待兔地跑 SQL，翻日志就能找到现场证据。这个配置本身也会给所有被捕获的查询增加 EXPLAIN ANALYZE 的额外开销，但在生产环境里，以极小的资源代价换全量的慢查询全景，是完全值得的。

我在工作中见到太多人，数据库出了问题就往日志里翻，却不知道日志里其实根本没有包含执行计划。开启 auto_explain 之后，相当于给数据库装了一台行车记录仪，后续的故障复盘才能基于事实。这个细节，我认为是每一个 PostgreSQL 维护者都该尽早掌握的。

提示：auto_explain 的日志可能会非常冗长，建议筛选 log_min_duration 的阈值，保持在你能接受的噪音水平即可。每秒执行数百条的轻量查询，就不要用 100ms 阈值去捕获，否则日志量会爆炸。

4. 对症下药：从索引设计到执行细节的完整避坑建议

讲了这么多“为什么会变慢”，重点还是要落在“怎么解决”。每一个问题都有对应的解决思路，从前期的索引设计理念，到中期的执行细节，再到后期的运维维护，是一整套方法论。我并不建议直接套用模板，因为每张表的数据特征、业务负载、查询模式都不一样，真正有价值的是你根据这些因素做取舍的能力。

4.1 设计期：评估字段选择性，该用部分索引就绝不犹豫

在建索引之前，花两分钟检查字段的基数（cardinality）。先跑一条估算语句：

SELECT COUNT(DISTINCT status) FROM orders;

如果结果很小，比如十几、几十的枚举值，普通 B-Tree 索引就很难发挥价值。真正兴奋起来的场景是高基数字段上的精确匹配或范围查询，比如用户 ID、订单号。

部分索引（Partial Index）是处理低选择性字段的利器。它允许你只对满足特定条件的行建立索引，从而大幅压缩索引体积，提升命中效率。比如对于订单表，业务里高频查询是“未完成状态的订单”，而这个状态在表里占比很小：

CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status IN ('PENDING', 'PROCESSING');

这样建出来的索引，只包含待处理状态的订单，查询 `WHERE status IN ('PENDING', 'PROCESSING') AND created_at >= ...` 时，索引体积小、扫描快，其他状态完全不会干扰。而“已完成订单”这种大多数情况，走全表扫描反而更快，就是合理的。这里没有一刀切的对错，关键是精准匹配业务查询模式。

在实际设计时，还可以考虑复合索引的字段顺序。PostgreSQL 在索引的等值条件和范围条件混合使用时，适合把等值条件的字段放前面，范围条件的放后面。比如 `(status, created_at)` 的复合索引，在 `WHERE status='PENDING' AND created_at BETWEEN ...` 时效率最高。如果全部是等值条件，则任意顺序差异不大；如果全部是范围条件，就要考虑哪个字段的区分度更高。

4.2 查询期：规避函数包装和隐式类型转换

索引设计得再完美，写 SQL 的时候一个函数就把索引废了。最常见的写法是 `WHERE DATE(created_at) = '2024-01-01'`——函数把索引字段包了一层，使索引变成无效。正确的姿势是使用范围查询：

SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

这样即使用户没有精确到小时，也可以用区间表达达到同样的效果。另一种情况是隐式类型转换。比如一个 varchar 字段存的是手机号，查询时 `WHERE phone = 13800138000`（整数），PostgreSQL 会尝试将字段类型转换为整数，这会绕过索引。正确的匹配应该是 `WHERE phone = '13800138000'`（字符串）。

还有一个容易被忽视的细节是 COLLATE 排序规则。如果数据库的默认排序规则与索引创建时的排序规则不一致，排序或比较操作可能无法使用索引。多语言环境、大小写不敏感场景下尤其容易出现类似问题。如果你不确定，用 `SHOW lc_collate` 检查，并且尽量在索引创建和查询时保持一致的上下文。

4.3 维护期：用 VACUUM 与 REINDEX 把性能控制在稳定区间

索引不是建完就一劳永逸的。PostgreSQL 的多版本并发控制（MVCC）机制，决定了频繁的 UPDATE 和 DELETE 会在表和索引中留下大量“废弃”版本，必须由 VACUUM 后台活动来回收。如果你发现一个索引的占用空间持续增长，执行查询的时间也随时间推移缓慢上升，那么大概率是在膨胀问题。

此时就应该执行索引重建操作。在 PostgreSQL 12 及之后版本，使用 `REINDEX CONCURRENTLY` 可以在不锁表的情况下完成：

REINDEX INDEX CONCURRENTLY orders_status_idx;

从运维实践角度看，REINDEX 的触发时机至少应该评估两个维度：一是更新频率，二是索引体积。对于每日更新量在万级别以上的表，我一般会将其纳入每周的定期维护任务。对于几乎只读的表，则定期检查即可。

此外，VACUUM 的调度也要重视。默认的 autovacuum 会对所有表进行自动维护，但如果你有超大表或者写入量巨大的表，可能需要为它们单独设置更积极的 autovacuum 参数，比如提高 autovacuum_vacuum_scale_factor 的敏感度，或者使用自定义阈值。这里给一个简单的配置示例，针对大表把阈值改成固定值：

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05); ALTER TABLE orders SET (autovacuum_vacuum_threshold = 50000);

这样设置后，当 orders 表中超过 5% 的行或者超过 5 万行发生变化时，autovacuum 就会更早地介入，索引膨胀被控制在一个相对稳定的区间内。

4.4 工具期：PostgreSQL 索引诊断辅助功能清单

工欲善其事，必先利其器。下面这张表是我在实际工作中高频使用的一些 PostgreSQL 索引诊断辅助功能，每一项都有明确的定位和适用场景，按需取用即可：

pg_stat_user_indexes 这个视图特别实用于识别冗余索引。如果一个索引长时间 `idx_scan` 都是零或者接近零，同时每次写入又需要维护它，那么这个索引大概率是不值得存在的。把它删掉，既减少写入成本，也节约存储空间。这类“僵尸索引”在真实系统里其实非常常见，定期用这个视角做一次索引审计，收益远远大于建索引本身。

5. 典型故障复盘：三个拿来即用的实战案例

写技术文章最怕全篇都是理论，没有实例。这一章我整理了三个自己经历过的、真实的“索引反而变慢”案例，每个案例的排查路径、最终结论和解决方案都不同。希望对正在排查类似问题的人，能提供一个比较完整的参考模板。

5.1 低选择性字段上的盲目索引

某项目的消息通知表 message，总量约 800 万条，核心查询场景是通过 user_id 查找某个用户的消息列表，附带一个 status 过滤。建索引时，同事非常自然地给 (user_id, status) 建了复合索引。起初一切正常，但后来业务扩张，status 中 ‘READ’ 占比变得极高（超过 80%），此时走索引扫描每次都要回表读取大量行，用户量大的时候查询从 30ms 变成 1s 多。

排查时 EXPLAIN 显示确实走了 Index Scan，但因为命中行数太多，绝大部分成本消耗在回表上。简单粗暴的解决办法不建索引显然不对，毕竟单独查未读消息时必须快。最终方案是调整索引为一对“部分索引”组合：

CREATE INDEX message_user_unread_idx ON message (user_id, created_at) WHERE status != 'READ'; CREATE INDEX message_user_all_idx ON message (user_id, created_at DESC);

第一条专门服务“未读消息”的高频且低结果集查询，第二条服务用户所有消息的场景，但没有 status 视角，因为那时 status 过滤已经没有意义了。改动后，查询性能恢复到了预期水平，索引体积反而比之前更小。这个案例最大的教训是：复合索引的结果集规模会随着数据分布动态变化，必须用业务数据特征检验，而不是用建表的瞬间拍脑袋决定。

5.2 函数操作与统计滞后：一场全表扫描引发的排查

另一个项目里的 user 表有 300 万行，其中有一个 last_login_at 字段，用于记录最后一次登录时间。开发同学建了一个普通索引，查询逻辑是 `WHERE DATE(last_login_at) = current_date`。结果很明显，索引根本派不上用场，查询每次都触发全表扫描。

更让人迷惑的是，执行计划里明明显示优化器选择了索引扫描，但查询却很慢。查了一下，原来代码里两个版本并存，有的模块用 `last_login_at >= now()::date`，有的模块用 `DATE(last_login_at)`，而统计信息又恰好促使优化器在部分情况下选择了索引。这个现场非常混乱，同时也说明一个事实：如果查得慢，先别急着怀疑数据库，把 SQL 写对写统一，效果经常立竿见影。

解决方案是两个动作：把 `DATE(last_login_at)` 统一为范围写法；然后为了支持“某天登录用户”的查询，建一个表达式索引：

CREATE INDEX user_last_login_date_idx ON user (DATE(last_login_at));

建完表达式索引之后，走索引的查询必须写 `WHERE DATE(last_login_at) = '2025-02-01'`，才匹配。这种写法在日常实践中也完全可以接受，但条件是表达式在查询中固定不变。这类案例提醒我们，索引和查询是一个契约，两边都得照着约定来。

5.3 数据量增长后统计信息顿化：10 倍性能差的元凶

一个电商系统的订单报表，每天凌晨跑一次汇总。最初订单表 100 万行时，SQL 一直跑在 100ms 级别。半年后数据涨到了 500 万行，报表却突然变成 15 秒级别。初步排查时直接看 EXPLAIN，发现优化器选择了错误的索引，明明条件是一个订单号上的唯一索引，优化器却偏走了另一个字段的索引。

仔细看了统计信息，发现该表已经有将近两个月没被 ANALYZE 过，而恰巧在这两个月里，订单量翻了倍。优化器引用的数据分布严重过时，导致它严重低估了订单号这个过滤条件的行数，于是放弃了唯一索引的高效率路径。跑完 ANALYZE 之后，执行计划立刻恢复正常，查询回到了百毫秒级。

这类情况在运维中很常见，尤其是手动维护的大表、刚迁移完的大表，或者有定时批处理重写其内容的表。处理手段并不复杂：批量或者关键任务后主动 ANALYZE，或者把 autovacuum 的相关参数调得激进一些。但它反映了一个更深层的原则——索引的性能表现不是静态的，它依赖于统计信息的准确性。你要把统计信息的健康度当成基础设施的一部分来维护。

6. 常见问题与排查技巧实录

结合上面的案例和技术要点，把常见的“索引反而变慢”情况整理成一份速查表，方便你在排查时对照。

从实际操作角度来看，有几个排查技巧值得专门分享。第一点，永远不要把 EXPLAIN 输出的 cost 数值当绝对真理，它只是估算；但如果经过 ANALYZE 后 cost 依然差距巨大，就说明真实数据与预测偏离得很厉害。第二点，在生产环境排查时，先用 `EXPLAIN (ANALYZE, BUFFERS)` 在事务中跑一次，注意加 `BEGIN; ... ROLLBACK;` 包裹，避免对线上数据造成修改。第三点，排查慢查询时，积累到一定的量的数据再下结论。单次执行计划的波动可能来自缓存、并发、资源争抢，多次测试取中位数才是稳定判断。这些方法听起来基础，却是真实的数据库性能工程师每天都在做的事情。

说到这里，我回想这些年在 PostgreSQL 上踩过的坑，最大的感受其实是：索引优化没有银弹，所有技巧都建立在“先看执行计划、再分析数据”的基础上。数据分布会变、业务模式会变、索引的价值也会变，所以定期做索引体检、关注统计信息健康度，比一次性建好一套“完美索引”更重要。这一点，也值得每个运维和开发同学在未来的项目中持续保持警觉。