EXPLAIN 逐字段读法

准备中…重置数据集

EXPLAIN 的输出信息密度很高，但真正需要看的字段就那么几个。这一节给出一套固定的读法。

先分清 EXPLAIN 和 EXPLAIN ANALYZE

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

▶ 运行⌘/Ctrl + Enter

不带 ANALYZE 时，查询根本没有执行，你看到的全是优化器的估算。

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

▶ 运行⌘/Ctrl + Enter

带上 ANALYZE 之后多出 actual time 和 rows——查询真的跑了一遍。

对写语句要格外小心

EXPLAIN ANALYZE UPDATE ... 会真的执行那个 UPDATE。想看写语句的计划又不想改数据，包一层事务再回滚：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET amount = 0 WHERE id = 1;
ROLLBACK;

逐字段拆解

一个典型节点长这样：

Bitmap Heap Scan on orders  (cost=4.31..40.28 rows=10 width=25)
                            (actual time=0.021..0.043 rows=10.00 loops=1)
  Recheck Cond: (user_id = 42)
  Buffers: shared hit=12

字段	含义	怎么用
cost=4.31..40.28	启动代价..总代价	启动代价是「吐出第一行前的花费」。Sort 和 Hash 的启动代价高——它们必须处理完全部输入才能给出第一行
rows=10	优化器估算的输出行数	和 actual 比，差 10 倍以上就是问题所在
width=25	每行平均字节数	突然很大通常意味着选了不该选的宽列
actual time=0.021..0.043	实际的首行时间..总时间	单位毫秒，是单次循环的平均值
loops=1	这个节点被执行了多少次	⚠ 见下
Buffers: shared hit=12	从共享缓冲区读了 12 个页	hit 是命中缓存，read 是从 OS/磁盘读

loops 是最大的陷阱

actual time 和 rows 都是「每次循环」的平均值，要乘以 loops 才是总量。

SELECT u.id, o.id AS order_id FROM users u CROSS JOIN LATERAL (SELECT id FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) o WHERE u.id <= 200

▶ 分析执行计划

内层节点的 loops 是 200。如果它显示 actual time=0.5 rows=1 loops=200，真实开销是 0.5 × 200 = 100ms，不是 0.5ms。

Nested Loop 的慢查询几乎都栽在这里

看到一个 Nested Loop，永远先看内层的 loops。一个「看起来只要 0.05ms」的内层节点，循环十万次就是 5 秒。

本书的 PlanViewer 组件会把 loops > 1 时的总行数直接算好显示出来，就是为了避免这个误读。

三个最有价值的诊断信号

1. 估算与实际差距过大

SELECT * FROM orders WHERE status = 'paid' AND user_id < 100

▶ 分析执行计划

估算严重偏离实际，说明优化器手里的信息是错的。后果是它可能选错连接算法、选错扫描方式、给下游分配错误的 work_mem。根因通常是统计信息过期或多列相关——下一节专门讲。

2. Rows Removed by Filter

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE amount > 2000;

▶ 运行⌘/Ctrl + Enter

Rows Removed by Filter 告诉你白读了多少行。如果这个数远大于输出行数，说明过滤发生得太晚——数据已经从磁盘读上来了才被丢掉。解法通常是给过滤列建索引，把过滤下推到索引层。

对比一下走索引时的样子：

SELECT * FROM orders WHERE user_id = 42

▶ 分析执行计划

走索引时是 Index Cond 而不是 Filter——Index Cond 是在索引里就完成的筛选，Filter 是读出行之后才筛。一个计划里如果 Index Cond 后面还跟着 Filter，说明索引只用上了一部分条件。

3. Buffers

BUFFERS 是判断「这个查询到底碰了多少数据」最可靠的指标——比 actual time 稳定得多，因为它不受缓存冷热和机器负载影响。

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM order_items;

▶ 运行⌘/Ctrl + Enter

字段	含义
shared hit	在共享缓冲区里找到了
shared read	没找到，向操作系统要（可能命中 OS cache，也可能真的读盘）
shared dirtied	这次查询弄脏了多少页（SELECT 也可能弄脏——设置提示位）
temp read/written	溢出到磁盘的临时文件，出现就说明 work_mem 不够

看到 temp written 就该调 work_mem

Sort 或 Hash 节点下面出现 temp read/written，意味着它们放不下内存、退化成了外部排序或多批哈希。这是最容易修的性能问题之一：

SET work_mem = '64MB';   -- 会话级先验证效果

注意 work_mem 是每个执行节点一份，不是每个查询一份。一个有 5 个排序节点、并行度 4 的查询，峰值可能占用 20 份 work_mem。调这个参数要按「最大并发 × 每查询节点数」估算，而不是按连接数。

常见节点类型速查

扫描类

节点	什么时候出现
Seq Scan	全表扫描。大表上出现要问为什么
Index Scan	走索引并逐行回表，适合少量行
Index Only Scan	索引覆盖 + 页面全可见，不回表
Bitmap Heap/Index Scan	命中行数中等，先攒页面位图再按物理顺序读

连接类

节点	适用	危险信号
Nested Loop	外层行数很少	外层实际行数远超估算 → 灾难
Hash Join	一侧能装进内存	Batches > 1 说明溢出磁盘
Merge Join	两侧都已排序	需要额外 Sort 时未必划算

其它

节点	含义
Materialize	把子节点结果缓存起来重复使用
Memoize	PG 14+，缓存 Nested Loop 内层的重复查找结果
Gather / Gather Merge	并行查询的汇聚点
Incremental Sort	输入已按前缀有序，只排剩下的部分

一套固定的排查流程

面对一个慢查询，按这个顺序看：

