PostgreSQL EXPLAIN 完全使用指南

一、概述

PostgreSQL 会为收到的每个查询生成一个执行计划，而选择正确的计划对于查询性能至关重要[reference:0]。EXPLAIN 命令用于显示 PostgreSQL 规划器为给定语句生成的执行计划[reference:1]。执行计划展示了语句引用的表将如何被扫描（顺序扫描、索引扫描等），以及如果涉及多表，将使用何种连接算法整合数据[reference:2]。

掌握 EXPLAIN 的输出解读是一门需要经验积累的技术，但对性能调优至关重要[reference:3]。

二、EXPLAIN 基本语法

2.1 语法格式

sql

EXPLAIN [ ( option [, ...] ) ] statement

-- 其中 option 可以是：
-- ANALYZE [ boolean ]
-- VERBOSE [ boolean ]
-- COSTS [ boolean ]
-- SETTINGS [ boolean ]
-- GENERIC_PLAN [ boolean ]
-- BUFFERS [ boolean ]
-- SERIALIZE [ { NONE | TEXT | BINARY } ]
-- WAL [ boolean ]
-- TIMING [ boolean ]
-- SUMMARY [ boolean ]
-- MEMORY [ boolean ]
-- FORMAT { TEXT | XML | JSON | YAML }

2.2 基本用法

最简单的形式是 EXPLAIN 后直接跟 SQL 语句：

sql

CREATE TABLE users(id INT, age INT);

EXPLAIN SELECT * FROM users WHERE age > 18;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on users  (cost=0.00..38.25 rows=753 width=8)
   Filter: (age > 18)
(2 rows)

此时 PostgreSQL 不会真正执行查询，而是基于统计信息生成估计的执行计划[reference:4]。

2.3 常用选项组合

选项组合	用途
`EXPLAIN`	仅查看估计计划，不执行查询
`EXPLAIN ANALYZE`	实际执行查询，显示真实耗时和行数
`EXPLAIN (ANALYZE, BUFFERS)`	在 ANALYZE 基础上增加 I/O 统计
`EXPLAIN (ANALYZE, BUFFERS, VERBOSE)`	最详细的分析输出
`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)`	机器可读的 JSON 格式

⚠️ 重要提示：使用 ANALYZE 选项时，语句会被实际执行。对于 INSERT、UPDATE、DELETE 等会修改数据的语句，建议在事务中执行并在分析后回滚：
sql
BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE id = 1;
ROLLBACK;

三、选项详解

3.1 选项概览

选项	说明	默认值
`ANALYZE`	实际执行语句，显示实际运行时间和行数	`FALSE`
`VERBOSE`	显示计划的附加信息（输出列、触发器名称等）	`FALSE`
`COSTS`	显示每个节点的估计启动成本、总成本、行数和行宽	`TRUE`
`SETTINGS`	显示影响查询规划且与默认值不同的配置参数	`FALSE`
`GENERIC_PLAN`	为带参数的语句生成通用计划（不能与 ANALYZE 同时使用）	`FALSE`
`BUFFERS`	显示缓冲区使用情况（命中、读取、脏块、写入）	`FALSE`
`TIMING`	显示实际启动时间和执行时间（需与 ANALYZE 配合）	`TRUE`
`SUMMARY`	在查询后显示摘要信息（规划时间、执行时间）。ANALYZE 时默认包含	`FALSE`（不含 ANALYZE 时）
`FORMAT`	输出格式：TEXT、XML、JSON、YAML	`TEXT`

[reference:5]

3.2 BUFFERS 选项详解

BUFFERS 是诊断 I/O 瓶颈的关键工具。输出格式为：

Buffers: shared hit=3 read=1 dirtied=0 written=0

三种块类型前缀：

shared：普通表和索引的数据块
local：临时表和临时索引的数据块
temp：排序、哈希、物化等操作使用的短期临时数据

四种操作后缀：

hit：块在 PostgreSQL 缓冲区缓存中找到（命中）
read：块不在缓存中，需从磁盘或操作系统缓存读取
dirtied：块被当前查询修改（变脏）
written：块被从缓存中淘汰并写入磁盘

[reference:6][reference:7]

