如何阅读PG数据库的执行计划

如果我们已经找到了某条 T OP SQL, 要针对 S QL 进行优化分析，该如何进行呢？在学习其他数据库的时候，我们已经了解到了， S QL 优化主要从三个角度进行： 1）扫描方式；2）连接方式；3）连接顺序。如果解决好这三方面的问题，那么这条S QL 的执行效率就基本上是靠谱的。

看懂 S QL 的执行计划的关键也是要首先了解这三方面的基本概念，只有搞清楚了这些基本概念，才能够更好的看懂 S QL 的执行计划，下面我们分别来学习这些预备知识。

要想让 S QL 语句有好的执行效果，首先要采用正确的扫描方式。 P G 的扫描方式与 Oracle等其他数据库类似，但也存在较大的不同，为了掌握好S QL 语句优化的技术，我们首先要学会看 S QL 语句的执行计划，而看执行计划的最为基础的能力就是看懂每一步的扫描方式。下表是一个 P G 常用的表扫描方式的清单，大家一定要熟练掌握。

顺序扫描（ Seq Scan ）往往是开销最大的扫描方式，其方式是针对一个关系（表）从头到尾进行扫描，从而找到所需要的数据。如果这张表上的数据量比较大，那么这种扫描方式可能会产生较大的 I O ，消耗较多的 C PU 资源，持续较长的时间。如果某条 SQL 语句扫描某张表的时候返回的记录数较少（或者返回记录的比例较少，比如小于 5%）。而S QL 语句的 W HERE 条件中具有针对某几个字段的某些条件的，那么在这张表上创建适当的索引可能会大大提高这条 S QL 的执行效率。如果扫描返回的记录数占表的比例比较大，比如超过 50%，那么，通过索引扫描该表可能效率还不如直接进行顺序扫描。因此我们不能看到顺序扫描就认为这条 SQL 扫描数据的方式存在问题，而是要根据实际情况来判断扫描方式是否合理。

并行顺序扫描（ Parallel   Seq   Scan）是一种改良的顺序扫描，从P G 9.6 开始支持的一种新的扫描功能。如果对于某张表的扫描无法使用索引，必须进行顺序扫描，那么我们如何提高这样的扫描的性能呢？答案就是 Parallel   Seq   Scan，通过并行扫描的方式对大表进行扫描，从而减少扫描所需的时间。采用并行扫描时应该注意两个问题：第一个问题是，并行扫描会增加系统的资源开销，比如在S QL 执行时会消耗更多的 C PU/IO/ 内存等资源。如果系统资源本身存在瓶颈，那么就要尽可能限制并行扫描的数量；第二是并行扫描并不一定具有更高的效率，在不同的系统环境与数据情况下，有时候并行顺序扫描效率并不会比普通的顺序扫描更快。这取决于并行扫描的协同工作成本是否较高。

索引扫描（ Index   Scan ） 是我们希望遇到的扫描方式，不过索引唯 一扫描（ Index   Only   Scan）具有更高的效率，因为Index   Only   Scan不需要再进行回表操作，就可以完成执行工作，获得到所需要的数据，因为索引中已经包含了S QL 执行所需要的所有数据。不过我们要注意的是，有些时候，索引扫描的效率还不一定比顺序扫描高，比如某个扫描需要返回的行数较多，底层存储的顺序读性能远高于离散读，这种情况下，如果我们还一味追求索引扫描，那么可能会起到副作用。

C TE SCAN 是一种特殊的扫描，当 S QL 语句中存在 C TE 结构（语法上的 W ITH …） ,那么在S QL 的执行计划中会看到 C TE SCAN 的内容。相当于从一个固化的子查询体中获得数据。 C TE 结构在一次 S QL 执行中只执行一次，但是可以给 S QL 中的子查询多次使用，从而减少响应的开销。

Function   Scan也是一种特殊的扫描方式，是从函数中获取数据。

针对一个单表的访问，我们只要选择最适合的表扫描方式就可以实现优化了，不过我们面对的 S QL 往往不是一张单表访问的，很多 S QL 涉及多张表的关联操作。因此仅仅了解 P G 数据库的扫描方式是不够的，我们需要认真学习一下 P G 数据库的表连接方式。和其他关系型数据库类似， PostgreSQL 支持三种连接操作：嵌套循环连接 （ Nested   Loop   Join） 、合并连接 （ Merge   Join）和 散列连接 （ Hash   Join） 。PostgreSQL 中的嵌套循环连接和合并连接有几种变体。 要注意的是这里所说的 P G 数据库的表连接方式与 S QL 语句中的表连接不是一码事。 PostgreSQL支持的三种join方法都可以进行所有的join操作，不仅是INNER JOIN，还有LEFT/RIGHT OUTER JOIN、FULL OUTER JOIN等 。

