PostgreSQL执行计划

PostgreSQL执行计划

PostgreSQL在执行任何 SQL 语句之前，优化器都会为它创建一个执行计划（Query Plan）。执行计划描述了 SQL 语句的具体实现步骤，可以使用EXPLAIN命令查看生成的查询计划。当我们遇到慢查询等性能问题时，通常可以先查看 SQL 语句的执行计划。

1. EXPLAIN基础

查询计划的结构是一个计划结点的树。最底层的结点是扫描结点：它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型：顺序扫描、索引扫描、位图索引扫描。 也还有不是表的行来源，例如VALUES子句和FROM中返回集合的函数，它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作，那么就会在扫描结点之上有其它额外的结点来执行这些操作。 并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的结点类型。 EXPLAIN给计划树中每个结点都输出一行，显示基本的结点类型和计划器为该计划结点的执行所做的开销估计。 第一行（最上层的结点）是对该计划的总执行开销的估计；计划器试图最小化的就是这个数字。

EXPLAIN 语句的基本语法如下：

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

这里 option可以是：

ANALYZE [ boolean ]

VERBOSE [ boolean ]

COSTS [ boolean ]

SETTINGS [ boolean ]

BUFFERS [ boolean ]

WAL [ boolean ]

TIMING [ boolean ]

SUMMARY [ boolean ]

FORMAT { TEXT | XML | JSON | YAML }

参数解释：

ANALYZE：执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。

VERBOSE：显示关于计划的额外信息。 特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。 如果已经被计算，查询标识符也会被显示，详请参见compute_query_id。 这个参数默认被设置为FALSE。

COSTS：包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。

SETTINGS：包括有关配置参数的信息。具体来说，包括影响查询计划的选项，其值与内置默认值不同。此参数默认为FALSE。

BUFFERS：包括有关缓冲区使用情况的信息。具体包括共享块命中、读取、脏化和写入的数量，本地块命中、读取、脏化和写入的数量，临时块读取和写入的数量，以及读取和写入数据文件块和临时文件块所花费的时间（以毫秒为单位），如果启用了track_io_timing。 命中表示在需要时已在缓存中找到块，因此避免了读取。 共享块包含来自常规表和索引的数据；本地块包含来自临时表和索引的数据；而临时块包含在排序、哈希、Materialize计划节点等情况下使用的短期工作数据。 脏化块的数量表示此查询更改的先前未修改的块数量；而写入块的数量表示此后端在查询处理过程中从缓存中驱逐的先前脏化的块数量。 对于上层节点显示的块数包括其所有子节点使用的块数。在文本格式中，仅打印非零值。默认值为FALSE。

WAL：包括有关WAL记录生成的信息。 具体来说，包括记录数、整页图像数（fpi）和生成的WAL字节数量。 在文本格式中，仅打印非零值。 此参数只能在同时启用ANALYZE时使用。 它默认为FALSE。

TIMING：在输出中包括实际启动时间以及在每个结点中花掉的时间。反复读取系统时钟的负荷在某些系统上会显著地拖慢查询，因此在只需要实际的行计数而不是实际时间时，把这个参数设置为FALSE可能会有用。即便用这个选项关闭结点层的计时，整个语句的运行时间也总是会被度量。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为TRUE。

SUMMARY：在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用ANALYZE 时默认包含摘要信息，但默认情况下不包含摘要信息，但可以使用此选项启用摘要信息。 使用EXPLAIN EXECUTE中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。

FORMAT：指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。这个参数默认被设置为TEXT。

Boolean：指定被选中的选项是否应该被打开或关闭。可以写TRUE、ON或1来启用选项，写FALSE、OFF或0禁用它。boolean值也能被忽略，在这种情况下会假定值为TRUE。

Statement：任何SELECT，INSERT，UPDATE， DELETE，MERGE， VALUES，EXECUTE， DECLARE，CREATE TABLE AS，或 CREATE MATERIALIZED VIEW AS语句，您希望查看其执行计划。

注意：加上ANALYZE 选项后，会真正执行实际的SQL。如果SQL是Insert，Update，Delete或者Create Table as语句，这些会修改数据库。所以，为了不影响实际的数据，可以把EXPLAIN

