EXPLAIN ANALYZE 深度解读

我接过一个工单:管理后台的"用户订单列表"打开要 12 秒。后端同学查了半天,说"加索引也加了,查询也简单,就是不知道为什么慢"。我让他把 EXPLAIN ANALYZE 的输出贴给我。一眼看过去——Nested Loop 外面套了一层,内层是个 Seq Scan on orders,循环了 8000 多次。

12 秒里,11.8 秒耗在了那个内层 Seq Scan 上。这不是"查询慢",这是"查询在用最笨的方式跑"。加一个复合索引,12 秒变 80 毫秒。

做了这么多年 PG 调优,我有个强烈印象:EXPLAIN ANALYZE 是数据库调优唯一的 X 光机。别的工具(慢查询日志、监控面板、pg_stat_statements)告诉你"哪条查询慢";只有 EXPLAIN ANALYZE 告诉你"它为什么慢、慢在哪一步"。不会读执行计划,等于医生不会看 CT——只能凭感觉开药。

这篇文章把 EXPLAIN ANALYZE 讲透:两种模式的区别、节点类型怎么认、四个关键指标怎么读、哪些信号是红灯、以及一套从慢查询到优化的实战闭环。

一、EXPLAIN vs EXPLAIN ANALYZE:预估与真相

很多人混用这两个命令,其实它们做的事完全不同。

EXPLAIN:只做计划,不执行。优化器基于统计信息估算出一个执行计划,给你看的 cost 和 rows 都是猜的。优点是零代价,即便是个会跑几小时的查询,EXPLAIN 也是秒回。缺点是——它可能猜错,而且错得离谱。

EXPLAIN ANALYZE:真正执行这条查询(注意:INSERT/UPDATE/DELETE 会真改数据,小心!),记录每一步的实际耗时和行数。它给你看的 actual time、actual rows、loops 是真实发生的。这才是调优真正的依据。

对比看一眼就懂:

-- 只 EXPLAIN(预估)
Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)

-- EXPLAIN ANALYZE(实际执行)
Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)
                    (actual time=1.2..6234.8 rows=2 loops=1)

第一行说"我猜这条查询会返回 1 行";第二行说"实际上它扫了 6.2 秒、返回了 2 行"。估算的 cost 是 48 万,看着挺贵;但真实 time 是 6234 毫秒,这才是你用户感受到的痛。

调优时永远以 actual time 为准。cost 只是优化器内部的一个相对单位(无量纲),它用来在多个计划之间挑选最优,不代表真实秒数。我见过有人盯着 cost=100 觉得"好便宜",结果实际跑出来 30 秒——因为统计信息过期,优化器整张图都画错了。

二、执行节点类型:从扫描到连接

执行计划是一棵树,从内到外、从下往上读。每个节点做一件事:扫描、连接、排序、聚合。看懂节点类型,就懂了查询在干嘛。

1. Seq Scan(顺序全表扫描)

把整张表从第一页读到最后一页。小表无所谓(PG 8KB 一页,几千行的小表扫一下就是微秒级);大表上看到 Seq Scan,基本就是索引没建对或没用上。这是最常见的慢查询根因。

2. Index Scan / Index Only Scan / Bitmap Index Scan(索引扫描)

三种走索引的方式,各有适用场景:

• Index Scan:走索引找到行指针,再回表读数据。适合返回少量行。
• Index Only Scan:索引里就包含了查询需要的所有列,不用回表,这是最优态。想达到它,得建一个"覆盖索引"把 SELECT 的列都包进去。
• Bitmap Index Scan + Bitmap Heap Scan:先把索引匹配到的行号收集成一个位图,再批量回表。适合返回中等数量的行(几千到几万行),比纯 Index Scan 高效,比 Seq Scan 节省。

3. Join 节点:Nested Loop / Hash Join / Merge Join

JOIN 的三种策略,优化器根据数据量和索引自动选:

• Nested Loop:对外层每一行,扫一遍内层。外层 100 行、内层 1000 行,就是 10 万次内层扫描。只有内层有索引(或内层很小)时才合理。否则就是 N+1 式灾难。
• Hash Join:把内表全量建一个哈希表,再扫外表探测。适合大表 join 大表、无索引、等值连接。这是 PG 处理分析型查询的主力。
• Merge Join:两边都按 join key 排好序,归并合并。要求输入有序(走索引或先 Sort),适合范围/等值连接。

