在执行任何 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 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 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 = '2024-01-01'::date) AND (vd