ANALYZE 放到一个事务中，执行完后回滚事务，如下：

Begin;

EXPLAIN ANALYZE ...;

Rollback;

1. 执行计划

在开始之前，首先创建表并插入一些数据：

CREATE TABLE t_inning(

inningid INTEGER PRIMARY KEY,

name VARCHAR(100),

tax NUMERIC,

playdate DATE );

INSERT INTO t_inning

SELECT id, 'player'||random(), 1000*random(),'2025-01-01'::date+(100*random())::int

FROM GENERATE_SERIES(1, 10000) id;

ANALYZE t_inning;

• 单表访问：全表扫描

执行如下：

explain select * from t_inning ;

PostgreSQL 执行计划结果包含几部分内容：操作（Seq Scan on t_inning ）、成本（cost）、预估返回的行数（rows）以及预估每行数据的平均宽度（width），单位为字节。

上例中：

Seq Scan on t_inning：表示顺序扫描表t_inning，顺序扫描也就是全表扫描，它会依次读取整个表中的数据。如果查询条件字段没有索引，一般需要执行顺序扫描，适用于返回大部分数据行。

cost=0.00..194.00：成本，描述执行一个SQL的代价是多少。成本包含两个数字，中间由“..”分隔。第一个数字代表返回第一行数据的启动成本是0.00，第二个数字代表返回全部结果的总成本是194.00。对于大多数查询而言，我们需要关注总成本；但是某些情况下（例如 EXISTS 子查询），查询计划器会选择最小的启动成本，因为执行器只需要获取一行数据。另外，如果我们使用了 LIMIT 子句限制返回的行数，查询计划器会基于两个成本计算一个合适的中间值。

rows=10000：表示会返回10000行。

width=43：表示每行平均宽度为43字节。

注：成本(cost)，默认情况下，一些主要的操作的cost为如下：

• seq_page_cost：设置规划器计算一次顺序磁盘页面抓取的开销。默认值是1.0。
• random_page_cost：设置规划器对一次非顺序获取磁盘页面的代价估计。默认值是 4.0。
• cpu_tuple_cost：设置规划器对一次查询中处理每一行的代价估计。默认值是 0.01。
• cpu_index_tuple_cost：设置规划器对一次索引扫描中处理每一个索引项的代价估计。默认值是 0.005。
• cpu_operator_cost：设置规划器对于一次查询中处理每个操作符或函数的代价估计。默认值是 0.0025。

以上执行计划的总成本194.00是如何计算的呢？我们查看一下：

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

会发现t_inning有94个磁盘页面和10000行。

cost被计算为:页面读取数(relpages) * seq_page_cost +扫描的行数(reltuples ) * cpu_tuple_cost。默认情况下，seq_page_cost是1.0，cpu_tuple_cost是0.01， 因此估计的cost是 (94 * 1.0) + (10000 * 0.01) = 194。

全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，优化器会选择使用全表扫描。

注意：实际上，如果一张表的数据很小，几时存在相关的索引，优化器仍然会使用全表扫描。例如：

create table t (id int);

insert into t values (1),(2),(3);

create index i_id on t(id);

analyze t;

explain select * from t;

explain select * from t where id =2;

• 单表访问：全表扫描 + Filter

现在，修改查询并增加一个WHERE条件，执行如下：

explain select * from t_inning where tax < 100;

注意EXPLAIN输出显示WHERE子句被当做一个“过滤器”条件附加到顺序扫描计划结点。 这意味着该计划结点为它扫描的每一行检查该条件，并且只输出通过该条件的行。因为WHERE子句的存在，估计的输出行数降低了。不过，扫描仍将必须访问所有 10000 行，因此开销没有被降低；实际上开销还有所上升（准确来说，上升了 10000 * cpu_operator_cost）以反映检查WHERE条件所花费的额外 CPU 时间。

cost被计算为: (94 * 1.0) + (10000 * 0.01) + (10000 * 0.0025) = 219。

• 单表访问：索引扫描

现在，让我们继续执行如下：