4. Sort(排序)和 Aggregate(聚合)

ORDER BY 触发 Sort,GROUP BY / COUNT / SUM 触发 Aggregate。Sort 节点要重点看 Sort Method(见第四节信号灯);Aggregate 看是否走 Hash Aggregate(内存里哈希分组)还是 Group Aggregate(要求先排序)。

三、关键指标:cost、actual time、rows、loops

一行节点输出大概长这样:

Hash Join  (cost=1234.56..7890.12 rows=1000 width=64)
           (actual time=45.2..612.8 rows=982 loops=1)

四个指标,逐个讲。

1. cost — 估算成本,只是个参考

格式是 cost=启动成本..总成本。启动成本是"产生第一行需要的代价"(对 LIMIT、Exists 很重要);总成本是"全部跑完的代价"。关键:这只是估算,来自统计信息,可能错。不要拿 cost 当真实秒数。

2. actual time — 真实执行时间,以此为准

格式 actual time=启动毫秒..总毫秒,单位是毫秒。只有在 EXPLAIN ANALYZE 下才有。这是我读计划时盯得最紧的指标。找到 actual time 最大的那个节点,基本就定位了瓶颈。

3. rows — 估算 vs 实际,偏差大就是统计过期

cost 行里的 rows=1000 是估算行数,actual 行里的 rows=982 是实际行数。两者接近 = 统计信息准;差几个数量级 = 统计信息过期,优化器在瞎猜。偏差大时跑一次 ANALYZE 表名,往往能让计划彻底改观。

4. loops — 循环次数,看 actual 要乘上它

loops=N 表示这个节点被执行了 N 次。actual time 和 actual rows 都是单次的平均值——想知道总耗时,要用 time × loops。这个坑很多人踩:看到 actual time=0.5 觉得很快,其实 loops=200000,总耗时 100 秒,这才是真凶。

四、信号灯:四种典型的"病态"计划

读 EXPLAIN 输出,我主要在找下面这几个红灯。

信号 1:大表上的 Seq Scan(缺索引)

Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212)
                    (actual time=1.2..6234.8 rows=2 loops=1)
  Filter: (user_id = 12345)
  Rows Removed by Filter: 19999998

2000 万行的表,过滤掉 19999998 行只留 2 行——明显的缺索引。加一个 CREATE INDEX ON orders(user_id),Seq Scan 变成 Index Scan,6 秒变毫秒。

信号 2:rows 估算 1,实际 100000(统计过期)

Nested Loop  (cost=0.58..12.34 rows=1 width=100)
             (actual time=0.5..8234.1 rows=102456 loops=1)

优化器以为只返回 1 行,所以选了 Nested Loop(以为便宜);实际返回 10 万行,Inner 被扫了 10 万次。根因是统计信息过期,优化器基于错误数据选了错误策略。跑 ANALYZE 更新统计,下次执行计划会自动换成 Hash Join。

信号 3:Sort Method: external merge(排序溢出磁盘)

