PostgreSQL EXPLAIN深入解析与应用实例小结

来源：互联网 2026-07-26 08:33:15

PostgreSQL 收到一个 SQL 查询之后，接下来会发生什么？优化器（planner）会登场，在众多可能的执行方案里，估算出一个“代价”最低的计划，这就是我们常说的查询计划。 PostgreSQL 会把优化器选中的计划展示给我们，想要看它长什么样子，用 EXPLAIN 命令就行。读懂这个计划是

PostgreSQL 收到一个 SQL 查询之后，接下来会发生什么？优化器（planner）会登场，在众多可能的执行方案里，估算出一个“代价”最低的计划，这就是我们常说的查询计划。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

PostgreSQL 会把优化器选中的计划展示给我们，想要看它长什么样子，用 EXPLAIN 命令就行。读懂这个计划是个技术活，需要经验的积累。这篇文章就来聊聊 EXPLAIN 的基础用法，以及那些常见的计划节点到底是什么意思。

文中示例基于 PostgreSQL 9.3 开发版的回归测试库，环境已经执行过 VACUUM ANALYZE。你在自己的环境里跑，代价和行数估计可能会有点出入，这很正常。

一、EXPLAIN 基础

查询计划的本质，是一棵“计划节点树”。

最底层的，通常是各种扫描节点：它们负责从表中把原始数据捞出来，比如顺序扫描、索引扫描、位图索引扫描等。
往上走，则是处理数据的节点：连接（join）、排序（sort）、聚集（aggregate）、过滤（filter）等等。

EXPLAIN 会给每个节点输出一行，告诉你节点类型，以及优化器对它的代价估算。最上面那一行，就是整个计划的总体代价估算，优化器的目标就是把这个值压到最低。

1. 最简单的例子

EXPLAIN SELECT * FROM tenk1;

输出是这样的：

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

优化器选了最直接的方式——顺序扫描（Seq Scan），把整个表过一遍。括号里那四个数字，含义分别是：

启动代价（start-up cost）：在吐出第一行结果之前需要付出的代价。比如排序节点，得先把排完序才能输出第一行。
总代价（total cost）：假设这个节点从头执行到尾（用完所有数据行）的总代价。
输出行数（rows）：预计这个节点会输出多少行。
行宽度（width）：预计每行的平均宽度，单位是字节。

代价是 PostgreSQL 内部的一个“虚构单位”，默认以“读取一个磁盘页面”为基准：seq_page_cost = 1.0，其他参数则相对它来设定。

2. 代价是怎么算出来的？

拿 tenk1 来说，先查一下元数据：

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

结果：

relpages = 358（表占 358 个磁盘页面）
reltuples = 10000（表里估计有 10000 行）

顺序扫描的代价估算公式大致是：

总代价 ≈ 页面读取代价 + 处理每一行的 CPU 代价

= relpages * seq_page_cost + reltuples * cpu_tuple_cost

用默认参数（seq_page_cost = 1.0, cpu_tuple_cost = 0.01）算一下：

358 * 1.0 + 10000 * 0.01 = 358 + 100 = 458

这不就跟 EXPLAIN 里的 cost=0.00..458.00 对上了么。

二、WHERE 条件与过滤

给查询加上 WHERE 条件之后，计划就开始有变化了。

1. 简单过滤：还是顺序扫描

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

输出：

Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 width=244)  Filter: (unique1 < 7000)

Filter: (unique1 < 7000) 的意思是：顺序扫描每一行，然后在读到内存之后，用这个条件进行筛选。
预计输出的行数从 10000 降到了 7001。
总代价略有上升（从 458 到 483），多出来的部分，就是执行过滤条件时消耗的 CPU 时间。

2. 更严格的条件：索引登场

当过滤条件收得更紧，优化器就可能考虑索引了：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

输出：

Bitmap Heap Scan on tenk1 (cost=5.07..229.20 rows=101 width=244)

Recheck Cond: (unique1 < 100)

-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)

Index Cond: (unique1 < 100)

这里用的是位图索引扫描 + 位图堆扫描两段式计划：

子节点 Bitmap Index Scan：在索引 tenk1_unique1 上找出所有满足 unique1 < 100 的行位置，生成一个位图。
父节点 Bitmap Heap Scan：拿着这个位图，去表里把对应的物理行取出来，同时再检查一下条件（Recheck Cond）。

随机访问表行听起来比顺序扫描贵，但因为只读了一小部分页面，所以总体代价还是比全表扫描低。

3. 多条件：索引条件和过滤条件的区别

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';

输出：

Bitmap Heap Scan on tenk1 (cost=5.04..229.43 rows=1 width=244)

Recheck Cond: (unique1 < 100)

Filter: (stringu1 = 'xxx'::name)

-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)

Index Cond: (unique1 < 100)

