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

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

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

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

扫描方式简称	扫描方式说明
Seq Scan	顺序扫描整个对象
Parallel Seq Scan	采用并行方式顺序扫描整个对象
Index Scan	采用离散读的方式，利用索引访问某个对象
Index Only Scan	仅通过索引，不访问表快速访问某个对象
Bitmap Index Scan	通过多个索引扫描后形成位图找到符合条件的数据
Bitmap Heap Scan	往往跟随bitmap index scan，使用该扫描生成的位图访问对象
CTE Scan	从CTE(Common Table Expression)中扫描数据 (WITH Block)
Function Scan	从存储过程中扫描数据

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

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

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

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

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

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

Nested Loop Join（嵌套循环连接）是最基本的连接操作，它可以用于任何连接条件。PostgreSQL 支持嵌套循环连接，包括其多种变体。参与Nested Loop 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条记录，因此针对这张表的条件建立了一个物化视图，用test_inner作为外表，执行nested loop。

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

第三种常用的表连接方式是Hash Join（哈希连接）。与Merge Join类似，Hash  Join只能用于自然连接和等连接。PostgreSQL 中的Hash  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的特殊批处理，以在第一轮更有效地处理更多的元组。

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

1. join – 采用某种方法把两个node的数据连接起来
2. sort – 进行排序操作
3. limit –通过limit结束扫描，限制返回的数据量
4. aggregate – 进行汇总
5. hash aggregate – 通过hash分组数据
6. unique – 对于已经排序的数据进行除重
7. gather – 从不同的并发worker中汇总数据

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

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

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

 ANALYZE 执行SQL并且显示执行细节

 VERBOSE 详细输出

 COSTS 显示执行计划开销

 BUFFERS 显示查询的buffers操作信息

 TIMING 显示执行消耗的时间

 SUMMARY 在最后显示汇总信息

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

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

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，条件正是SQL语句中针对该表的两个过滤条件。Parallel Sequence Scan的成本为:

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

从上面的数据可以看出，Parallel Seq Scan的成本是177447.41。经过Gather后生成了一个物化视图，成本变为178450.45。rows=12指出了本操作返回的行数，而width=8指出了每行数据的长度，rows*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的成本占比很高，如果要优化这条SQL，可以考虑创建一个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也下降了上百倍。这是PG数据库SQL优化最为常用的方法。