Nested   Loop   Join（嵌套循环连接）是最基本的连接操作，它可以用于任何连接条件。 PostgreSQL 支持嵌套循环连接 ，包括其多 种变体。 参与 Nested  Loo p Join 的两张表分为外表（ Outer）和内表（Inner），首先找出外表符合条件的数据集，然后针对这个数据集的每一行进行一次循环，找出内表中符合条件的数据。针对内表的扫描可能是Index   Scan，也可能是Seq   Scan。如果内表数据量不大，那么Seq   Scan是可以接受的，如果内表比较大，那么进行Seq   Scan的成本太高，就可能导致Nested   Loop的成本过高。因此这种情况下，就需要在内表上创建适当的索引来进行优化。如果关联条件使用索引的效果不佳，那么Nested   Loop连接的性能就无法优化了。另外如果外表的结果集太大，有上万甚至几十万条记录，那么Nested   Loop的循环次数就很大，哪怕内表扫描使用Index   Scan，总体效率也不高。

每当读取外部表的每个元组时，上述嵌套循环连接必须扫描内部表的所有元组。如果上面所说的情况出现，由于为每个外表元组扫描整个内表是一个昂贵的过程， PostgreSQL 通过一种变种的 Nested   Loop连接方式- 物化嵌套循环连接（Materialized Nested Loop Join）以降低内表的总扫描成本 ，从而解决这个问题 。

=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                         QUERY PLAN                                          

----------------------------------------------------------------------------------

 Nested Loop  (cost=1000.00..180434.51 rows=2 width=8)

   Join Filter: (o.o_id = i.o_id)

   ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

         Filter: (o_id < 3000)

   ->  Materialize  (cost=1000.00..178450.45 rows=29 width=8)

         ->  Gather  (cost=1000.00..178450.31 rows=29 width=8)

               Workers Planned: 2

               ->  Parallel Seq Scan on test_outer o  (cost=0.00..177447.41 rows=12 width=8)

                     Filter: ((o_w_id = 29) AND (o_c_id = 1831))

(9 rows)

从上面的执行计划看，针对表的过滤条件比较好，筛选后只有 29条记录，因此针对这张表的条件建立了一个物化视图，用t est_inner 作为外表，执行 n ested loop 。

第二种常用的表连接方式是 Merge   Join（合并连接）在一些其他数据库中也叫 Sort Merge Join ，是因为两个结果集做 JOIN 之前，都需要对连接字段进行排序，然后再进行连接。如果结果集数量不大，所有元组都可以存储在内存中，那么排序操作就可以在内存中进行；否则，将使用临时文件。使用临时文件排序的效率远低于内存排序，因此要确保 w ork_mem 的配置足够大，从而提高合并连接的性能。与嵌套循环连接一样，合并连接也支持物化合并连接来物化内表，使内表扫描更加高效。 Merge   Join往往在内外表的大小相差较小的情况下有较好的效果。

第三种常用的表连接方式是 Hash   Join（哈希连接）。与Merge  Join 类似， H ash  Join 只能用于自然连接和等连接。 PostgreSQL 中的 H ash  Join的行为取决于表的大小。如果目标表足够小（更准确地说，内表的大小是 work_mem 的 25% 或更少），它将是一个简单的两阶段内存哈希连接；否则 需要采用具有倾斜处理的 混合 哈希 连接。

内存中哈希连接（ In-memory   Hash   Join） 是在work_mem上处理的，这个hash表区在PostgreSQL中称为batch。一批具有散列槽，内部称为桶 。外表上构建好 Hash桶之后，内表的连接字段逐个探测Hash桶，完成连接操作。  

当内表的元组无法在work_mem中存储为一个batch时，PostgreSQL使用了混合散列连接和skew算法，这是基于混合散列连接的一种变体。在构建和探测阶段，PostgreSQL 准备多个批次。批次数与桶数相同 ， 在这个阶段，work_mem中只分配了一个batch，其他batch作为临时文件创建；并将属于这些批次的元组写入相应的文件并使用临时元组存储功能进行保存。在混合哈希联接中，构建和探测阶段执行的次数与批次数相同，因为内表和外表存储在相同的批次数中。在构建和探测阶段的第一轮中，不仅创建了每个批次，而且处理了内部表和外部表的第一批。另一方面，第二轮和后续轮次的处理需要向/从临时文件写入和重新加载，因此这些是昂贵的过程。因此，PostgreSQL 还准备了一个名为skew的特殊批处理，以在第一轮更有效地 处理更多的元组。

