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 就能修复。