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 statusCOMPLETED而这个状态在表中占了 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-TreeBRIN 只记录块级统计信息体积小维护成本极低对大量追加写入的场景非常友好。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 (cost0.00..183254.00 rows905123 width118) Filter: ((status COMPLETED::text) AND (created_at 2024-01-01)) Rows Removed by Filter: 1094877 Buffers: shared hit18234注意这里的关键信息是“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 statusPENDING 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 索引诊断辅助功能每一项都有明确的定位和适用场景按需取用即可工具/命令用途适用情况EXPLAIN (ANALYZE, BUFFERS)查看执行计划与实际执行的成本所有查询性能排查的起点pg_stat_user_indexes查看索引的扫描次数、命中率判断哪些索引闲置哪些被高频使用pg_stat_all_tables 的 seq_scan/idx_scan对比全表扫描与索引扫描的次数判断优化器是否频繁忽略索引pgstatindex 扩展查看索引叶子页、死页、膨胀程度索引膨胀的专项诊断auto_explain自动记录慢 SQL 对应的执行计划生产环境慢查询的长期监控pg_stat_statements聚合统计每条 SQL 的执行总时长和调用次数定位哪些 SQL 是性能热点REINDEX CONCURRENTLY在线重建索引索引膨胀或损坏时的修复操作pg_relation_size / pg_indexes_size查看表与索引的真实空间占用快速评估索引存储成本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 显示 Seq Scan但建了索引选择性低、优化器认为全表扫描更便宜检查数据分布、运行 ANALYZE 验证统计信息使用部分索引、覆盖索引或用 BRIN 替代 B-Tree索引扫描但整体很慢大量回表随机 I/O 代价高EXPLAIN ANALYZE 查看 Buffers计算命中和回表行数改成覆盖索引缩小查询范围清理膨胀写入性能骤降索引数量过多或频繁更新触发页分裂查看写入缓慢时间段检查索引数量删除低效索引评估 BRIN 索引批量导入时临时移除索引执行计划在不同时间表现不一致统计信息滞后手动 ANALYZE对比前后执行计划设置合适的 autovacuum 参数批任务后主动 ANALYZE索引体积异常膨胀MVCC 废弃数据多、VACUUM 不及时使用 pgstatindex 查看死页REINDEX CONCURRENTLY 重建索引调整 VACUUM 频率查询里加函数或类型转换导致不走索引索引字段被包裹EXPLAIN 看不走索引 检查 SQL 写法改用范围查询或建立表达式索引从实际操作角度来看有几个排查技巧值得专门分享。第一点永远不要把 EXPLAIN 输出的 cost 数值当绝对真理它只是估算但如果经过 ANALYZE 后 cost 依然差距巨大就说明真实数据与预测偏离得很厉害。第二点在生产环境排查时先用 EXPLAIN (ANALYZE, BUFFERS) 在事务中跑一次注意加 BEGIN; ... ROLLBACK; 包裹避免对线上数据造成修改。第三点排查慢查询时积累到一定的量的数据再下结论。单次执行计划的波动可能来自缓存、并发、资源争抢多次测试取中位数才是稳定判断。这些方法听起来基础却是真实的数据库性能工程师每天都在做的事情。说到这里我回想这些年在 PostgreSQL 上踩过的坑最大的感受其实是索引优化没有银弹所有技巧都建立在“先看执行计划、再分析数据”的基础上。数据分布会变、业务模式会变、索引的价值也会变所以定期做索引体检、关注统计信息健康度比一次性建好一套“完美索引”更重要。这一点也值得每个运维和开发同学在未来的项目中持续保持警觉。