💡 最佳实践：始终使用 EXPLAIN (ANALYZE, BUFFERS) 而非仅 EXPLAIN ANALYZE，以便看到查询执行的真实 I/O 工作量[reference:8]。

四、输出解读

4.1 计划树结构

EXPLAIN 的输出是一个计划节点的树形结构。树的底层节点是扫描节点，负责从表中返回原始行。如果查询需要连接、聚合、排序等操作，则会在扫描节点之上增加相应的操作节点[reference:9]。

示例：

sql

EXPLAIN SELECT * FROM tenk1;

输出：

Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

[reference:10]

4.2 成本字段解读

括号中的数字（从左到右）：

字段	含义
启动成本	输出阶段开始前花费的时间，如排序节点中的排序时间
总成本	计划节点执行完成（检索所有可用行）的总成本
行数	该计划节点输出的估计行数（并非处理或扫描的行数）
宽度	输出行的平均宽度（字节）

[reference:11]

成本以规划器的成本参数确定的任意单位度量，传统做法是以磁盘页面获取为单位，通常将 seq_page_cost 设为 1.0[reference:12]。

⚠️ 重要理解：
上层节点的成本包含其所有子节点的成本[reference:13]。
成本不包括将输出值转换为文本或传输到客户端的时间[reference:14]。
rows 值表示节点发出的行数，通常比扫描的行数少，因为 WHERE 条件进行了过滤[reference:15]。

4.3 启动成本 vs 总成本

对于大多数查询，总成本更重要。但在特定上下文中，规划器会选择最小启动成本：

EXISTS 子查询中的子查询（执行器获取一行后即停止）
带 LIMIT 子句的查询（规划器在端点成本间进行插值）

[reference:16]

4.4 ANALYZE 输出的额外信息

使用 ANALYZE 后，每个计划节点会增加实际执行统计：

Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244) 
                    (actual time=0.012..0.458 rows=10000 loops=1)

新增字段说明：

actual time：实际执行耗时（毫秒），格式为"启动时间..总时间"
rows：实际返回的行数
loops：该节点执行的循环次数（嵌套循环连接中常见）

EXPLAIN ANALYZE 还会在最后显示：

Planning Time：规划阶段耗时
Execution Time：执行阶段耗时

五、常见节点类型

5.1 扫描节点

Seq Scan（顺序扫描）

最简单的操作：PostgreSQL 打开表文件，逐行读取数据，返回给用户或上层节点[reference:17]。

Seq Scan on users  (cost=0.00..45.00 rows=1000 width=244)
                  (actual time=0.012..0.458 rows=1000 loops=1)
  Filter: (age > 18)
  Rows Removed by Filter: 500

当添加 WHERE 子句时，会显示 Filter 条件和 Rows Removed by Filter 信息[reference:18]。

Index Scan（索引扫描）

使用索引定位符合条件的行：

打开索引
在索引中定位符合条件的行位置
打开表
获取索引指向的行
检查行可见性后返回

[reference:19]

Index Scan using idx_users_email on users  (cost=0.15..8.17 rows=1 width=244)
                                          (actual time=0.007..0.007 rows=1 loops=1)
  Index Cond: (email = 'alice@example.com')

Index Only Scan（仅索引扫描）

当索引已包含查询所需的全部列时，无需回表访问堆，效率更高。

Index Only Scan using idx_users_email on users  (cost=0.15..8.17 rows=1 width=50)
  Heap Fetches: 0

Heap Fetches 表示需要回表获取不可见行的次数，理想情况下应为 0。

Bitmap Scan（位图扫描）

适用于多索引条件组合的场景，通过位图合并多个索引的结果：

Bitmap Index Scan：在索引上扫描，生成位图
Bitmap Heap Scan：根据位图读取表数据，并应用额外的过滤条件

Bitmap Heap Scan on users  (cost=10.25..100.50 rows=500 width=244)
  Recheck Cond: ((age > 18) AND (city = 'Beijing'))
  -> BitmapAnd
        -> Bitmap Index Scan on idx_users_age
        -> Bitmap Index Scan on idx_users_city

5.2 连接节点

Nested Loop（嵌套循环连接）

对于外层返回的每一行，在内层查找匹配行。适合小表驱动大表且有索引的场景。

