PostgreSQL / 系统课时

掌握 EXPLAIN 与 ANALYZE

掌握 EXPLAIN 与 ANALYZE

读执行计划时,最关键的是区分“规划器估计了什么”和“语句实际做了什么”。本课用一个最小可运行样本读取 EXPLAIN 与 EXPLAIN ANALYZE 的核心字段,并明确实际执行查询尤其是写语句时的安全边界。

学习目标

  • 区分计划成本、估算行数与实际执行指标
  • 读懂 rows、loops、Buffers、Planning Time 和 Execution Time
  • 安全选择只看估算计划或采集实际计划

准备最小样本

以下临时表只在当前会话存在,适合反复执行计划实验。主键保证点查有明确的索引访问路径,避免示例结果被复杂数据分布干扰。

sql
CREATE TEMP TABLE explain_lab (
    id integer PRIMARY KEY,
    bucket integer NOT NULL,
    payload text NOT NULL
);

INSERT INTO explain_lab (id, bucket, payload)
SELECT i, ((i - 1) % 100) + 1, repeat('x', 32)
FROM generate_series(1, 10000) AS g(i);

ANALYZE explain_lab;

预期输出

text
CREATE TABLE
INSERT 0 10000
ANALYZE

先读估算计划

不带 ANALYZE 的 EXPLAIN 只生成计划,不执行目标查询。它适合先确认访问路径和估算基数,尤其适合生产环境中的写语句。

sql
EXPLAIN
SELECT *
FROM explain_lab
WHERE id = 4242;

预期输出

text
Index Scan using explain_lab_pkey on explain_lab  (cost=<START>..<TOTAL> rows=1 width=<WIDTH>)
  Index Cond: (id = 4242)

cost=<START>..<TOTAL> 是规划器内部成本单位,不是毫秒;rows=1 是该节点每次执行预计输出的行数;width 是预计平均行宽。判断计划是否可信时,先看节点类型,再看估算行数是否符合数据分布。

用 ANALYZE 对照实际执行

加入 ANALYZE 后,PostgreSQL 会真的执行查询,并把实际耗时、实际行数和循环次数写回计划。BUFFERS 进一步显示命中或读取了多少共享、本地或临时缓冲页。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM explain_lab
WHERE id = 4242;

预期输出

text
Index Scan using explain_lab_pkey on explain_lab  (cost=<START>..<TOTAL> rows=1 width=<WIDTH>) (actual time=<TIME>..<TIME> rows=1 loops=1)
  Index Cond: (id = 4242)
  Buffers: <BUFFER_STATS>
Planning Time: <TIME> ms
Execution Time: <TIME> ms

<TIME>、<WIDTH> 和 <BUFFER_STATS> 会随版本、缓存状态与机器变化。分析时重点比较同一节点的估算 rows 与实际 rows;当 loops 大于 1 时,计划中显示的实际行数和节点时间是每次循环的平均值,判断总工作量时要把循环次数一起考虑。

字段 代表什么 常见误读
cost=a..b 启动成本与总成本估计 当成毫秒
rows 节点每次执行的估算或实际输出行数 忽略 loops
actual time 实际首行与完成时间 只看末级节点
Buffers 缓冲页命中、读取、写入等计数 直接等同于磁盘 I/O
Planning Time 生成计划的时间 混入 SQL 执行时间
Execution Time 执行器完成语句的总时间 当成客户端端到端延迟

定位错误基数时,不要只看最顶层的总行数。应从计划树靠近数据源的位置开始,找到估算行数第一次明显偏离实际行数的节点,再检查该节点使用的过滤条件、连接条件和统计信息。

写语句的安全边界

EXPLAIN ANALYZE 对 INSERT、UPDATE、DELETE 同样会真正执行。受控实验中可以用事务包住语句并回滚,验证执行计划而不保留普通表数据修改。

sql
BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE explain_lab
SET payload = 'changed'
WHERE id = 4242;

ROLLBACK;

SELECT payload = repeat('x', 32) AS rolled_back
FROM explain_lab
WHERE id = 4242;

预期输出

text
BEGIN
Update on explain_lab  (cost=<START>..<TOTAL> rows=0 width=0) (actual time=<TIME>..<TIME> rows=0 loops=1)
  ->  Index Scan using explain_lab_pkey on explain_lab  (cost=<START>..<TOTAL> rows=1 width=<WIDTH>) (actual time=<TIME>..<TIME> rows=1 loops=1)
        Index Cond: (id = 4242)
<BUFFER_AND_TIME_LINES>
ROLLBACK
 rolled_back
-------------
 t

这个模式只适合已知副作用可回滚的实验。序列取值不会因事务回滚而恢复,用户函数、触发器或扩展也可能产生数据库事务之外的外部副作用,因此不能把“BEGIN 加 ROLLBACK”当成生产环境的万能沙箱。

生产采集怎么选

生产环境应先用不执行语句的 EXPLAIN 判断计划形态。只有在确认目标是可接受实际执行的读查询,并且已经控制超时、并发和资源影响时,才考虑采集 EXPLAIN ANALYZE;对写语句通常只采估算计划。

当逐节点计时本身带来明显开销时,可以使用 TIMING OFF,仍保留实际行数、循环次数和总执行时间;需要归档或自动比较计划时,再使用 FORMAT JSON。真正有价值的计划证据不是“节点越多越详细”,而是能把估算值、实际值、循环次数和 I/O 证据对应到同一个节点上。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

掌握 EXPLAIN 与 ANALYZE

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通