unique1 < 100 是索引条件（Index Cond），索引可以高效地完成这个过滤。
stringu1 = 'xxx' 不在索引里，只能作为过滤条件（Filter），从表里拿到行之后再检查。
预计输出行数降到了 1，但代价变化不大。因为不管过滤结果如何，都要先访问那 101 行数据。

三、索引扫描 vs 位图扫描

PostgreSQL 有好几种索引访问方式，常见的是这两个：

1. 索引扫描（Index Scan）

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

输出：

Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)

Index Cond: (unique1 = 42)

直接通过唯一索引定位到那一行数据，适合返回少量行的精确查询。
如果 ORDER BY unique1 和索引顺序一致，优化器还可能直接用索引扫描来省掉一次额外排序。

2. 位图扫描（Bitmap Scan）

位图扫描则更适合那些返回中等数量行的情况：

先走索引，命中符合条件的行，把对应的行号集合构建成一个位图。
然后按物理位置排序，批量访问表数据，尽可能减少随机 I/O。

选哪种，可以简单概括为：

返回行数很少 → Index Scan
返回行数中等 → Bitmap Heap Scan + Bitmap Index Scan
返回行数很多 → Seq Scan（全表扫描）

四、排序与 LIMIT

1. 显式排序

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

输出：

Sort (cost=1109.39..1134.39 rows=10000 width=244)

Sort Key: unique1

-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)

先全表扫描，再统一排序。
Sort 节点的代价大头来自内存排序的 CPU 开销。

2. 增量排序（Incremental Sort）

如果排序键的前缀已经有序，PostgreSQL 会考虑用增量排序来偷懒：

EXPLAIN SELECT * FROM tenk1 ORDER BY four, ten LIMIT 100;

输出：

Limit (cost=521.06..538.05 rows=100 width=244)

-> Incremental Sort (cost=521.06..2220.95 rows=10000 width=244)

Sort Key: four, ten

Presorted Key: four

-> Index Scan using index_tenk1_on_four on tenk1 (cost=0.29..1510.08 rows=10000 width=244)

Presorted Key: four 意味着 four 列已经通过索引得到了有序数据。
增量排序只需对 ten 列做“分段排序”，特别适合配合 LIMIT，因为可以提前输出部分结果。

3. LIMIT 带来的变化

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;

输出：

Limit (cost=0.29..14.48 rows=2 width=244)

-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..71.27 rows=10 width=244)

Index Cond: (unique2 > 9000)

Filter: (unique1 < 100)

没有 LIMIT 时，优化器可能会选位图扫描。
加上 LIMIT 2 之后，优化器改用了 Index Scan，因为只要找到 2 行就能停下来，没必要付出位图扫描的启动代价。

需要提一下的是：

索引扫描节点的 cost 和 rows 是按“全部执行完”来估算的。
实际运行时，Limit 节点会提前终止，所以真实耗时会远比估算的总代价要少。

五、连接（Join）计划

连接是复杂查询里的重头戏。PostgreSQL 支持三种主要连接算法：嵌套循环（Nested Loop）、哈希连接（Hash Join）、归并连接（Merge Join）。

1. 嵌套循环连接（Nested Loop）

EXPLAIN SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

输出：

Nested Loop (cost=4.65..118.62 rows=10 width=488)

-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)

Recheck Cond: (unique1 < 10)

-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)

Index Cond: (unique1 < 10)

-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244)

Index Cond: (unique2 = t1.unique2)

结构解析：

外层（outer）：tenk1 t1 的位图扫描，预计返回约 10 行。
内层（inner）：对 tenk2 t2 的索引扫描，每次查询时，拿外层行 t1.unique2 的值作为条件。
嵌套循环的总代价 ≈ 外层代价 + 外层行数 × 内层单次代价 + 连接处理的 CPU 开销。

这种算法在什么场景下比较吃香？

外层结果集很小。
内层可以通过索引快速命中。

2. 哈希连接（Hash Join）

当内层表变大之后，哈希连接往往更划算：

EXPLAIN SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

输出：

Hash Join (cost=230.47..713.98 rows=101 width=488)

Hash Cond: (t2.unique2 = t1.unique2)

-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)

-> Hash (cost=229.20..229.20 rows=101 width=244)

-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)

Recheck Cond: (unique1 < 100)

-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)

Index Cond: (unique1 < 100)

执行过程分两步：

构建阶段：扫描 tenk1 t1，以 unique2 为键构建哈希表。
探查阶段：扫描 tenk2 t2，对每一行，在哈希表里查找匹配的 unique2。

哈希连接适合的场景：

内层表能放进内存。
连接条件是等值连接（=）。

3. 归并连接（Merge Join）

归并连接要求两个输入都已经是按连接键排好序的：

EXPLAIN SELECT *FROM tenk1 t1, onek t2WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

输出：