EXPLAIN SELECT inningid , playdate FROM t_inning WHERE inningid = 1000;

Index Scan表明使用索引扫描，意味着遍历索引的 B-树叶子节点，找到所有满足条件的索引项，然后通过索引指针读取表中的数据，所以使用索引扫描，通常意味着需要回表。

如果我们需要查询的字段都可以通过索引获取，PostgreSQL 可以使用仅索引扫描（Index-Only Scan）技术优化查询，查询语句不需要访问表中的数据即可返回查询结果，也就是说，不需要回表。例如：

CREATE INDEX idx_t_inning_inningid_and_playdate ON t_inning(inningid,playdate);

EXPLAIN SELECT inningid , playdate FROM t_inning WHERE inningid= 1000;

PostgreSQL 提供了覆盖索引（Covering Index），可以进一步实现 Index-Only Scan 优化。

例如：

首先，先查看一下未建覆盖索引的情况：

EXPLAIN SELECT inningid , playdate, name FROM t_inning WHERE inningid = 1000;

接下来，使用INCLUDE 语法，创建覆盖索引，并再次查看一下执行计划：

CREATE INDEX idx_t_inning_inningid_and_playdate_2 ON t_inning(inningid,playdate) INCLUDE (name);

EXPLAIN SELECT inningid , playdate, name FROM t_inning WHERE inningid = 1000;

从上面可以看到的确使用到了新建的索引
idx_t_inning_inningid_and_playdate_2

• 单表访问：位图索引扫描

索引扫描每次找到一个满足条件的索引项时，都会基于元组指针再次访问表中的数据（回表），这是一种随机 IO。如果索引扫描只返回很少的数据行，它是一个很好的访问方法。但是如果扫描索引返回的数据行比较多，大量的随机回表会导致性能下降；一个优化的方法就是把回表的随机 IO 变成顺序 IO，为此 PostgreSQL 引入了位图索引扫描。

位图索引扫描（Bitmap Index Scan）的原理是一次扫描索引获取所有满足条件的元组指针，然后在内存中基于“位图”数据结构进行排序，最后按照元组指针的物理顺序访问表（Bitmap Heap Scan）中的数据。如果走了两个索引，可以把两个索引形成的位图进行“and”或者“or”计算，合并成一个位图，再到表的数据文件中把数据读取出来。例如：

CREATE INDEX idx_t_inning_playdate ON t_inning(playdate);

EXPLAIN SELECT * FROM t_inning WHERE playdate = '2025-01-01'::date;

该查询语句返回 35行数据，使用索引扫描的话，还需要35次回表。因此，PostgreSQL 选择了位图索引的访问方法。

更常见的一种情况是查询条件组合使用了多个索引时的位图进行“and”或者“or”计算，例如：

EXPLAIN SELECT * FROM t_inning WHERE inningid < 200 or playdate = '2025-01-01'::date;

• 多表访问：嵌套循环连接（NestLoop Join）

嵌套循环连接（NestLoop Join）是在两个表做连接时最朴素的一种连接方式，在嵌套循环中，从外部表（驱动表）中获取满足条件的数据，然后为每一行数据遍历一次内部表（被驱动表），获取所有匹配的数据。下图演示了嵌套循环连接的执行过程：

例如：

EXPLAIN SELECT * FROM t_inning t1 CROSS JOIN t_inning t2;

上面中的Materialize说明 t2 的扫描结果进行了缓存，极大地减少了磁盘访问次数。下图演示了Materialize嵌套循环连接的执行过程：

执行如下：

EXPLAIN SELECT * FROM t_inning t1 JOIN (SELECT inningid FROM t_inning where playdate = '2025-01-01'::date) t2 on t1.inningid = t2.inningid;

上图显示了Indexed 嵌套循环连接方式，下图演示了Indexed 嵌套循环连接的执行过程：

嵌套循环连接的三种变体：

• 多表访问：哈希连接（Hash Join）

