Menu

PostgreSQL EXPLAIN ANALYZE:如何看懂执行计划

EXPLAIN 显示 PostgreSQL 为查询选择的执行计划,EXPLAIN ANALYZE 会真正运行查询,并补上每一步的实际行数和耗时。学会阅读计划树和常见的节点名称。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

EXPLAIN query 显示 PostgreSQL 为查询选择的计划:一棵由步骤(节点)组成的树,带有估计的代价和行数。EXPLAIN ANALYZE query 还会运行查询,并补上每个节点的实际耗时、行数和循环次数。从缩进最深的节点开始往外读:内层节点把它们的行交给上一层节点。

email 上没有索引,所以计划是 Seq Scan:PostgreSQL 读取每一行,只保留通过 Filter 的行。Rows Removed by Filter 显示了白做的工作量。每次运行的耗时都不同;计划的形状比具体数字更重要。

读懂一行计划

每个节点打印一行,格式如下:

Seq Scan on users  (cost=STARTUP..TOTAL rows=N width=W) (actual time=FIRST..LAST rows=N loops=L)
部分含义
cost=STARTUP..TOTAL返回第一行之前和返回所有行的估计工作量,单位是规划器单位(不是毫秒)
rows(第一个)节点预计返回的行数
width估计的平均行大小,单位是字节
actual time每次循环中返回第一行和最后一行所用的毫秒数
rows(实际)每次循环返回的行数
loops节点运行的次数;耗时和行数都要乘以它

父节点的代价包含其子节点。底部的 Planning Time 和 Execution Time 是整条语句的总计。

认识常见的计划节点

EXPLAIN (COSTS OFF) 会隐藏数字,这样更容易看清计划的形状:

从里往外读。两张表都被扫描,筛选后的客户进入 Hash。Hash Join 用每个订单去探查它,最上面的 GroupAggregate 统计结果。最常见的节点:

节点作用
Seq Scan读取整张表
Index Scan通过索引找到行,再从表中读取每一行
Index Only Scan直接从索引中得到结果,不访问表
Bitmap Index Scan + Bitmap Heap Scan先从索引中收集匹配行的位置,再按表中的顺序读取
Nested Loop对外层的每一行,在内层输入中查找匹配;外层较小时很合适
Hash Join + Hash用一个输入建哈希表,再用另一个输入去探查
Merge Join连接两个已按连接键排好序的输入
Sort, Incremental Sort为 ORDER BY、DISTINCT 或归并连接排序
HashAggregate, GroupAggregate用哈希表或在已排序的输入上做 GROUP BY
Limit取到 N 行后停止
Gather收集并行工作进程返回的行

找出错误的行数估计

EXPLAIN ANALYZE 中最有用的检查,是对比同一节点上估计的 rows 和实际的 rows。两者相差很大时,说明规划器是根据错误的数字选择了计划,原因通常是统计信息缺失或过时:

扫描实际返回 10 行,但估计值高得多,因为这张表还没有被分析过。在 EXPLAIN 之前加上 ANALYZE tasks;,估计值就会接近 10。autovacuum 会在后台分析表,但批量导入数据之后,最好自己运行一次 ANALYZE(见 VACUUM 和 autovacuum)。

分析数据修改语句而不保留修改

EXPLAIN ANALYZE 会执行语句,所以 UPDATE 或 DELETE 会真的修改行。把它放进事务里再回滚:

回滚之后,1000 行都还在。

用 BUFFERS 等选项查看 I/O

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;        -- pages read from cache (hit) and disk (read)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; -- also list changed planner settings
EXPLAIN (FORMAT JSON) SELECT ...;              -- machine-readable, for plan visualizers
EXPLAIN (ANALYZE, TIMING OFF) SELECT ...;      -- row counts without per-node timing overhead

BUFFERS 显示 I/O 情况:read 页面很多的节点访问了磁盘。PostgreSQL 17 新增了 SERIALIZE(把输出行转换成文本的代价)和 MEMORY(规划器使用的内存)。官方 EXPLAIN 指南讲解了更多计划。

用规划器设置测试计划

想看看 PostgreSQL 不用某种方法时会选择什么计划,可以在当前会话中关掉这种方法。只在实验中这样做,不要用在生产代码里:

只有 100 行时,规划器更喜欢 Seq Scan;设置 enable_seqscan = off 后,计划显示 Index Scan using items_pkey。比较两次 EXPLAIN ANALYZE 的耗时,看看索引是否有帮助。RESET enable_seqscan; 恢复默认设置。

常见问题

EXPLAIN 和 EXPLAIN ANALYZE 有什么区别?

EXPLAIN 只规划查询并显示估计值。EXPLAIN ANALYZE 还会执行查询,并补上每个节点的实际耗时、行数和循环次数,所以它花的时间和查询本身一样长。

PostgreSQL 执行计划中的 cost 是什么意思?

cost 是查询规划器的估计值,单位是任意的,按顺序读取一个页面的代价为 1(seq_page_cost)来换算。它适合用来比较同一个查询的不同计划,而不是表示多少毫秒。

EXPLAIN ANALYZE 会修改数据吗?

会,对于 INSERT、UPDATE、DELETE 和 MERGE,它会真正执行修改。把它放在 BEGIN 和 ROLLBACK 之间,就能测量而不保留结果。

为什么有索引却选择了 Seq Scan?

因为规划器估计这样更便宜:表很小,查询会返回很大比例的行,或者条件用不上索引。过时的统计信息也可能误导它,运行 ANALYZE 就能修复。

Coddy 编程语言插图

和 Coddy 一起学编程

开始