在执行任何 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
}
}
]