1. 找 actual time 最大的那个节点（记得乘 loops）——先定位到「时间花在哪」，而不是从上往下读。
2. 看它的 rows 估算和实际差多少。差 10 倍以上，问题在统计信息，去看下一节。
3. 看有没有 Rows Removed by Filter 远大于输出。有的话，缺索引或索引没覆盖全部条件。
4. 看 Buffers 的绝对值。读了几十万个页却只返回几行，就是扫描方式选错了。
5. 看有没有 temp written。有的话先调 work_mem 再说别的。
6. 看 Nested Loop 的内层 loops。大循环次数配上没有索引的内层扫描，是最常见的两个数量级问题。

更好的输出格式

排查复杂计划时，加上 VERBOSE 能看到每个节点实际输出了哪些列；SETTINGS 会列出被改过的相关参数（排查「为什么开发环境和线上计划不一样」很有用）：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...;

FORMAT JSON 适合喂给工具。explain.depesz.com 和 pgMustard 能把文本计划渲染得更易读，后者还会直接给出优化建议。

本节验收

先自己回答，再点开对照。

cost 的两个数字分别是什么？什么样的节点启动代价特别高？

cost=4.31..40.28 是启动代价..总代价。启动代价是「吐出第一行之前的花费」，总代价是把全部结果算完的花费。

启动代价高的典型是 Sort 和 Hash——它们必须处理完全部输入才能给出第一行。相对的，Seq Scan、Index Scan 读到一行就能往上吐，启动代价接近 0。

常见错误一：以为两个数要相加。总代价是累计值，已经把启动代价包含在内了，40.28 就是全部花费。

常见错误二：以为加个 LIMIT 1 就能绕开一个昂贵的 Sort。绕不开——启动代价的定义就是「第一行之前」，排序在吐出第一行之前已经全部做完了。LIMIT 只能省掉总代价里超出启动代价的那部分，对启动代价高的节点几乎省不到钱。这正是「加了 LIMIT 还是慢」最常见的成因。

actual time=0.5 rows=1 loops=200 的这个节点，实际总耗时是多少？

100 毫秒，不是 0.5 毫秒。actual time 是单次循环的平均值，要乘以 loops。

常见错误：只记得乘时间，忘了 rows 也是单次平均值。这个节点的实际输出是 1 × 200 = 200 行，不是 1 行。漏掉这一步会连带毁掉下一个判断：拿单次的 rows=1 去和优化器的估算比对，会得出「估算很准」的错误结论——而估算准不准是排查流程的第二步，判错了就整条路走偏。

看到 Nested Loop，永远先看内层的 loops。一个「看起来只要 0.05ms」的内层节点，循环十万次就是 5 秒。本书的 PlanViewer 会在 loops > 1 时直接把总行数算好显示出来，就是为了掐掉这个误读。

Index Cond 和 Filter 有什么区别？一个节点同时出现两者说明什么？

Index Cond 是在索引里就完成的筛选，Filter 是把行读上来之后才筛。 两者同时出现，说明索引只吃下了一部分条件，剩下的条件必须先付出读取的代价才能判断。

Rows Removed by Filter 就是这部分代价的计量：它告诉你白读了多少行。这个数远大于输出行数，说明过滤发生得太晚。

常见错误：看到计划是 Index Scan 就认定「索引用上了，这里没问题」，转头去查别的地方。走索引和用满索引是两回事。 (a, b) 索引上跑 WHERE a = 1 AND c = 2：a 进 Index Cond，c 只能进 Filter。如果 a = 1 命中十万行而 c = 2 只剩十行，那就是十万行全白读——节点类型漂漂亮亮写着 Index Scan，实际代价接近扫十万行。判据是 Rows Removed by Filter，不是节点类型。

计划里出现 temp written 意味着什么？该调哪个参数？为什么调它要按节点数而不是连接数估算？

意味着 Sort 或 Hash 放不下内存，退化成了外部排序或多批哈希，中间结果溢出到了磁盘临时文件。调 work_mem，先在会话级 SET work_mem = '64MB' 验证效果。

要按节点数估算，是因为 work_mem 是每个执行节点一份，不是每个查询一份。一个有 5 个排序节点、并行度 4 的查询，峰值可能同时占用 20 份。

常见错误：用 max_connections × work_mem 算内存上限，算出来觉得安全就放心往大调。这个公式系统性地低估峰值——真正的乘数是「连接数 × 每查询节点数 × 并行度」。把 work_mem 从 4MB 调到 256MB 看着只是 64 倍，配上那个 20 份的查询，单个连接就能吃掉 5GB。OOM 事故里这个算错的公式出现频率极高。

写出你排查慢查询的前三步。

1. 找 actual time 最大的那个节点，记得乘 loops——先定位「时间花在哪」。
2. 看这个节点的 rows 估算和实际差多少。差 10 倍以上，根因在统计信息。
3. 看有没有 Rows Removed by Filter 远大于输出行数。有的话是缺索引，或者索引没覆盖全部条件。

常见错误一：从计划第一行开始往下逐行读。计划是一棵树，最外层节点的 actual time 天然是最大的——它包含了所有子节点。从上往下读只是在反复确认「总共很慢」这件已知的事。要找的是自身耗时占比最大的那个节点。

常见错误二：一上来就 SET enable_seqscan = off 试试看。那是排查流程的末端手段，而且它并不是「禁止全表扫描」——只是给 Seq Scan 加一个巨大的代价惩罚，没有别的选择时优化器照样用它，只不过所有代价估算都被扭曲了。正确顺序是先修统计信息，再调代价参数，最后才考虑改写查询。

（详见统计信息：优化器凭什么做决定）