Merge Join (cost=198.11..268.19 rows=10 width=488)

Merge Cond: (t1.unique2 = t2.unique2)

-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)

Filter: (unique1 < 100)

-> Sort (cost=197.83..200.33 rows=1000 width=244)

Sort Key: t2.unique2

-> Seq Scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)

tenk1 t1 通过索引扫描，天然就按 unique2 排好了序。
onek t2 先顺序扫描，再显式排序。
然后两个有序结果集按“归并”的方式合并。

它适合的场景？

连接键上已经有索引，或者结果本身就是有序的。
连接条件是等值连接。

六、EXPLAIN ANALYZE：看真实执行情况

EXPLAIN ANALYZE 会真刀真枪地执行查询，然后给出真实的执行时间和实际行数：

EXPLAIN ANALYZE SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

输出片段：

Nested Loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)

-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)

Recheck Cond: (unique1 < 10)

-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)

Index Cond: (unique1 < 10)

-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)

Index Cond: (unique2 = t1.unique2)

Planning time: 0.181 ms

Execution time: 0.501 ms

新增字段的含义：

actual time：实际执行时间（毫秒），分为启动时间和总时间。
rows：实际输出的行数。
loops：这个节点被执行的次数。

对于会被多次执行的节点（比如嵌套循环的内层）：

actual time 和 rows 是每次执行的平均值。
总时间 ≈ actual time × loops。

1. 额外统计信息

有些节点还会输出额外的细节，比如排序和哈希：

EXPLAIN ANALYZE SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;

排序节点输出：

Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)

Sort Key: t1.fivethous

Sort Method: quicksort Memory: 77kB

哈希节点输出：

Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 28kB

这些信息可以帮助判断：

排序是否在内存中完成，有没有溢出到磁盘。
哈希表是否太大，需不需要分批次（Batches > 1）。

2. 过滤与索引重检查

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

输出：

Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)

Filter: (ten < 7)

Rows Removed by Filter: 3000

Rows Removed by Filter：被过滤条件丢弃掉的行数。

对于 GiST 这类“有损”索引：

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

输出：

Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)

Index Cond: (f1 @> '((0.5,2))'::polygon)

Rows Removed by Index Recheck: 1

索引先返回候选行。
再通过“索引重检查”（Index Recheck）做一次精确判断，不满足的会被丢掉。

3. BUFFERS 选项

EXPLAIN (ANALYZE, BUFFERS) 能展示 I/O 相关的统计：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

输出里会包含：

Buffers: shared hit=15

shared hit：从共享缓冲区（内存）命中的页面数。
shared read：不得不去磁盘读取的页面数（缓存未命中）。

这个信息能直观地告诉你，查询的瓶颈到底是不是在磁盘 I/O 上。

七、数据修改语句的 EXPLAIN ANALYZE

EXPLAIN ANALYZE 对 INSERT / UPDATE / DELETE / MERGE 同样有效：

BEGIN;EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;ROLLBACK;

输出：

Update on tenk1  (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)
  ->  Bitmap Heap Scan on tenk1  (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)
        Recheck Cond: (unique1 < 100)
        Heap Blocks: exact=90
        ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)
              Index Cond: (unique1 < 100)

顶层是 Update 节点，负责真正写入数据。
子节点负责找出需要更新的行。
rows=0 是因为更新节点自己不输出任何行（只做修改）。

一个重要的提醒：

EXPLAIN ANALYZE 是会真的去修改数据的，所以通常要在事务里跑完就回滚。
触发器（尤其是 AFTER 触发器）的执行时间，可能不会完整地体现在计划节点的时间里。

八、使用注意事项与常见误区

EXPLAIN ANALYZE 会真的执行查询
- 对于 SELECT，结果虽然丢弃了，但副作用（比如函数调用）仍然会发生。
- 对于 INSERT / UPDATE / DELETE，会实实在在地改数据，务必小心使用（通常配合事务 + ROLLBACK）。
计时本身也有开销
- EXPLAIN ANALYZE 会引入额外的计时开销。
- 在某些系统上，gettimeofday() 调用比较慢，会导致执行时间看起来比实际长一些。
- 可以用 pg_test_timing 评估一下系统的计时开销。
估计值 vs 实际值
- 优化器给出的代价和行数，是基于统计信息的近似值。
- 当计划被 LIMIT 或连接算法提前终止时，实际行数和时间会远小于估计值，这本身不是“错误”，只是显示上的差异。
小表上的计划没有代表性
- 小表往往直接走顺序扫描，就算有索引也未必会用。
- 因为读取一个索引页面再加一个表页面，可能比直接顺序扫一个页面还要贵。
分区表与 Subplans Removed
- 扫描分区表时，某些分区在运行时可能被判定为不可能包含任何数据。
- 这些子计划会被省略掉，并且在计划中显示 Subplans Removed: N。