你确定读懂了PostgreSQL执行计划吗？
在执行任何 SQL 语句之前，PostgreSQL 优化器都会为它创建一个执行计划（Query Plan）。执行计划描述了 SQL 语句的具体实现步骤，例如使用全表扫描还是索引查找的方式获取表中的数据，连接查询使用 Nested Loop Join、Hash Join 还是 Sort Merge Join 算法，以及连接的顺序等等。当我们遇到慢查询等性能问题时，通常可以先查看 SQL 语句的执行计
在执行任何 SQL 语句之前，PostgreSQL 优化器都会为它创建一个执行计划（Query Plan）。执行计划描述了 SQL 语句的具体实现步骤，例如使用全表扫描还是索引查找的方式获取表中的数据，连接查询使用 Nested Loop Join、Hash Join 还是 Sort Merge Join 算法，以及连接的顺序等等。
当我们遇到慢查询等性能问题时，通常可以先查看 SQL 语句的执行计划，因此本文给大家详细介绍一下如何获取并解读 PostgreSQL 执行计划。
获取执行计划
PostgreSQL 提供了 EXPLAIN 语句，可以很方便地获取 SQL 语句的执行计划。EXPLAIN 语句的基本语法如下：
EXPLAIN statement;
我们首先创建初始化数据：
CREATE TABLE test(
id INTEGER PRIMARY KEY,
vc VARCHAR(100),
vn NUMERIC,
vd DATE,
other char(100) DEFAULT 'N/A' NOT NULL
);
INSERT INTO test (id, vc, vn, vd)
SELECT id, 's'||random(), 100*random(),'2024-01-01'::date+(100*random())::int
FROM GENERATE_SERIES(1, 10000) id;
ANALYZE test;
最后的 ANALYZE 命令是为了收集表的统计信息，帮助查询优化器做出合理的选择。
提示：PostgreSQL 优化器需要知道最新的数据库统计信息（pg_statistic）才能选择合适的执行计划，通常 autovacuum 后台守护进程会定期更新统计信息。但是，如果某个表近期执行了大量数据更新，我们可以执行 ANALYZE 命令更新它的统计信息。
以下是一个简单的 EXPLAIN 示例：
EXPLAIN SELECT * FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|
PostgreSQL 执行计划结果包含几部分内容：操作（Seq Scan on test）、成本（cost）、预估返回的行数（rows）以及预估每行数据的平均宽度（width），单位为字节。
其中，最重要的信息是成本，它的单位一般是磁盘页读取次数。成本包含两个数字，分别代表返回第一行数据之前的启动成本和返回全部结果的总成本。对于大多数查询而言，我们需要关注总成本；但是某些情况下（例如 EXISTS 子查询），查询计划器会选择最小的启动成本，因为执行器只需要获取一行数据。另外，如果我们使用了 LIMIT 子句限制返回的行数，查询计划器会基于两个成本计算一个合适的中间值。
EXPLAIN 语句还支持一些选项，其中需要重点注意的一个选项就是 ANALYZE，因为它不仅显示预估的执行计划，还会实际执行相应的语句并且返回执行时间统计。例如：
EXPLAIN ANALYZE
SELECT * FROM test;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141) (actual time=0.021..1.374 rows=10000 loops=1)|
Planning Time: 0.083 ms |
Execution Time: 1.890 ms |
可以看出，执行计划结果中增加了实际运行时间（actual time）统计，包括每个操作节点消耗的时间（毫秒）、返回的数据行数以及执行的次数。Planning Time 是生成执行计划的时间；Execution Time 是执行语句的实际时间，不包括 Planning Time。ANALYZE 选项通常可以用于检查查询计划器的评估是否准确。
虽然 ANALYZE 选项忽略了 SELECT 语句返回的结果，但是对于 INSERT、UPDATE、DELETE 等语句，它仍然会修改表中的数据，为了避免这种副作用，我们可以在事务内部获取执行计划，然后回滚事务：
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
其他 EXPLAIN 选项的介绍可以参考下文。
解读执行计划
PostgreSQL 执行计划的结构是一棵由计划节点组成的树，EXPLAIN 命令的每一行对应一个节点。
每一行节点除了汇总信息之外，还可能包含缩进行，显示了完成该节点的底层操作。节点的执行顺序按照缩进来判断，缩进越多的越先执行，同样缩进的从上至下执行。第一行显示了预估的总成本，它也是优化器最小化的目标。
执行计划最底层的节点是扫描节点，通常用于从表中返回原始数据。我们就从简单的单表访问开始。
单表访问
对于不同的表访问方法，存在以下不同的扫描节点：
- 顺序扫描（适用于返回大部分数据行）
- 索引扫描（适用于返回很少数据行）
- 位图索引扫描（适用于返回较多数据行）
顺序扫描就是全表扫描，它会依次读取整个表中的数据。如果查询条件字段没有索引，一般需要执行顺序扫描，例如：
EXPLAIN
SELECT *
FROM test
WHERE vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------+
Seq Scan on test (cost=0.00..348.00 rows=59 width=141)|
Filter: (vd = '2024-01-01'::date) |
顺序扫描对应的操作名称为 Seq Scan，通常意味着我们需要基于查询条件字段创建索引，从而通过索引优化查询。
索引扫描意味着遍历索引的 B-树叶子节点，找到所有满足条件的索引项，然后通过索引指针读取表中的数据。例如：
EXPLAIN
SELECT *
FROM test
WHERE id = 1000;
QUERY PLAN |
----------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (id = 1000) |
如果我们需要查询的字段都可以通过索引获取，PostgreSQL 可以使用仅索引扫描（Index-Only Scan）技术优化查询。例如：
CREATE INDEX idx_test_vn ON test(vn,id);
EXPLAIN
SELECT vn, id
FROM test
WHERE vn = 1000;
QUERY PLAN |
----------------------------------------------------------------------------+
Index Only Scan using idx_test_vn on test (cost=0.29..4.30 rows=1 width=16)|
Index Cond: (vn = '1000'::numeric) |
索引 idx_test_vn 包含了 vn 字段和 id 字段，查询语句不需要访问表中的数据即可返回查询结果。
提示：PostgreSQL 提供了覆盖索引（Covering Index），可以进一步实现 Index-Only Scan 优化。另外，Index-Only Scan 优化需要满足一个条件：MVCC 可见性，因为索引中并没有存储数据的可见性信息，只有表的元组中存储了该信息。
索引扫描每次找到一个满足条件的索引项时，都会基于元组指针再次访问表中的数据（回表），这是一种随机 IO。如果索引扫描只返回很少的数据行，它是一个很好的访问方法。但是如果扫描索引返回的数据行比较多，大量的随机回表会导致性能下降；一个优化的方法就是把回表的随机 IO 变成顺序 IO，为此 PostgreSQL 引入了位图索引扫描。
位图索引扫描（Bitmap Index Scan）的原理是一次扫描索引获取所有满足条件的元组指针，然后在内存中基于“位图”数据结构进行排序，最后按照元组指针的物理顺序访问表（Bitmap Heap Scan）中的数据。例如：
CREATE INDEX idx_test_vd ON test(vd);
EXPLAIN
SELECT *
FROM test
WHERE vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------------------------+
Bitmap Heap Scan on test (cost=4.75..139.99 rows=60 width=141) |
Recheck Cond: (vd = '2024-01-01'::date) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..4.74 rows=60 width=0)|
Index Cond: (vd = '2024-01-01'::date) |
该查询语句返回 60 行数据，使用索引扫描的话，还需要 60 次回表。因此，PostgreSQL 选择了位图索引的访问方法。
Recheck Cond 发生在回表阶段，因为如果基于元组构建位图导致位图过大，就会基于数据页（Page）构建位图（有损方式），也就是只记录了哪些数据页包含了所需的数据行，所以在读取数据页之后需要再次检查具体的元组。对于无损方式构建的位图，也会出现 Recheck Cond 节点，但是并不执行检查操作。
位图索引扫描更常见的一种情况是查询条件组合使用了多个索引时，例如：
EXPLAIN
SELECT *
FROM test
WHERE vn = 1000 OR vd = '2024-01-01'::date;
QUERY PLAN |
-------------------------------------------------------------------------------+
Bitmap Heap Scan on test (cost=9.06..146.25 rows=61 width=141) |
Recheck Cond: ((vn = '1000'::numeric) OR (vd = '2024-01-01'::date)) |
-> BitmapOr (cost=9.06..9.06 rows=61 width=0) |
-> Bitmap Index Scan on idx_test_vn (cost=0.00..4.29 rows=1 width=0) |
Index Cond: (vn = '1000'::numeric) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..4.74 rows=60 width=0)|
Index Cond: (vd = '2024-01-01'::date) |
查询首先基于 idx_test_vn 以及 idx_test_vd 进行了位图索引扫描，然后进行了位图合并（BitmapOr），最后基于位图结果进行回表。
位图索引扫描存在一个副作用，就是查询结果不再按照索引顺序返回，无法通过索引优化 ORDER BY。例如：
EXPLAIN
SELECT *
FROM test
WHERE vd BETWEEN '2024-01-01'::date AND '2024-01-31'::date
ORDER BY vd;
QUERY PLAN |
-------------------------------------------------------------------------------------+
Sort (cost=485.23..492.65 rows=2966 width=141) |
Sort Key: vd |
-> Bitmap Heap Scan on test (cost=46.69..314.18 rows=2966 width=141) |
Recheck Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-31'::date)) |
-> Bitmap Index Scan on idx_test_vd (cost=0.00..45.94 rows=2966 width=0) |
Index Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-31'::date))|
查询计划中增加了额外的排序节点（Sort）。
提示：位图索引扫描增加了内存和 CPU 的消耗，但是会减少磁盘 IO。
除了表之外，还有一些特殊的数据源（例如 VALUES 子句和 FROM 子句中的集合函数）拥有特殊的扫描类型。例如：
EXPLAIN
SELECT *
FROM (VALUES(1,'sql')) t(id,v);
QUERY PLAN |
-----------------------------------------+
Result (cost=0.00..0.01 rows=1 width=36)|
EXPLAIN
SELECT *
FROM pg_catalog.generate_series(1, 100);
QUERY PLAN |
--------------------------------------------------------------------+
Function Scan on generate_series (cost=0.00..1.00 rows=100 width=4)|
多表连接
如果查询涉及多表连接操作，执行计划中的扫描节点之上将会显示额外的 Join 节点。通常连接操作一次连接两个表，如果查询包含多个连接操作，按照顺序进行连接，前两个表连接的中间结果和下一个表进行连接。
PostgreSQL 实现了以下三种连接算法：
- 嵌套循环（Nested Loop）
- 哈希连接（Hash Join）
- 排序合并（Merge Join）
嵌套循环连接类似于编程语言中的嵌套 for 循环，首先从外部表（驱动表）中获取满足条件的数据，然后为每一行数据遍历一次内部表（被驱动表），获取所有匹配的数据。下图演示了嵌套循环连接的执行过程：
以下查询将 test 和它自己进行交叉连接：
EXPLAIN
SELECT *
FROM test t1
CROSS JOIN test t2;
QUERY PLAN |
-------------------------------------------------------------------------+
Nested Loop (cost=0.00..1250671.00 rows=100000000 width=282) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Materialize (cost=0.00..373.00 rows=10000 width=141) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|
PostgreSQL 选择了嵌套循环算法实现以上连接查询，其中 Materialize 说明 t2 的扫描结果进行了缓存，极大地减少了磁盘访问次数。
哈希连接使用其中一个表中满足条件的记录创建哈希表，然后扫描另一个表进行匹配。哈希连接的执行过程如下图所示：
以下查询仍然使用 test 进行自连接，但是指定了连接条件：
EXPLAIN
SELECT *
FROM test t1
JOIN test t2 ON t1.vc=t2.vc;
QUERY PLAN |
-------------------------------------------------------------------------+
Hash Join (cost=448.00..908.50 rows=10000 width=282) |
Hash Cond: ((t1.vc)::text = (t2.vc)::text) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Hash (cost=323.00..323.00 rows=10000 width=141) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|
PostgreSQL 选择了哈希连接算法实现以上连接查询，并且使用 t2 表的数据创建哈希表。
排序合并连接先将两个数据源按照连接字段进行排序（Sort），然后合并两个已经排序的集合，返回满足连接条件的结果。排序合并连接的执行过程如下图所示：
以下查询使用主键 id 字段进行连接，并且按照 t1 的主键进行排序：
EXPLAIN
SELECT *
FROM test t1
JOIN test t2 ON t1.id=t2.id
ORDER BY t1.id;
QUERY PLAN |
-------------------------------------------------------------------------------------+
Merge Join (cost=0.57..1142.57 rows=10000 width=282) |
Merge Cond: (t1.id = t2.id) |
-> Index Scan using test_pkey on test t1 (cost=0.29..496.29 rows=10000 width=141)|
-> Index Scan using test_pkey on test t2 (cost=0.29..496.29 rows=10000 width=141)|
PostgreSQL 选择了排序合并连接算法实现以上连接查询，它可以避免额外的排序操作。
集合运算
集合运算符（UNION、INTERSECT、EXCEPT）用于将多个查询语句的结果进行并集、交集、差集运算，它们也会在执行计划中显示单独的节点。例如：
EXPLAIN
SELECT *
FROM test t1
UNION ALL
SELECT *
FROM test t2;
QUERY PLAN |
-------------------------------------------------------------------+
Append (cost=0.00..746.00 rows=20000 width=141) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141)|
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141)|
其中，Append 节点意味着将两个查询的结果追加合并成一个结果。
以下是一个 INTERSECT 示例：
EXPLAIN
SELECT *
FROM test t1
INTERSECT
SELECT *
FROM test t2;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
SetOp Intersect (cost=8324.77..8624.77 rows=10000 width=666) |
-> Sort (cost=8324.77..8374.77 rows=20000 width=666) |
Sort Key: "*SELECT* 1".id, "*SELECT* 1".vc, "*SELECT* 1".vn, "*SELECT* 1".vd, "*SELECT* 1".other|
-> Append (cost=0.00..946.00 rows=20000 width=666) |
-> Subquery Scan on "*SELECT* 1" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Subquery Scan on "*SELECT* 2" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141) |
其中，SetOp Intersect 节点代表了并集运算，它由一个 Append 节点和 Sort 节点组成，因为 INTERSECT 运算符需要去除重复记录。
最后是一个 EXCEPT 示例：
EXPLAIN
SELECT *
FROM test t1
EXCEPT
SELECT *
FROM test t2;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------+
SetOp Except (cost=8324.77..8624.77 rows=10000 width=666) |
-> Sort (cost=8324.77..8374.77 rows=20000 width=666) |
Sort Key: "*SELECT* 1".id, "*SELECT* 1".vc, "*SELECT* 1".vn, "*SELECT* 1".vd, "*SELECT* 1".other|
-> Append (cost=0.00..946.00 rows=20000 width=666) |
-> Subquery Scan on "*SELECT* 1" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t1 (cost=0.00..323.00 rows=10000 width=141) |
-> Subquery Scan on "*SELECT* 2" (cost=0.00..423.00 rows=10000 width=145) |
-> Seq Scan on test t2 (cost=0.00..323.00 rows=10000 width=141) |
其中，SetOp Except 节点表示差集运算，同样由一个 Append 节点和 Sort 节点组成。
排序分组
排序（ORDER BY）和分组（GROUP BY）也是查询语句中常见的操作，它们都有专门的节点类型。例如：
EXPLAIN
SELECT *
FROM test
ORDER BY vd;
QUERY PLAN |
----------------------------------------------------------------+
Sort (cost=987.39..1012.39 rows=10000 width=141) |
Sort Key: vd |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|
虽然 vd 字段存在索引，但是查询需要返回全部数据，PostgreSQL 还是选择了全表扫描加排序（Sort）的方式。
如果索引能够同时完成数据过滤（WHERE）和排序，执行计划中就不会出现 Sort 节点。例如：
EXPLAIN
SELECT *
FROM test
WHERE vn = 1000
ORDER BY id;
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = '1000'::numeric) |
索引 idx_test_vn 包含了 vn 以及 id 字段。
PostgreSQL 实现了两种分组算法：哈希聚合算法以及排序聚合算法。
哈希聚合算法使用一个临时哈希表对数据进行分组聚合，完成数据哈希之后的结果就是分组结果。例如：
EXPLAIN
SELECT vc,count(*)
FROM test
GROUP BY vc;
QUERY PLAN |
---------------------------------------------------------------+
HashAggregate (cost=373.00..473.00 rows=10000 width=28) |
Group Key: vc |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=20)|
vc 字段没有索引，PostgreSQL 选择了哈希聚合算法（HashAggregate）。
排序聚合算法首先将数据按照分组字段进行排序，将每个组内的数据都排列到一起，然后进行聚合操作。例如：
EXPLAIN
SELECT vc,count(*)
FROM test
GROUP BY vc
ORDER BY vc;
QUERY PLAN |
---------------------------------------------------------------------+
GroupAggregate (cost=987.39..1162.39 rows=10000 width=28) |
Group Key: vc |
-> Sort (cost=987.39..1012.39 rows=10000 width=20) |
Sort Key: vc |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=20)|
考虑到查询结果还需要进行排序，PostgreSQL 选择了排序聚合算法（Sort + GroupAggregate）。
排序聚合算法还可以基于索引避免排序操作，例如：
EXPLAIN
SELECT vn,count(*)
FROM test
GROUP BY vn
ORDER BY vn;
QUERY PLAN |
----------------------------------------------------------------------------------------+
GroupAggregate (cost=0.29..504.29 rows=10000 width=20) |
Group Key: vn |
-> Index Only Scan using idx_test_vn on test (cost=0.29..354.29 rows=10000 width=12)|
vn 字段存在索引，因此执行计划中只有 GroupAggregate 节点，而没有 Sort 节点。
限制结果
Top-N 查询和分页查询通常只需要返回有限数量的结果，例如：
EXPLAIN ANALYZE
SELECT *
FROM test
ORDER BY vn
FETCH FIRST 5 ROWS ONLY;
QUERY PLAN |
-------------------------------------------------------------------------------------------------------------------------------+
Limit (cost=0.29..0.91 rows=5 width=141) (actual time=0.013..0.017 rows=5 loops=1) |
-> Index Scan using idx_test_vn on test (cost=0.29..1246.20 rows=10000 width=141) (actual time=0.012..0.015 rows=5 loops=1)|
Planning Time: 0.084 ms |
Execution Time: 0.030 ms |
执行计划中的 Limit 节点表示 PostgreSQL 在获取足够数据行之后停止底层操作，索引扫描（Index Scan）不仅避免了排序操作，而且只需要扫描 5 个索引条目（actual time=0.012…0.015 rows=5 loops=1）就可以终止扫描，这种优化技术被称为管道（pipelined）操作。
Limit 操作的性能依赖于底层操作的实现，如果底层执行的是非管道操作，例如：
EXPLAIN ANALYZE
SELECT *
FROM test
ORDER BY vc
FETCH FIRST 5 ROWS ONLY;
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------+
Limit (cost=489.10..489.11 rows=5 width=141) (actual time=3.361..3.362 rows=5 loops=1) |
-> Sort (cost=489.10..514.10 rows=10000 width=141) (actual time=3.360..3.360 rows=5 loops=1) |
Sort Key: vc |
Sort Method: top-N heapsort Memory: 27kB |
-> Seq Scan on test (cost=0.00..323.00 rows=10000 width=141) (actual time=0.015..0.529 rows=10000 loops=1)|
Planning Time: 0.124 ms |
Execution Time: 3.384 ms |
vc 字段没有索引，所以需要执行额外的排序（Sort）。排序可能导致明显的性能问题，因为 Limit 节点需要等待所有数据（actual time=0.015…0.529 rows=10000 loops=1）完成排序之后才能返回数据。
访问谓词与过滤谓词
对于 WHERE 子句（谓词），PostgreSQL 提供了三种不同的实现方法：
- 索引访问谓词
- 索引过滤谓词
- 表级过滤谓词
索引访问谓词（Index Access Predicate）指定了索引叶子节点遍历的开始和结束条件。例如：
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE id BETWEEN 100 AND 120;
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.71 rows=21 width=141) (actual time=0.007..0.011 rows=21 loops=1)|
Index Cond: ((id >= 100) AND (id <= 120)) |
Planning Time: 0.133 ms |
Execution Time: 0.024 ms |
其中，Index Cond 表示索引扫描时基于该条件开始和结束，所以它属于访问谓词。
索引过滤谓词（Index Filter Predicate）在遍历索引叶子节点时用于判断是否返回该索引项，但是不会用于判断遍历的开始和结束条件，也就不会缩小索引扫描的范围。例如：
CREATE INDEX idx_test_vdvc ON test(vd, vc);
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE vd BETWEEN '2024-01-01'::date AND '2024-01-02'::date AND vc = 'xxx';
QUERY PLAN |
--------------------------------------------------------------------------------------------------------------------+
Index Scan using idx_test_vdvc on test (cost=0.29..9.36 rows=1 width=141) (actual time=0.024..0.024 rows=0 loops=1)|
Index Cond: ((vd >= '2024-01-01'::date) AND (vd <= '2024-01-02'::date) AND ((vc)::text = 'xxx'::text)) |
Planning Time: 0.124 ms |
Execution Time: 0.040 ms |
idx_test_vdvc 索引基于 vd 和 vc 两个字段，但是查询条件中只有 vd 用于决定索引遍历的开始条件和结束条件，vc 字段只能用于判断是否返回该索引项。因为 vd 是范围条件，导致索引节点中的 vc 字段不再具体顺序性。PostgreSQL 执行计划没有区分索引访问谓词和索引过滤谓词，统一显示为 Index Cond。
注意：索引过滤谓词看似使用了索引，但是随着数据量的增长可能导致性能明显下降，因为它没有减少索引扫描的范围。
对于以上查询语句，如果我们创建 idx_test_vdvc 索引时把 vc 字段放在最前面，就可以充分利用索引优化性能，因为此时所有的谓词都是所有访问谓词。
表级过滤谓词（Table Level Filter Predicate）是指谓词中的非索引字段在表级别进行判断，这意味着数据库需要读取表中的数据然后再应用该条件。例如：
EXPLAIN ANALYZE
SELECT *
FROM test
WHERE id = 100 AND other = 'N/A';
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141) (actual time=0.019..0.020 rows=1 loops=1)|
Index Cond: (id = 100) |
Filter: (other = 'N/A'::bpchar) |
Planning Time: 0.103 ms |
Execution Time: 0.037 ms |
查询使用了主键索引扫描（Index Scan），其中 id 是索引访问谓词（Index Cond），other 是表级过滤谓词（Filter）。
提示：一般来说，对于相同的查询语句，访问谓词的性能好于索引过滤谓词，索引过滤谓词的性能好于表级过滤谓词。
输出参数
最后我们介绍一下 EXPLAIN 语句的完整语法：
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
其中 option 可以为以下选项之一:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
GENERIC_PLAN [ boolean ]
BUFFERS [ boolean ]
WAL [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
其中，ANALYZE 和 VERBOSE 选项支持两种指定方法；其他选项需要使用括号包含，多个选项使用逗号进行分隔。
statement 可以是以下语句之一：SELECT、INSERT、UPDATE、DELETE、MERGE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS、CREATE MATERIALIZED VIEW AS。
boolean 用于启用或者禁用相关选项。TRUE、ON 或者 1 表示启用，FALSE、OFF 或者 0 表示禁用。如果忽略了 boolean 设置，默认为启用。
ANALYZE
ANALYZE 选项不仅显示预估的执行计划，还会实际执行相应的语句，并且返回执行时间和其他信息统计。该选项默认为 FALSE。
一方面，为了测量执行计划中每个节点的执行时成本，当前 EXPLAIN ANALYZE 的实现在执行计划中增加了一些分析开销，因此执行 EXPLAIN ANALYZE 命令有时候会导致查询比正常运行花费的时间明显更长。具体的分析开销取决于查询语句以及数据库运行的平台，有可能查询节点每次执行只需要很短的时间，但是操作系统获取时间的调用反而更慢，可以使用 pg_test_timing 工具测量系统的计时开销。
另一方面， EXPLAIN ANALYZE 不需要将查询结果发送到客户端，因此没有包含网络传输和转换成本。
VERBOSE
VERBOSE 选项用于在执行计划中显示额外的信息。例如：
EXPLAIN VERBOSE
SELECT *
FROM test;
QUERY PLAN |
------------------------------------------------------------------+
Seq Scan on emerald.test (cost=0.00..323.00 rows=10000 width=141)|
Output: id, vc, vn, vd, other |
以上 EXPLAIN VERBOSE 显示了顺序扫描节点输出的字段列表（Output），以及包含模式名限定的表名（emerald.test）。
对于不同的操作节点，VERBOSE 选项还会显示其他额外信息。该选项默认禁用。
COSTS
COSTS 选项用于输出每个计划节点的预估启动成本和总成本，以及预估行数和平均长度。该选项默认启用。例如：
EXPLAIN (COSTS)
SELECT *
FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|
SETTINGS
SETTINGS 选项用于显示配置参数，尤其是影响查询计划的非默认设置的参数。该选项默认禁用。例如：
EXPLAIN (SETTINGS)
SELECT *
FROM test;
QUERY PLAN |
----------------------------------------------------------+
Seq Scan on test (cost=0.00..323.00 rows=10000 width=141)|
Settings: search_path = 'hrdb, public, "$user"' |
GENERIC_PLAN
PostgreSQL 16 版本增加了 GENERIC_PLAN 选项，可以为预编译语句 生成通用执行计划，这种执行计划不依赖于绑定变量（例如 $1、$2等）的值。例如：
EXPLAIN (GENERIC_PLAN)
SELECT *
FROM test
WHERE vn = $1;
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = $1) |
GENERIC_PLAN 选项默认禁用，而且不能和 ANALYZE 选项一起使用，因为 ANALYZE 需要执行语句。
另外，预编译语句也可能使用定制执行计划，也就是使用绑定变量的具体值创建执行计划。例如：
PREPARE query_test(numeric)
AS
SELECT *
FROM test
WHERE vn = $1;
EXPLAIN EXECUTE query_test(10);
QUERY PLAN |
------------------------------------------------------------------------+
Index Scan using idx_test_vn on test (cost=0.29..8.30 rows=1 width=141)|
Index Cond: (vn = '10'::numeric) |
DEALLOCATE query_test;
索引扫描的访问谓词中使用了具体的参数值（10）。
提示：运行时参数 plan_cache_mode 决定了预编译语句使用通用执行计划还是定制执行计划。
BUFFERS
BUFFERS 选项用于显示缓冲区使用情况，默认禁用。例如：
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM test
WHERE id = 1000;
QUERY PLAN |
----------------------------------------------------------------------------------------------------------------+
Index Scan using test_pkey on test (cost=0.29..8.30 rows=1 width=141) (actual time=0.030..0.032 rows=1 loops=1)|
Index Cond: (id = 1000) |
Buffers: shared hit=3 |
Planning Time: 0.266 ms |
Execution Time: 0.071 ms |
其中，shared hit 表示共享块命中。
具体来说，BUFFERS 选项显示的信息包括共享内存块命中（hit）、读取（read）、标记脏块（dirtied）以及写入（written）数量，本地内存块命中（hit）、读取（read）、标记脏块（dirtied）以及写入（written）数量，临时内存块的读取（read）和写入（written）数量。如果启用了服务器参数 track_io_timing ，还会显示读写数据文件块和临时文件块的时间（毫秒）。
其中，一次命中意味着避免了一次磁盘读取，因为所需数据块已经存在缓存中。共享内存块包含了普通表和索引的缓存数据，本地内存块包含了临时表和索引的缓存数据；临时内存块包含了排序、哈希、物化节点等操作使用的临时数据。
脏块的数量表示之前未改动，但是当前查询修改的数据块；写入块的数量表示之前被标记为脏块，同时在当前查询处理过程总被后台进程刷新到磁盘的数据块。上层节点显示的数量包含了子节点的数量，对于 TEXT 输出格式，只显示非零数据值。
WAL
WAL 选项用于显示有关预写式日志记录生成的信息。具体来说，包括记录数、全页镜像数（fpi）以及生成的 WAL（字节）。 如果 FORMAT 选项的值为 TEXT（默认值），只显示非零信息。 该选项只能在启用 ANALYZE 选项时使用，默认为禁用。
TIMING
TIMING 选项用于显示每个计划节点的启用时间和完成时间（毫秒），该选项只能在启用 ANALYZE 选项时使用，默认为启用。
某些平台上重复读取系统时间可能会明显影响查询性能，如果只关注实际返回的行数，可以在启用 ANALYZE 选项时将该选项禁用。即使关闭了节点的计时功能，整个语句的运行时间仍然会统计并显示。
SUMMARY
SUMMARY 选项用于在执行计划之后显示汇总信息（例如总的时间消耗）。如果启用了 ANALYZE 选项，默认显示汇总信息；否则默认不会显示汇总信息。
对于 EXPLAIN EXECUTE 语句，Planning time 包含了从缓存中获取执行计划或者重新计划消耗的时间。
FORMAT
FORMAT 选项用于指定执行计划的输出格式，可以使用 TEXT、XML、JSON 或者 YAML 格式。默认输出格式为 TEXT，其他格式输出的内容和 TEXT 格式相同，只是更方便程序处理。例如：
EXPLAIN (FORMAT JSON)
SELECT *
FROM test;
[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "test",
"Alias": "test",
"Startup Cost": 0.00,
"Total Cost": 323.00,
"Plan Rows": 10000,
"Plan Width": 141
}
}
]
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
27
0- 0
已为社区贡献14条内容
所有评论(0)