PostgreSQL / 系统课时
掌握 EXPLAIN 与 ANALYZE
掌握 EXPLAIN 与 ANALYZE
读执行计划时,最关键的是区分“规划器估计了什么”和“语句实际做了什么”。本课用一个最小可运行样本读取 EXPLAIN 与 EXPLAIN ANALYZE 的核心字段,并明确实际执行查询尤其是写语句时的安全边界。
学习目标
- 区分计划成本、估算行数与实际执行指标
- 读懂
rows、loops、Buffers、Planning Time 和 Execution Time - 安全选择只看估算计划或采集实际计划
准备最小样本
以下临时表只在当前会话存在,适合反复执行计划实验。主键保证点查有明确的索引访问路径,避免示例结果被复杂数据分布干扰。
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;预期输出
CREATE TABLE
INSERT 0 10000
ANALYZE先读估算计划
不带 ANALYZE 的 EXPLAIN 只生成计划,不执行目标查询。它适合先确认访问路径和估算基数,尤其适合生产环境中的写语句。
EXPLAIN
SELECT *
FROM explain_lab
WHERE id = 4242;预期输出
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 进一步显示命中或读取了多少共享、本地或临时缓冲页。
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM explain_lab
WHERE id = 4242;预期输出
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 同样会真正执行。受控实验中可以用事务包住语句并回滚,验证执行计划而不保留普通表数据修改。
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;预期输出
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 证据对应到同一个节点上。