Sort  (cost=45678.00..48234.00 rows=100000 width=80)
      (actual time=234.5..1876.3 rows=100000 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 18432kB
  Buffers: shared hit=1234 read=5678, temp read=2345 written=2345

external merge + Disk = 排序内存不够,溢出到磁盘。磁盘排序比内存排序慢一两个数量级,看 temp read/written 就知道有多少临时数据在换页。解法是调大 work_mem(全局或当前会话 SET work_mem = '64MB'),让它变成 Sort Method: quicksort Memory。这一步常常单独省掉几百毫秒到几秒。

信号 4:Nested Loop + 内层 Seq Scan(N+1 式灾难)

Nested Loop  (actual time=0.3..11234.5 rows=8234 loops=1)
  -> Index Scan on users  (actual ... rows=8234 loops=1)
  -> Seq Scan on orders  (actual time=0.2..1.3 rows=1500 loops=8234)
        Filter: (user_id = users.id)

外层 8234 行,内层 Seq Scan 每次都全表扫一遍,总共扫了 8234 次。典型 N+1。给 orders.user_id 加索引,内层变成 Index Scan,或者干脆改写成 Hash Join,问题立即消失。这种计划一旦看到,基本就是"今天能准时下班"的信号。

五、实战流程:从慢查询到优化闭环

我处理慢查询有一套固定流程,从不靠"感觉调一调":

1. 拿到慢查询原文。从 pg_stat_statements 或慢查询日志来,别凭印象手敲——参数和真实场景可能差很远。
2. EXPLAIN (ANALYZE, BUFFERS) 跑一遍。注意:生产库慎用 ANALYZE 跑写语句,会真改数据。SELECT 没问题,UPDATE/DELETE 可以包在事务里跑完 ROLLBACK。
3. 从下往上读计划,定位 actual time 最大的节点。瓶颈通常集中在一两个节点上,不在整体。别看根节点的总时间,要看哪个子节点吃掉了大头。
4. 形成假设:缺索引?统计过期?work_mem 不够?JOIN 策略选错?
5. 对症下药:加索引 / ANALYZE / 调 work_mem / 改写查询。
6. 重测:EXPLAIN ANALYZE 再跑一遍,对比 actual time。没变好?换假设。变好了?记录下来,留作复盘。

每一步都"测量 → 假设 → 验证"。这是工程纪律,不是玄学。

六、BUFFERS:看见 I/O 的真相

EXPLAIN (ANALYZE, BUFFERS) 的第三个开关 BUFFERS,是被严重低估的利器。它告诉你每个节点读了多少缓存、落了多少磁盘:

Index Scan on orders  (actual time=0.5..12.3 rows=42 loops=1)
  Buffers: shared hit=8 read=4

• shared hit:在 shared_buffers 里命中,没花磁盘 I/O。
• shared read:没命中,从磁盘读的。
• temp read/written:work_mem 不够,溢出到临时文件(Sort/Hash 节点)。

命中率(hit / (hit+read))是关键。低于 90% 基本是缓存没覆盖到,要么数据量远超内存、要么 shared_buffers 偏小、要么查询扫的范围太大。一个查询慢,看 BUFFERS 往往立刻有方向——是 I/O 问题还是 CPU 问题,一目了然。

七、别只看 EXPLAIN 不 ANALYZE(以及坑)

最后强调几点血泪教训:

坑 1:只跑 EXPLAIN 不跑 ANALYZE。前面说过,纯 EXPLAIN 是估算,可能错。我见过优化器估算 rows=1 实际 rows=1000 万,基于这个错误估算选了 Nested Loop,线上慢成狗。调优必须看 actual,不看 estimated。

坑 2:统计信息过期导致全盘皆错。PG 的统计信息存在 pg_statistic 里,autovacuum 会定期更新,但大表、刚大批量导入数据的表、刚改了 schema 的表,统计常常滞后。调优前先 ANALYZE 一次,很多"玄学问题"会自动消失。

坑 3:EXPLAIN ANALYZE 会真的执行。对 SELECT 无副作用,但对 INSERT/UPDATE/DELETE 会真改数据。生产库调试写语句,务必包在 BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK; 里,改完即回滚。

坑 4:loops 高时忽略乘法。actual time 是单次平均,loops=10 万就是 10 万次叠加。读计划永远要算总账。

坑 5:成本参数没 context。cost 的大小只在同一个查询的不同计划间有意义,跨查询比 cost 没意义。优化器选 plans 用它,你调优不该用它。

小结

• EXPLAIN 是预估,EXPLAIN ANALYZE 才是真相。调优永远以 actual time、actual rows 为准,cost 只是优化器内部挑计划的相对值,可能严重偏离现实。
• 节点类型一眼识病:大表 Seq Scan 是缺索引;Index Only Scan 是最优态;Nested Loop 配内层 Seq Scan 是 N+1;Hash Join 是大表 join 的健康姿态;Sort Method: external merge 是 work_mem 不够。
• 四个指标要会读:cost(估算,仅供参考)、actual time(真实耗时,以此为准)、rows 估算 vs 实际(偏差大就 ANALYZE)、loops(别忘了把 time 乘进去)。
• 四个红灯信号:大表 Seq Scan、rows 估算与实际差几个数量级、排序溢出磁盘、Nested Loop 内嵌 Seq Scan——看到任何一个,基本就找到了瓶颈。
• BUFFERS 开关要看 I/O 真相:shared hit 高是健康,shared read 多是缓存没命中,temp 多是 work_mem 不够。命中率低于 90% 就有优化空间。

一句话收尾:EXPLAIN ANALYZE 不是看报告,是给查询做 CT——读懂每一个节点、每一行 actual time,你就比优化器更懂这条 SQL 在你机器上真正发生了什么。