PostgreSQL / 系统课时

使用 EXPLAIN ANALYZE

使用 EXPLAIN ANALYZE

EXPLAIN ANALYZE 会真实执行 SQL,并把实际耗时、实际行数和循环次数写入计划。本课将比较估算值与实测值,同时说明它用于修改语句时可能产生的数据影响。

学习目标

  • 使用 EXPLAIN ANALYZE 获取真实运行指标
  • 对比估算行数与实际行数
  • 安全分析可能修改数据的语句

执行并观察真实指标

下面创建临时表并分析一条聚合查询。BUFFERS 会补充缓存命中和读取信息,适合判断查询是否大量访问数据页。

sql
CREATE TEMP TABLE analyze_orders (
    id integer PRIMARY KEY,
    customer_id integer NOT NULL,
    amount numeric(10, 2) NOT NULL
);

INSERT INTO analyze_orders (id, customer_id, amount)
SELECT n, n % 200, (n % 1000 + 1)::numeric(10, 2)
FROM generate_series(1, 20000) AS n;

ANALYZE analyze_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(amount)
FROM analyze_orders
WHERE id BETWEEN 1000 AND 5000
GROUP BY customer_id;

预期结果

text
HashAggregate  (... actual time=<TIME> rows=200 loops=1)
  Group Key: customer_id
  ->  Index Scan using analyze_orders_pkey on analyze_orders
        (... actual time=<TIME> rows=4001 loops=1)
        Index Cond: ((id >= 1000) AND (id <= 5000))
Planning Time: <TIME> ms
Execution Time: <TIME> ms

<TIME> 会随机器性能和缓存状态变化。核心检查点是索引扫描实际返回约 4001 行,聚合后输出 200 行。

读懂 actual time、rows 和 loops

actual time=a..b 表示节点第一次产生结果和完成全部结果的平均时间;rows 是每次循环平均输出行数;loops 是节点执行次数。

节点的总处理量通常近似为 rows × loops。嵌套循环内层节点即使单次很快,循环次数过大也可能成为主要耗时来源。

比较估算与实际

计划中的 rows 是规划器估算,actual rows 是真实结果。两者相差几倍通常可以接受;若相差几十倍或更多,连接顺序和扫描方式就可能被误导。

常见原因包括统计信息过期、列值分布倾斜、多个条件之间存在相关性,以及表达式或函数让规划器难以估算选择率。

修改语句会真实生效

对 UPDATE、DELETE 或 INSERT 使用 EXPLAIN ANALYZE 时,语句会真正修改数据。需要分析而不保留修改时,应放在事务中并回滚。

sql
BEGIN;

EXPLAIN ANALYZE
UPDATE analyze_orders
SET amount = amount + 1
WHERE id <= 10;

ROLLBACK;

预期结果

text
Update on analyze_orders
  ->  Index Scan using analyze_orders_pkey on analyze_orders
        Index Cond: (id <= 10)
Rollback

计划会显示实际更新路径,而 ROLLBACK 会撤销这 10 行修改。输出中的时间和成本数字可能不同。

减少测量干扰

首次执行可能包含磁盘读取,后续执行可能命中缓存,因此单次耗时不一定代表稳定性能。对重要查询应多次测试,并结合 BUFFERS、数据规模和并发环境判断。

EXPLAIN ANALYZE 本身也会增加计时开销,所以它适合定位瓶颈,不应被当作生产接口的常规执行方式。

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

目录与阅读设置

阅读设置

17

记录一个想法

使用 EXPLAIN ANALYZE

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

登录哪都通