深夜两点监控告警把我从梦里叫醒核心订单库CPU直接打满十几个慢查询堆在会话列表里全在对同一张订单表做扫描。当时的版本是 PostgreSQL 16数据量不过200万行平时跑得挺稳怎么突然就崩了后来定位到问题发现就是一条WHERE status pending的查询表里超过80%的订单都是这个状态偏偏我给这列建了一个普通B-Tree索引——建了索引反而成了累赘。这篇文章就是那次事故之后我陆陆续续处理的5个真实慢查询案例的复盘。它们都属于同一类问题索引看着建了执行计划却根本不走或者走了索引反而更慢。内容不绕弯子直接讲怎么定位、为什么失效、怎么改SQL和索引、改完什么效果。适合正在被慢查询折磨、又不想拿线上库瞎试的PostgreSQL使用者从入门到有一定经验都值得过一遍。1. 先搭好排查台子慢查询日志、EXPLAIN 与统计信息谈案例之前得先把“怎么发现慢查询”这件事说清楚。我见过太多人一上来就问“为什么我的查询慢”但连慢查询日志都没开全靠肉眼和猜。没有数据支撑的优化基本等于瞎蒙。1.1 慢查询日志怎么开才不踩雷PostgreSQL 的慢查询日志配置比较简单核心参数是log_min_duration_statement。把它设成一个阈值超过这个时间的SQL就会被记录到日志文件里# 编辑 postgresql.conf然后重启或 reload部分参数需要重启 log_min_duration_statement 1000 log_statement nonelog_min_duration_statement 1000表示只记录超过1秒的语句。注意这个参数可以reload不需要完全重启psql -U postgres -c ALTER SYSTEM SET log_min_duration_statement 1000; SELECT pg_reload_conf();但光有日志还不够。如果线上库有多个应用接入日志里会混进大量无关查询。我更建议顺手把pg_stat_statements扩展打开它能按SQL指纹聚合统计总执行时间、平均耗时、调用次数慢查询排行一目了然# 先加 shared_preload_libraries然后重启 PostgreSQL ALTER SYSTEM SET shared_preload_libraries pg_stat_statements;然后创建扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;之后你就可以用这条SQL直接拉出最耗时Top10SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;这是我的第一个建议先让数据库告诉你哪些查询慢而不是自己猜。噪音最多的时间段、某个页面点击后的接口全都对得上。1.2 EXPLAIN ANALYZE 才是真正的照妖镜慢查询日志定位到具体SQL后下一步就是分析执行计划。这里有一句我反复跟团队强调的话只看 EXPLAIN 不够必须 EXPLAIN ANALYZE。EXPLAIN 是预估EXPLAIN ANALYZE 会真的执行这条SQL把每一步的耗时、实际行数、循环次数都打出来。比如一个简单查询EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status pending;输出会显示出真实的执行时间节点以及每个节点扫描了多少行。重点看几个地方Seq Scan全表扫描行数一大通常就是慢查询的信号。actual time真实的启动时间和完成时间。rows与actual rows的差异如果计划预估1000行实际却扫了80万行说明统计信息失真或表达式写复杂导致优化器无法准确估算。BUFFERS选项也建议加上。它能看到缓存命中情况如果Buffers: shared hit很少大量shared read说明数据要靠磁盘IO物理读是慢查询的最大元凶之一。1.3 我习惯先检查这四项内容每次拿到一个慢查询我会按固定顺序把执行计划“读”一遍避免被其他噪声带偏。检查项说明慢查询里的典型异常扫描方式是顺序扫描还是索引扫描大表出现Seq Scan且过滤条件命中大量行预估行数对比预估rows和实际actual rows偏差超过一个数量级优化器选错路径Sort 节点排序是否在索引外发生表大但 ORDER BY 强制全量排序循环次数(loops)嵌套循环里被反复执行内表循环成千上万次如果一个查询全占基本不是单靠加索引能解决的可能需要改写SQL结构。下面5个案例各自踩中了其中一个或多个点我一个个拆开讲。2. 案例一选择性太差的索引加了不如不加2.1 事故现场80%的行都命中的“pending”状态回到最开始那次告警。订单表orders大约200万行业务上有一个状态字段status取值只有pending、paid、finished、cancelled几种。查询很常见就是后台管理页面拉取所有未完成的订单SELECT order_no, user_id, created_at FROM orders WHERE status pending ORDER BY created_at DESC;当时同事的直觉是status在 WHERE 条件里肯定要加索引。于是建了CREATE INDEX idx_orders_status ON orders(status);结果不生效。甚至执行计划显示出数据库压根不打算用这个索引EXPLAIN (ANALYZE) SELECT order_no, user_id, created_at FROM orders WHERE status pending ORDER BY created_at DESC; Seq Scan on orders (cost0.00..40480.10 rows1642340 width52) (actual time0.012..486.822 rows1642340 loops1) Filter: (status pending::text) Rows Removed by Filter: 357660 Planning time: 0.08 ms Execution time: 487.31 ms实际命中了164万行占总行数80%以上。对 PostgreSQL 来说这种比例下顺序扫描远比走索引快索引扫描要先一个个回表取堆数据每次都伴随随机IO而全表扫描是连续IO一气呵成。2.2 索引选择性的边界到底在哪“选择性”selectivity是判断要不要建普通B-Tree索引的第一指标。粗糙的定义是一个查询条件能过滤掉多少数据。选择性高比如按唯一ID查能过滤到只剩1行索引效果极好。选择性低比如按性别、状态查可能命中几十万行索引价值很低。经验阈值一般是这样预估返回行数占表总行数超过5%~10%普通列索引基本帮不上忙全表扫描反而更优。PostgreSQL 的优化器也会基于统计信息这么算所以在低选择率列上建普通索引常常会出现“优化器根本不理你”的现象。这个案例的最终方案是部分索引partial index。业务上绝大多数订单最终会变成finished真正一直停留在pending的是少数历史脏数据大约不到2万行。既然如此索引里只放pending就够了CREATE INDEX idx_orders_pending ON orders(created_at DESC) WHERE status pending;注意我把索引列从status换成了created_at DESC查询里正好要按created_at DESC排序这样不仅过滤条件能命中部分索引连 ORDER BY 都不需要额外排序。优化后的执行计划Index Scan Backward using idx_orders_pending on orders (cost0.29..1792.57 rows11338 width52) (actual time0.025..14.223 rows11338 loops1) Filter: (status pending::text) Execution time: 15.98 ms从487毫秒降到了15毫秒索引扫描目标行数只有1万多行。这个案例想说的核心是索引的价值取决于它能帮你躲开多少数据而不是“是否在WHERE里出现”。2.3 部分索引的适用条件和注意坑部分索引不是万金油它有个硬性要求查询的WHERE条件必须能和索引定义里的谓词匹配否则优化器无法使用它。比如上面索引只包含status pending如果业务写的是WHERE status IN (pending, paid)这个索引就不会被使用。还有一点容易踩坑如果pending本身是高频状态比如占了40%的数据那部分索引也没救。部分索引最适合的场景是“某个值的分布极度偏斜且业务经常只查这一个值”。我自己后来写索引脚本前都会先跑一句确认数据分布SELECT status, count(*) FROM orders GROUP BY status ORDER BY count(*) DESC;数据分布均匀OK偏斜严重就要考虑部分索引或干脆不建索引。这是我从那次告警里最值钱的教训之一。3. 案例二函数包住索引列索引变成了摆设3.1 明明有索引日期条件却触发全表扫描第二个案例来自报表部门的同事。他写了一个统计查询统计7天前当天新订单数SELECT count(*) FROM orders WHERE date(created_at) current_date - interval 7 days;created_at是带时间的 timestamp 字段表上也确实有idx_orders_created_at索引。但EXPLAIN一出来又是个全表扫描Aggregate (actual time632.781..632.782 rows1 loops1) - Seq Scan on orders (actual time0.012..610.033 rows112032 loops1) Filter: (date(created_at) (current_date - 7 days::interval)) Rows Removed by Filter: 1887968问题出在date(created_at)。B-Tree索引里存储的是列本身的原始值不是对列做了函数转换后的结果。优化器在匹配索引时需要找到“索引键 查询键”的对应关系。一旦你在索引列上套了函数比如date()、lower()、substr()PostgreSQL就没办法把这个函数结果和索引键做范围匹配于是干脆放弃索引。3.2 两个解决方案改SQL和表达式索引方案一是改写SQL把“套函数”变成“范围条件”。这是我最先推荐的因为它不动任何索引零额外存储成本SELECT count(*) FROM orders WHERE created_at date_trunc(day, current_date) - interval 7 days AND created_at date_trunc(day, current_date) - interval 6 days;逻辑完全一样查询某一天全天的订单。但这样created_at不会被函数包裹优化器可以直接用普通B-Tree索引做范围扫描。方案二是在确实无法改SQL的情况下用表达式索引CREATE INDEX idx_orders_created_date ON orders ( (date(created_at)) ); -- 查询也要保持同样写法 SELECT count(*) FROM orders WHERE date(created_at) current_date - interval 7 days;表达式索引的原理很简单普通索引存列原始值表达式索引存“表达式计算结果”。所以查询里也要写一模一样的表达式否则优化器还是匹配不上。3.3 表达式索引的隐蔽开销表达式索引不是免费的。它需要额外的磁盘空间而且每次INSERT或UPDATE数据库都要计算一次表达式结果并更新索引。如果表写入量大索引维护成本会被明显放大。我踩过一次坑给一张每天新增几十万行的日志表建了月份表达式索引结果为了维护它写入性能掉了接近20%最后删了索引改成定时任务预聚合表。所以使用表达式索引前先想清楚查询是否无法改写比如是第三方报表工具自动生成的SQL。写入频率是否允许额外索引维护开销。同一列是否反复被不同函数包裹若这样索引数量和代价都会成倍增加。函数导致的索引失效其实不只是date()。任何非sargable写法都值得警惕比如WHERE id 1 100、WHERE LOWER(email) abcx.com。排查套路都一样看优化器是否在索引列上做了函数转换。4. 案例三OR 条件和多单列索引的骗局4.1 两个单列索引OR 之后反而更慢第三个案例是订单查询页的通用筛选。业务方要求支持按“已取消订单”或“来自App渠道”筛选于是同事分别给两列建了独立索引CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_channel ON orders(channel);查询是SELECT * FROM orders WHERE status cancelled OR channel app ORDER BY created_at DESC LIMIT 50;乍一看两个条件各自有索引OR组合应该没问题。但执行计划显示全表扫描Seq Scan on orders (actual time0.012..912.467 rows787456 loops1) Filter: ((status cancelled::text) OR (channel app::text)) Rows Removed by Filter: 1212544问题根源在于表里channelapp的订单占了接近一半即使走了两个索引再合并命中总量还是太大。优化器算了一下BitHeap scan 加上随机IO的代价比顺序扫描还高直接选了最朴素的方案。4.2 为什么 BitmapOr 解决不了这类问题PostgreSQL 支持位图扫描。当多个单列索引被OR条件合并时优化器会用 BitmapOr 把两个索引的结果位图合并起来再去堆表取数据。听起来很美好但在这种命中占比很大的场景下位图合并后依然是海量行而且存在大量重复和“recheck”工作每个单列索引扫描一次构建位图。位图合并后还要逐个检查堆页面因为位图跳过了排序本质是随机IO。如果两个条件命中行数都非常大整体代价就会爆炸。我后面做了两个方向的调整。第一个方向是把应用侧的一个查询拆成两个查询再UNION ALL合并前提是两个分支返回行数都不大SELECT * FROM orders WHERE status cancelled UNION ALL SELECT * FROM orders WHERE channel app AND status cancelled ORDER BY created_at DESC LIMIT 50;这样每个分支都能用独立索引而且第二个分支加了status cancelled去重保证UNION不重复。但“两个分支结果集不大”这个前提在channelapp命中一半数据的情况下并不成立所以这个方案只能缓解不能根治。第二个方向才是真正的解法业务上下一次知道当筛选“App渠道的已取消订单”时本质上是在一个小集合里圈选。真正适合的是让组合条件更聚焦而不是把所有订单都捞出来。最终业务方改成了先按时间范围缩小数据窗口再加复合索引CREATE INDEX idx_orders_status_channel_created ON orders(status, channel, created_at DESC);如果改成WHERE status cancelled AND channel app这类AND组合复合索引按 (status, channel) 顺序可以快速定位。这也是一个很重要的常识OR 是集合加法AND 是集合减法索引对减法更友好。4.3 单列索引堆复合索引不等于优化这个案例暴露了一个普遍误解给每个 WHERE 里的列建独立索引查询就能自动加速。实际上AND 条件下多个单列索引有时还能靠 BitmapAnd 救一下但 OR 条件下多单列索引的表现往往取决于命中的比例和相关性。复合索引和单列索引是两码事。复合索引的意义不只是多存一列它定义了多个列的排序顺序可以直接支撑 AND 过滤、排序、覆盖查询等场景。所以我现在的习惯是先看查询的 WHERE 和 ORDER BY 涉及哪些列。评估哪些查询最频繁、过滤效果最强。为每个热门查询族设计一两个复合索引而不是给每列都上一把锁。5. 案例四排序分页慢LIMIT 没有救回全表排序5.1 分页查询其实在做全量排序第四类案例太典型了后台订单列表按用户ID查该用户最近的订单分页20条SELECT id, order_no, created_at, status FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 20;当时表上有单列索引idx_orders_user_id。按说 LIMIT 20 只取20行数据库应该像走捷径一样快速找到。但实际执行计划暴露了问题Sort (actual time318.442..318.508 rows20 loops1) Sort Key: created_at DESC Sort Method: top-N heapsort Memory: 26kB - Index Scan using idx_orders_user_id on orders (actual time0.017..286.055 rows98654 loops1) Index Cond: (user_id 12345)这个用户有近10万条订单。索引射中了这10万行然后数据库在内存里对这10万行做了top-N heapsort排完序再取20条。排序本身其实不慢26kB内存太小了起步时间0.017秒但整体286毫秒也不好看。如果这个用户订单量更大或者内存排序换成磁盘排序文件就完全不能看了。问题出在哪单列索引(user_id)只保证了同一个user_id的索引条目按内部TID相邻并没有按created_at排序。ORDER BY 需要一个“已经有序的数据源”索引如果帮不了只能额外建排序节点。5.2 复合索引直接提供排序顺序这个案例的解法是建一个能同时服务“过滤”和“排序”的复合索引CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);这个索引的本质是在user_id相等的前提下把created_at从大到小排好。查询执行时优化器可以沿着索引顺序直接读前20条排序节点直接消失Limit (actual time0.018..0.023 rows20 loops1) - Index Scan Backward using idx_orders_user_created on orders (actual time0.018..0.022 rows20 loops1) Index Cond: (user_id 12345) Execution time: 0.045 ms从286毫秒降到0.045毫秒这是今天所有案例里反差最大的一个。它的原理最容易被忽略B-Tree索引本来就是有序结构排序需求不一定要额外排序前提是让索引顺序和 ORDER BY 顺序一致。5.3 覆盖索引和 visibility map 的进阶影响既然已经建了idx_orders_user_created还能继续优化。查询 SELECT 里需要id、order_no、status但索引里只有user_id和created_atPostgreSQL 找到每条索引条目后还要再回堆表取其他字段这叫 heap fetch。如果业务对这个查询极其频繁可以考虑覆盖索引把返回列直接塞进索引叶子节点CREATE INDEX idx_orders_user_created_cover ON orders(user_id, created_at DESC) INCLUDE (order_no, status, id);之后执行计划会变成Index Only Scan不再回表。不过覆盖索引会显著增加索引体积不能无脑建必须针对热点查询。关于Index Only Scan还有一个隐藏条件并不是所有索引扫描都能变成 Index Only Scan。PostgreSQL 需要靠 visibility map 判断堆页面是否“所有人都可见”。如果表已经很久没被 VACUUMvisibility map 缺失或过期数据库必须回表校验行版本。我遇到过类似问题索引和查询都完美但执行计划显示Index Scan而非Index Only Scan差在大量 heap fetches。跑一次VACUUM之后计划就变成了 Index Only Scan。所以这里也带一句别把 autovacuum 调没或者定期人工 VACUUM 一下高频读大表。检查方式很简单SELECT relname, n_live_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE relname orders;6. 案例五JOIN 慢类型不一致让索引完全失效6.1 一个隐蔽的隐式类型转换第五个案例来自一次报表联表查询。订单表和用户表做 JOIN目的是查每个订单的收货人姓名SELECT o.order_no, u.username FROM orders o JOIN users u ON u.id o.user_id WHERE o.status paid;users.id是 bigint 主键orders.user_id却因为历史迁移原因留成了 int4。PostgreSQL 在比较两者时会把user_id隐式转换为 bigint这种转换本身没什么问题但坏在了索引匹配上如果转换发生在列一侧而不是参数一侧优化器就无法直接用users.id的主键索引去连接。执行计划里它选择把整个订单表做一次 HashJoin把用户表整个哈希化去匹配而不是走索引。EXPLAIN (ANALYZE) SELECT o.order_no, u.username FROM orders o JOIN users u ON u.id o.user_id WHERE o.status paid; Hash Join (actual time345.214..742.183 rows672340 loops1) Hash Cond: (o.user_id u.id) - Seq Scan on orders o (actual time0.014..230.203 rows672340 loops1) Filter: (status paid) - Hash (actual time312.552..312.555 rows500000 loops1) Buckets: 65536 Batches: 4 Memory Usage: 22193kBHash Join 也不算慢但要完全构建一个50万行的哈希表内存占用堆到2万多kB还因为装不下分了4个batch部分数据落盘。如果是热路径查询这种代价不可接受。根因就是数据类型不一致导致等式两侧没法进行索引连接条件的匹配。解决的硬核方案是统一数据类型ALTER TABLE orders ALTER COLUMN user_id TYPE bigint USING user_id::bigint;改了之后 JOIN 走 NestLoop Index Scan on users命中1000个用户的行直接通过主键索引取不再全量哈希。执行时间直接降了一个数量级。如果线上表不能立刻改类型也有临时的替代方案为转换后的表达式建索引。CREATE INDEX idx_orders_user_id_bigint ON orders ( (user_id::bigint) ); -- 查询里保持同样的表达式 SELECT o.order_no, u.username FROM orders o JOIN users u ON u.id o.user_id::bigint WHERE o.status paid;但这里有个非常微妙的坑显式转换时如果表达式写在o.user_id::bigint一侧PostgreSQL 匹配的是表达式索引如果写着写着又手滑变成u.id::int去和原始列比情况又会反过来。总之表达式索引的匹配规则极其“死板”不推荐作为长期方案只适合救急。6.2 统计信息过期怎样把优化器带歪还有一个和 JOIN 强相关的慢查询原因统计信息过期。现象是索引没失效JOIN 条件类型也一致但优化器选了一个极慢的 NestLoop 或者 HashJoin。我排查时遇到过一次报表凌晨批量跑订单表一夜之间新增了15万行但ANALYZE还没执行。优化器还在用旧统计信息以为某个JOIN关联的user_id只有几百个不同值于是选了 NestLoop结果内表循环了几十万次。执行计划里典型信号是预估行数和实际行数差太多。处理方法很直接ANALYZE orders;生产环境如果不方便手动ANALYZE可以调大自动分析的触发频率或者对关键大表调高STATISTICS采样粒度ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;STATISTICS值越大ANALYZE 采样越精细优化器的行数估算越准但 ANALYZE 也会变慢。通常 1000 已经够用不用盲目开到10000。6.3 JOIN 慢排查步骤小结JOIN 慢的排查顺序我建议是先看两边连接列的字段类型是否完全一致不一致优先统一。再确认优化器选择的是 NestLoop、HashJoin 还是 MergeJoin结合表大小判断是否合理。检查EXPLAIN ANALYZE中预估 rows 与实际 rows 偏差偏差大先ANALYZE。确认连接列上是否有索引索引是否被隐式类型转换或函数挡住。7. 几个真实项目里的索引检查清单到这里5个案例都讲完了。最后把我在实战中沉淀下来的一张检查清单写出来每次排查慢查询我都会按这个顺序过一遍覆盖了绝大多数情况。先用pg_stat_statements或慢查询日志锁定问题SQL。对SQL跑EXPLAIN (ANALYZE, BUFFERS)找出耗时节点。检查扫描类型和行数预估偏差偏差大先ANALYZE再看计划。看WHERE条件里有没有函数包裹索引列有就改SQL或建表达式索引。评估条件的选择性低选择性列优先考虑部分索引或直接放弃索引。涉及ORDER BY时检查索引顺序是否覆盖排序需求。涉及JOIN时确认连接列类型一致、索引可用。最后检查 VACUUM 和 visibility map特别是高频Index Only Scan的表。这些步骤看起来简单但我发现很多线上慢查询就是栽在其中一个环节。索引不是用来“证明我们优化过”的装饰品每加一个索引表在INSERT/UPDATE/DELETE时就要多维护一份数据。多一个索引就是多一份写放大和存储成本。我能给的最终经验就一句话在执行计划面前你自己的直觉真的不那么重要。让数据告诉你要建什么索引、要不要建索引然后针对每一条慢查询做取舍。这也是我做完这5个案例后最大的感受。