Nested Loop  (cost=0.00..150.00 rows=1000 width=100)
  -> Seq Scan on small_table
  -> Index Scan on large_table

Hash Join（哈希连接）

从较小的输入（构建侧）构建哈希表
从较大的输入（探测侧）逐行探测哈希表进行匹配

Hash Join  (cost=45.00..200.00 rows=5000 width=100)
  Hash Cond: (t1.id = t2.foreign_id)
  -> Seq Scan on t1
  -> Hash
        -> Seq Scan on t2

Merge Join（归并连接）

要求两表已按连接键排序，适合大数据量且已排序的场景。

Merge Join  (cost=50.00..300.00 rows=10000 width=100)
  Merge Cond: (t1.id = t2.id)
  -> Index Scan using idx_t1_id on t1
  -> Sort
        -> Seq Scan on t2

5.3 其他常见节点

节点类型	功能
`Sort`	显式排序操作
`Group`	分组操作
`Aggregate`	聚合操作（如 COUNT、SUM）
`Limit`	限制结果行数
`Append`	合并多个子查询结果（常用于分区表）
`Materialize`	物化子查询结果，避免重复执行

六、实战案例分析

6.1 案例一：顺序扫描 vs 索引扫描

sql

-- 创建测试表
CREATE TABLE test (id serial PRIMARY KEY, value int);
INSERT INTO test (value) SELECT random()*1000000 FROM generate_series(1, 1000000);
ANALYZE test;

-- 执行计划分析
EXPLAIN SELECT * FROM test WHERE id = 500000;

输出：

Index Scan using test_pkey on test  (cost=0.29..8.30 rows=1 width=8)
  Index Cond: (id = 500000)

若查询条件不使用索引列：

sql

EXPLAIN SELECT * FROM test WHERE value = 500000;

输出：

Seq Scan on test  (cost=0.00..17900.00 rows=1 width=8)
  Filter: (value = 500000)

这表明缺少 value 列的索引，导致全表扫描。

6.2 案例二：使用 BUFFERS 诊断 I/O

sql

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

输出示例：