了解了表的扫描方式与表连接的方式之后，我们就可以来分析 S QL 的执行计划了。不过在看执行计划之前，我们还需要了解一下执行计划中的每个节点的操作。常见的操作包括如下几种：

l  join – 采用某种方法把两个 n ode 的数据连接起来

l  sort – 进行排序操作

l  limit – 通过 l imit 结束扫描，限制返回的数据量

l  aggregate – 进行汇总

l  hash aggregate – 通过 h ash 分组数据

l  unique – 对于已经排序的数据进行除重

l  gather – 从不同的并发 w orker 中汇总数据

学习了每个节点的操作符，我们基本上就能看懂 P G 的执行计划了。我们可以使用 e xplain 命令来查看 P G 的 S QL 语句的执行计划。 Explain 命令的语法如下：

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

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

 ANALYZE 执行 S QL 并且显示执行细节

 VERBOSE 详细输出

 COSTS 显示执行计划开销

 BUFFERS 显示查询的 b uffers 操作信息

 TIMING 显示执行消耗的时间

 SUMMARY 在最后显示汇总信息

FORMAT TEXT / XML / JSON / YAML 显示格式选择

下面我们还是以上面举例的那条 S QL 来看看 SQL 的执行计划。通过 e xplain 命令我们可以获得某条 S QL 语句的执行计划。比如下面的 S QL:

PG=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                         QUERY PLAN                                          

-------------------------------------------------------------------------------------------

 Nested Loop  (cost=1000.00..180434.51 rows=2 width=8)

   Join Filter: (o.o_id = i.o_id)

   ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

         Filter: (o_id < 3000)

   ->  Materialize  (cost=1000.00..178450.45 rows=29 width=8)

         ->  Gather  (cost=1000.00..178450.31 rows=29 width=8)

               Workers Planned: 2

               ->  Parallel Seq Scan on test_outer o  (cost=0.00..177447.41 rows=12 width=8)

                     Filter: ((o_w_id = 29) AND (o_c_id = 1831))

(9 rows)

我们看到最下面的两行，只针对 test_outer 表做并行 Seq   Scan，条件正是S QL 语句中针对该表的两个过滤条件。 Parallel   Sequence   Scan的成本为:

(cost=0.00..177447.41 rows=12 width=8)

从上面的数据可以看出， Parallel  Seq Scan 的成本是 177447.41。经过Gather后生成了一个物化视图，成本变为178450.45。rows=12指出了本操作返回的行数，而w idth =8指出了每行数据的长度，r ows*width 可以计算出操作涉及的字节数。

然后执行了一个和物化视图同等级的 Seq   Scan，是针对 test_inner 表的，这个扫描操作：

  ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

这个操作的成本为 691.75，返回2971条记录。然后这两个结果集之间进行 Join, 方式采用的是 Nested   Loop。

读懂了执行计划，就可以判断执行计划中那些地方存在问题了。我们看到对于 test_outer 表的扫描采用 Parallel   Seq   Scan的成本占比很高，如果要优化这条S QL ，可以考虑创建一个 o _c_id 和 o _w_id 的索引来进一步优化。

highgo=# create index idx_outer1 on test_outer(o_c_id,o_w_id);

CREATE INDEX

highgo=# explain select o.o_c_id,i.apd from test_outer o,test_inner i where o.o_id=i.o_id and o.o_w_id=29 and o.o_c_id=1831 and i.o_id<3000;

                                      QUERY PLAN                                       

---------------------------------------------------------------------------------------

 Hash Join  (cost=729.32..760.00 rows=2 width=8)

   Hash Cond: (o.o_id = i.o_id)

   ->  Index Scan using idx_outer1 on test_outer o  (cost=0.43..30.98 rows=29 width=8)

         Index Cond: ((o_c_id = 1831) AND (o_w_id = 29))

   ->  Hash  (cost=691.75..691.75 rows=2971 width=8)

         ->  Seq Scan on test_inner i  (cost=0.00..691.75 rows=2971 width=8)

               Filter: (o_id < 3000)

(7 rows) 可以看出，执行计划中使用了这个索引，而且表连接方式也变成了 Hash   Join，Cost也下降了上百倍。这是P G 数据库 S QL 优化最为常用的方法。