PostgreSQL 性能优化（二）：读懂 EXPLAIN ANALYZE

本文沿用第 1 篇的订单系统案例，把已经定位到的目标 SQL 转换成可以逐层核对的执行证据。

为什么要从最深层节点向上阅读

以按用户倒序查询订单为例，先取得包含实际执行信息和缓冲区使用量的计划：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  id,
  public_id,
  status,
  total_amount,
  created_at
FROM orders
WHERE user_id = 42000
ORDER BY created_at DESC, id DESC
LIMIT 50;

EXPLAIN 只展示规划器估算；加入 ANALYZE 后，PostgreSQL 会真正执行查询，并把实际行数、循环次数和时间写入计划。阅读时从缩进最深的扫描节点开始，再沿父节点向上检查连接、过滤、排序，最后才看 Limit 等输出节点。因为上层节点消费下层结果，顶层只返回 50 行，并不表示底层也只处理了 50 行。

底层扫描节点决定读了多少行和数据块，中间连接、过滤和排序节点决定如何加工，上层 Limit 或 Aggregate 只是最终输出。每一层都要结合 estimated rows、actual rows、loops 和 buffers 判断。

估算行数与实际行数

计划中的 rows 是规划器预计每次执行该节点返回的行数，actual rows 是实际平均行数。两者相差几个数量级时，后续的扫描方式、连接顺序和内存策略都可能建立在错误前提上。常见原因是批量写入后未执行 ANALYZE、热点值造成数据倾斜、多个条件存在相关性，或表达式让选择性难以估算。

先确认统计信息是否新鲜，再判断偏差是否稳定地出现在特定参数上。不要因为一次估算不准就立即改统计目标；更高的采样精度会增加 ANALYZE 的时间与统计信息空间，应只用于确有倾斜的重要列。

actual time 必须结合 loops

actual time=a..b rows=n loops=m 中，a 是该节点产生第一行前的启动时间，b 是运行到结束的时间，时间和行数按每次循环平均显示。估算节点总工作量时必须把单次数据与 loops 一起看。

Nested Loop 的内层节点可能每次只耗时很短，却被外层结果触发几十万次。此时真正的问题不是某一次调用慢，而是很小的成本被重复放大。反过来，父子节点的时间包含关系也意味着不能把每一行 actual time 直接相加；应比较相邻节点，寻找行数、循环或处理量突然扩大的位置。

BUFFERS 揭示真实读取量

BUFFERS 把耗时背后的数据访问量暴露出来：

• shared hit 表示目标块已在 PostgreSQL 共享缓冲区中。
• shared read 表示需要从存储层读取共享块。
• shared dirtied 表示执行期间把页面改成了脏页。
• temp read 与 temp written 表示排序、哈希等操作使用了临时文件。

命中缓存不等于查询高效。若一条查询每次命中几十万个无关块，它仍会消耗 CPU 和内存带宽，并挤占其他热点数据。比较方案时应在相近的数据规模与缓存条件下同时记录执行时间、处理行数和块读取量。

排序、过滤与回表

继续向上阅读时，重点检查工作量是否在返回结果前才被丢弃：

• Rows Removed by Filter 很大，说明节点扫描了大量数据后才完成过滤。
• Sort Method 出现 external merge，说明排序超出可用内存并写入磁盘。
• Bitmap Heap Scan 的 heap block 很多，说明筛选后仍访问了大量表页。
• Heap Fetches 很大，说明 Index Only Scan 仍频繁回表核对可见性。

这些信号只负责说明成本出现在哪里，不直接等价于某个修复方案。尤其不能仅凭出现 Seq Scan 或 Index Scan 就判断计划好坏；还要结合返回比例、读取块数、过滤行数与循环次数。

数据倾斜与扩展统计

如果单列热点值导致估算长期失真，可以提高该列的统计目标并重新采样：

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders (status);

若查询同时按 status 和 user_id 过滤，而这两列在业务数据中明显相关，可以让规划器收集函数依赖统计：

CREATE STATISTICS orders_status_user_dependencies (dependencies)
ON status, user_id
FROM orders;

ANALYZE orders;

扩展统计用于修正规划器把相关条件当作相互独立所造成的估算偏差。它不能替代缺失的访问路径，也不会自动改善所有连接估算；创建后应重新取得执行计划，确认估算行数确实更接近实际行数。

EXPLAIN ANALYZE 的安全边界

对 INSERT、UPDATE、DELETE 和会产生副作用的函数，EXPLAIN ANALYZE 会真正执行语句。确认所有数据库内变更都受事务控制时，可以用回滚避免持久化测试更新：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'cancelled',
    updated_at = now()
WHERE id = 123
  AND status = 'pending';

ROLLBACK;

事务回滚不是绝对安全的沙箱：序列取值不会恢复，触发器可能调用外部系统，函数也可能产生事务外副作用。写语句应优先在隔离环境或可控数据上验证。

对于执行时间极短、调用频率很高的查询，逐节点计时本身可能形成可见开销。可以比较 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)；关闭计时后仍能看到实际行数、循环次数和整体执行时间，但看不到各节点的实际时间。

参考资料

• Using EXPLAIN
• EXPLAIN
• Planner Statistics