哈希连接使用其中一个表中满足条件的记录创建哈希表，然后扫描另一个表进行匹配。这种方式适用于较小的表可以完全放入内存的情况，这样总成本就是访问两个表的成本之和。但是如果表很大，不能完全放入内存，优化器会将它分割为若干不同的分区，把不能放入内存的部分写入磁盘的临时段，此时要有较大的临时段以便尽量提高I/O的性能。

例如：

EXPLAIN SELECT * FROM t_inning t1 JOIN t_inning t2 on t1.inningid = t2.inningid;

PostgreSQL 选择了哈希连接算法实现以上连接查询，并且使用 t2 表的数据创建哈希表。

• 多表访问：合并连接（Merge Join）

排序合并连接先将两个数据源按照连接字段进行排序（Sort），然后合并两个已经排序的集合，返回满足连接条件的结果。排序合并连接的执行过程如下图所示：

例如：

EXPLAIN SELECT * FROM t_inning t1 JOIN t_inning t2 on t1.inningid = t2.inningid order by t1.inningid ;

PostgreSQL 选择了排序合并连接算法实现以上连接查询，它可以避免额外的排序操作。

如对扫描结果进行了缓存，则下图演示了Materialize合并连接的执行过程：

其它的三种变体：

• 集合运算

集合运算符（UNION、INTERSECT、EXCEPT）用于将多个查询语句的结果进行并集、交集、差集运算，它们也会在执行计划中显示单独的节点。例如：

EXPLAIN SELECT * FROM t_inning where inningid < 100 union SELECT * FROM t_inning where inningid >500 and inningid < 1000;

其中，Append 节点意味着将两个查询的结果追加合并成一个结果。最后进行Hash聚合，分组键为：t_inning.inningid, t_inning.name, t_inning.tax, t_inning.playdate

EXPLAIN SELECT * FROM t_inning where inningid >400 and inningid < 1200 INTERSECT SELECT * FROM t_inning where inningid >500 and inningid < 1000;

其中，HashSetOp Intersect 节点代表了并集运算，因为 INTERSECT 运算符需要去除重复记录。

EXPLAIN SELECT * FROM t_inning where inningid >400 and inningid < 1200 EXCEPT SELECT * FROM t_inning where inningid >500 and inningid < 1000;

其中，HashSetOp Except 节点表示差集运算。

• 排序分组

排序（ORDER BY）和分组（GROUP BY）也有其专有的节点类型。例如：

EXPLAIN SELECT * FROM t_inning ORDER BY tax ;

如果索引能够同时完成数据过滤（WHERE）和排序，执行计划中就不会出现 Sort 节点。例如：

EXPLAIN SELECT * FROM t_inning ORDER BY inningid ;

PostgreSQL 实现了两种分组算法：哈希聚合，排序聚合。例如：

EXPLAIN SELECT playdate ,count(*) FROM t_inning GROUP BY playdate;

哈希聚合算法使用一个临时哈希表对数据进行分组聚合。例如：

EXPLAIN SELECT tax ,count(*) FROM t_inning GROUP BY tax ;

1. 解读执行计划

PostgreSQL 执行计划的结构是一棵由计划节点组成的树，EXPLAIN 命令的每一行对应一个节点。

每一行节点除了汇总信息之外，还可能包含缩进行，显示了完成该节点的底层操作。节点的执行顺序按照缩进来判断，缩进越多的越先执行，同样缩进的从上至下执行。

执行计划最底层的节点是扫描节点，通常用于从表中返回原始数据。

查看执行计划结构原则：

• 从下到上：每个箭头表示1个节点。上一个节点的输入信息来自于它的下一个节点，所以需要从最下面开始分析，依次读到最顶层。
• 从里往外：由于上层结果是下层结果的输出，所以在理清层次结构后，需要先分析最里层，再依次往外分析。
• 每一步cost包括上一步cost：最里层的启动成本是0.00，结束成本是10.00；那么上一层可能就是启动成本从10.00开始，结束成本大于10.00。

1. 相关配置项

参数提供了影响查询优化器选择不同执行计划的方法，有时，如果执行计划不是最优的，可以设置参数强制选择一个更加好的执行计划临时解决问题。一般情况下，执行计划都不会错误，错误可能是因为统计信息不及时导致，可以运行Analyze来解决。