卷 V · 计划CH 20深度 20/24

读懂 EXPLAIN (ANALYZE)

规划器怎么想的，不用猜——EXPLAIN 把它的计划和估算摊开给你，EXPLAIN ANALYZE 更是真的跑一遍、把实际耗时也告诉你。学会读这份「思路说明书」，是 PostgreSQL 性能调优的第一项、也是最重要的一项硬功夫。

EXPLAINANALYZE计划树估算 vs 实际

EXPLAIN 和 EXPLAIN ANALYZE 的区别

  • EXPLAIN 查询：不执行查询，只让规划器给出它打算怎么执行、以及各步的估算成本和行数。快、安全（连 UPDATE 都不会真改）。
  • EXPLAIN ANALYZE 查询：真的执行查询，然后同时给出估算和实际的耗时、行数。信息更全，但注意——它真的会跑，对 UPDATE/DELETE 要包在事务里 ROLLBACK，否则会真改数据。

调优时常用的完整咒语是 EXPLAIN (ANALYZE, BUFFERS)——多给一个 BUFFERS，能看到读了多少缓存页、多少磁盘页。

怎么读一棵计划树

一份计划长这样：

Hash Join  (cost=27.10..1650.00 rows=850 width=40) (actual time=0.5..12.3 rows=812 loops=1)
  Hash Cond: (o.cust = c.id)
  ->  Seq Scan on orders o  (cost=0.00..1500.00 rows=100000 width=20)
  ->  Hash  (cost=18.00..18.00 rows=1000 width=24)
        ->  Seq Scan on customers c  (cost=0.00..18.00 rows=1000 width=24)

两条阅读法则：

  1. 从里往外、从下往上：缩进最深的节点最先执行。箭头 -> 表示「这个节点的输出，喂给它上一层的父节点」。上面这棵树，两个 Seq Scan 先跑，扫出来的行分别喂给 Hash 和 Hash Join，最顶上的 Hash Join 最后完成。
  2. 每个节点是一个物理算子：Seq Scan（顺扫）、Index Scan（走索引）、Hash Join / Nested Loop（连接）、Sort、Aggregate……它们就是第 7、19 章讲的那些执行手法。

每个节点后面括号里的数字，含义是：

字段含义
cost=启动..总计估算成本。「启动」= 吐出第一行前的开销，「总计」= 全部完成的开销
rows=N规划器估算这个节点会吐出多少行
width=N每行估算多少字节
actual time=启动..总计（ANALYZE 才有）实际耗时，单位毫秒
rows=N loops=M（ANALYZE 才有）实际每次吐出的行数、这个节点被执行了几次
◆ 最有用的一招：比对「估算 rows」和「实际 rows」

EXPLAIN ANALYZE 最值钱的信息，是让你把每个节点的估算行数（cost 里的 rows）和实际行数（actual 里的 rows）放一起看：

  • 两者接近：规划器对数据分布判断准确，它选的计划大概率是好的。
  • 两者相差悬殊（估 10 行、实际 10 万行）：规划器被误导了——它基于错误的行数估算，很可能选了个糟糕的计划（比如以为只有几行就用了嵌套循环，结果几万行，慢成灾难）。

估算严重失准，最常见的原因是统计信息过期——表刚灌了大量数据、autovacuum 还没来得及 ANALYZE。手动跑一句 ANALYZE 表名 更新统计，往往计划立刻就对了。这是排查慢查询的第一反射动作。

同一句 JOIN，随数据量换算法

第 7 章埋的伏笔，现在兑现。同一句两表 JOIN，规划器会根据表的大小，在 Nested Loop 和 Hash Join 之间切换：

▶ 动手 · Hash Join vs Nested Loop

下面这台真引擎对同一句 orders JOIN customers 计划成本。切换 orders 的大小，看规划器怎么翻脸换算法、EXPLAIN 树怎么变。

规律是：orders 小的时候用 Nested Loop——对那寥寥几行，每行去 customers 用主键索引点一下，最省；orders 大起来（10 万行）就改用 Hash Join——逐行去点索引要点 10 万次，太亏，不如把小表 customers 一次性建成内存哈希表，大表扫一遍去撞。你没改一个字的 SQL，只是数据变了，最优路径就换了——这正是声明式的价值：规划器随数据实时择优，而你什么都不用改。

看到这些，警惕

读计划时，几个常见的「性能红旗」：

  • 大表上的 Seq Scan：小表顺扫无所谓，但百万行大表还在顺扫某个本该走索引的条件，就要查为什么（没建索引？条件写法让索引失效？选择性太低顺扫反而对？）。
  • Nested Loop 套着一个大 rows 的节点：嵌套循环的内层被执行 loops 次，如果外层行数很大，内层又不便宜，总成本爆炸。常是行数估算失准害的。
  • estimate 和 actual 差几个数量级：见上，先 ANALYZE。
  • 巨大的 Sort 溢出到磁盘（Sort Method: external merge Disk）：内存排序放不下，落盘了，考虑加 work_mem 或用索引避免排序。
⇄ 对照 MySQL
  • 输出格式不同：MySQL 的 EXPLAIN 传统上是表格（一行一个访问的表，列出 type/key/rows/Extra），PostgreSQL 是缩进的树。PG 的树更直观地表达「谁喂给谁」，MySQL 8.0 也补了 EXPLAIN FORMAT=TREE 向这个方向靠。
  • EXPLAIN ANALYZE：PostgreSQL 很早就有；MySQL 直到 8.0.18 才引入 EXPLAIN ANALYZE（真跑并给实际耗时）。更早的 MySQL 只能看估算。
  • 「估算 vs 实际」的排查思路两家相通：MySQL 也会因统计过期而估错行数，对应动作是 ANALYZE TABLE。这个调优直觉可以直接搬过来。

这一章的一句话

EXPLAIN 摊开规划器的计划、ANALYZE 补上实际耗时；从里往外读树，重点比对每个节点的「估算 rows」和「实际 rows」——差得离谱多半是统计过期，先 ANALYZE。同一句 JOIN 会随数据量在 Nested Loop 和 Hash Join 间自动切换。

要让规划器选对路，最强的一张牌是索引。下一章，我们看 B-tree 怎么把一次全表顺扫，变成三层下钻的精准跳跃，以及 PostgreSQL 那些普通索引之外的特种索引。