Seq Scan on orders  (cost=0.00..45800.00 rows=15000 width=150)
                    (actual time=0.025..245.123 rows=15234 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 984766
  Buffers: shared hit=320 read=4500
Planning Time: 0.123 ms
Execution Time: 250.456 ms

解读：

shared hit=320：320 个数据块在缓存中命中
shared read=4500：4500 个数据块需从磁盘读取
每个数据块默认 8KB，总 I/O 约 38MB

若 read 数量远大于 hit，说明缓存命中率低，应考虑增加 shared_buffers 或优化查询[reference:20]。

6.3 案例三：统计信息过时导致的错误计划

问题现象：某个查询明明应该使用索引，却选择了顺序扫描，导致执行时间从毫秒级退化到分钟级。

诊断：使用 EXPLAIN ANALYZE 观察估计行数与实际行数的差异：

Seq Scan on orders  (cost=0.00..45800.00 rows=100 width=150)   -- 估计 100 行
                    (actual time=0.025..245.123 rows=150000 width=150)  -- 实际 15 万行

若估计行数远小于实际行数，表明统计信息可能过时。

解决方案：执行 ANALYZE orders 更新统计信息。在一个真实案例中，对从未分析过的表执行 ANALYZE 后，查询时间从超过 10 分钟下降到 20 秒以内，CPU 使用率从 60%+ 降至 10% 以下[reference:21]。

⚠️ 自动维护的重要性：PostgreSQL 的 autovacuum 后台进程会自动执行 VACUUM 和 ANALYZE 维护任务[reference:22]。若统计信息过时，优化器可能选择低效的执行计划，导致查询性能严重下降[reference:23]。

6.4 案例四：索引扫描效率下降

问题：频繁更新和删除导致索引扫描性能下降超过一倍。

现象：执行计划未变化，但 Buffers 和 Heap Fetches 显著增加[reference:24]：

-- 大量更新/删除后
Parallel Index Only Scan using large_idx on t_large
  (actual time=0.070..85.082 rows=330000 loops=3)
  Heap Fetches: 980000        -- 需要大量回表
  Buffers: shared hit=25293 dirtied=2733

原因：PostgreSQL 索引不存储可见性信息，需要通过可见性映射（Visibility Map）判断元组可见性。频繁更新和删除会导致可见性映射中标记的页面减少，需要更多回表操作[reference:25]。

解决方案：

执行 VACUUM 更新可见性映射
必要时执行 VACUUM FULL 或使用 pg_repack 物理重组表
定期维护高频更新的大表

七、性能调优指南

7.1 常见性能问题信号

执行计划特征	可能的问题	排查方向
出现 `Seq Scan` 且表较大	缺少合适索引或统计信息过时	检查 `WHERE`/`JOIN` 条件是否有索引
估计行数与实际行数偏差大	统计信息过时	执行 `ANALYZE`
`Buffers` 中 `read` 数量很大	缓存命中率低	增加 `shared_buffers` 或优化查询
出现 `Sort` 节点且数据量大	缺少排序索引	为 `ORDER BY` 列创建索引
`work_mem` 不足导致磁盘溢出	排序/哈希操作内存不足	增加 `work_mem`
`Heap Fetches` 数量大	可见性映射问题或索引膨胀	执行 `VACUUM`

7.2 规划器配置参数

PostgreSQL 提供了一系列 enable_* 参数，允许控制规划器的策略选择。这些参数通常用于调试或测试目的，不建议在生产环境中随意禁用。

sql

-- 临时禁用顺序扫描（用于测试索引是否可用）
SET enable_seqscan = OFF;
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 恢复默认值
RESET enable_seqscan;

常用 enable_* 参数[reference:26]：

参数	说明	默认值
`enable_seqscan`	启用顺序扫描	`on`
`enable_indexscan`	启用索引扫描	`on`
`enable_indexonlyscan`	启用仅索引扫描	`on`
`enable_bitmapscan`	启用量位图扫描	`on`
`enable_hashjoin`	启用哈希连接	`on`
`enable_mergejoin`	启用归并连接	`on`
`enable_nestloop`	启用嵌套循环连接	`on`
`enable_material`	启动物化操作	`on`
`enable_sort`	启用排序节点	`on`

7.3 成本参数调优

成本参数影响规划器对各种操作的"代价"评估：

参数	说明	默认值
`seq_page_cost`	顺序扫描中一页的读取成本	1.0
`random_page_cost`	随机读取一页的成本	4.0
`cpu_tuple_cost`	处理一行的 CPU 成本	0.01
`cpu_index_tuple_cost`	索引扫描中处理一个索引行的 CPU 成本	0.005
`cpu_operator_cost`	执行一个操作符或函数的成本	0.0025

在 SSD 存储上，random_page_cost 可以适当调低（如 1.1），使优化器更倾向于使用索引扫描。

7.4 内存参数调优

参数	说明	建议值
`shared_buffers`	共享内存缓冲区大小	物理内存的 25%
`work_mem`	排序/哈希等操作的内存上限	根据并发量调整，4-64MB
`maintenance_work_mem`	维护操作（VACUUM、索引创建）的内存	适当增大，如 1GB
`effective_cache_size`	操作系统缓存大小的估计值	物理内存的 50%

7.5 慢查询诊断步骤

sql

-- 1. 启用查询统计扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 2. 查看高频慢查询
SELECT query, calls, total_time, mean_time, rows 
FROM pg_stat_statements 
ORDER BY mean_time DESC LIMIT 10;

-- 3. 分析具体慢查询的执行计划
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT ...;

-- 4. 检查统计信息新鲜度
SELECT schemaname, tablename, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_analyze IS NULL OR last_analyze < NOW() - INTERVAL '7 days';

八、输出格式

8.1 TEXT 格式（默认）

人类可读的文本格式，适合在 psql 命令行中直接查看。

8.2 JSON 格式

适合程序化解析：

sql

EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

8.3 XML / YAML 格式

同样适合程序化处理[reference:27]。

sql

EXPLAIN (FORMAT XML) SELECT * FROM users WHERE id = 1;
EXPLAIN (FORMAT YAML) SELECT * FROM users WHERE id = 1;

九、注意事项

9.1 ANALYZE 的副作用

使用 EXPLAIN ANALYZE 时，语句会被实际执行。对于 INSERT、UPDATE、DELETE、MERGE、CREATE TABLE AS 等修改数据的语句，请在事务中使用并回滚：

sql

BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE created_at < '2024-01-01';
ROLLBACK;

9.2 计划缓存

PostgreSQL 的通用计划缓存机制可能导致使用通用计划而非自定义计划，有时会造成性能问题[reference:28]。如遇此情况，可以：

使用 EXPLAIN (GENERIC_PLAN) 查看通用计划
调整 plan_cache_mode 参数
使用 DEALLOCATE 清除特定预备语句的计划缓存

9.3 并行查询

EXPLAIN 输出中可能包含并行查询节点：

Gather  (cost=1000.00..50000.00 rows=1000000 width=100)
  Workers Planned: 2
  Workers Launched: 2
  -> Parallel Seq Scan on large_table

Gather：协调者节点，负责汇总并行工作进程的结果
Workers Planned：规划器计划启动的工作进程数
Workers Launched：实际启动的工作进程数
Parallel Seq Scan：并行顺序扫描

十、实用技巧汇总

场景	推荐命令
快速查看查询计划	`EXPLAIN query`
分析真实执行时间和行数	`EXPLAIN ANALYZE query`
分析 I/O 瓶颈	`EXPLAIN (ANALYZE, BUFFERS) query`
查看详细过滤条件	`EXPLAIN (ANALYZE, VERBOSE) query`
程序化解析	`EXPLAIN (FORMAT JSON) query`
诊断统计信息问题	对比估计行数与实际行数
测试索引是否可用	`SET enable_seqscan = OFF; EXPLAIN query;`
检查缓存效率	关注 `Buffers` 中 `hit` 与 `read` 的比例
检查磁盘溢出	查看是否有 `temp written` 或 `Sort Method: external merge`
检查并行执行	查看是否有 `Gather` 节点和 `Workers Launched`

总结

EXPLAIN 是 PostgreSQL 性能调优的核心工具。熟练掌握它，可以帮助你：

理解规划器的决策：通过成本估算了解优化器选择某种执行路径的原因
诊断性能问题：通过实际执行统计定位瓶颈
验证优化效果：对比优化前后的执行计划

建议从简单的查询开始练习，逐步增加复杂度。在实际调优中，始终使用 EXPLAIN (ANALYZE, BUFFERS)，以获取真实的执行时间、行数和 I/O 统计。阅读执行计划是一门需要经验积累的技能，但掌握它将极大提升你排查数据库性能问题的能力。

PostgreSQL EXPLAIN 完全使用指南 ​

一、概述 ​

二、EXPLAIN 基本语法 ​

2.1 语法格式 ​

2.2 基本用法 ​

2.3 常用选项组合 ​

三、选项详解 ​

3.1 选项概览 ​

3.2 BUFFERS 选项详解 ​

四、输出解读 ​

4.1 计划树结构 ​

4.2 成本字段解读 ​

4.3 启动成本 vs 总成本 ​

4.4 ANALYZE 输出的额外信息 ​

五、常见节点类型 ​

5.1 扫描节点 ​

Seq Scan（顺序扫描） ​

Index Scan（索引扫描） ​

Index Only Scan（仅索引扫描） ​

Bitmap Scan（位图扫描） ​

5.2 连接节点 ​

Nested Loop（嵌套循环连接） ​

Hash Join（哈希连接） ​

Merge Join（归并连接） ​

5.3 其他常见节点 ​

六、实战案例分析 ​

6.1 案例一：顺序扫描 vs 索引扫描 ​

6.2 案例二：使用 BUFFERS 诊断 I/O ​

6.3 案例三：统计信息过时导致的错误计划 ​

6.4 案例四：索引扫描效率下降 ​

七、性能调优指南 ​

7.1 常见性能问题信号 ​

7.2 规划器配置参数 ​

7.3 成本参数调优 ​

7.4 内存参数调优 ​

7.5 慢查询诊断步骤 ​

八、输出格式 ​

8.1 TEXT 格式（默认） ​

8.2 JSON 格式 ​

8.3 XML / YAML 格式 ​

九、注意事项 ​

9.1 ANALYZE 的副作用 ​

9.2 计划缓存 ​

9.3 并行查询 ​

十、实用技巧汇总 ​

总结 ​