SQL执行计划

执行计划显示信息

除了设置不同的执行计划显示格式外，还可以通过不同的EXPLAIN用法，显示不同详细程度的执行计划信息。常见有如下几种，关于更多用法请参见EXPLAIN语法说明。

• EXPLAIN statement： 只生成执行计划，不实际执行。其中statement代表SQL语句。
• EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间(以毫秒计)和它实际返回的行数。
• EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。

为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE或EXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。

因此，当定位SQL运行慢问题时，如果SQL长时间运行未结束，建议通过EXPLAIN命令查看执行计划，进行初步定位。如果SQL可以运行出来，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及其实际的运行信息，以便更精准地定位问题原因。

执行计划中的常见关键字说明：

1. 表访问方式
   • Seq Scan/CStore Scan

     全表顺序扫描。最基本的扫描算子，用于行/列存表的顺序扫描。

   • Index Scan/CStore Index Scan

     行/列存表的索引扫描。行/列存表上存在索引，条件列为索引列。

     优化器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出对应行。独立地抓取数据行比顺序地读取数据的开销高很多，但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是，上层规划节点在读取索引标识出来的行位置之前，会先将它们按照物理位置排序，这样可以最小化独立抓取的开销。

     如果在WHERE里面使用的好几个字段上都有索引，那么优化器可能会使用索引的AND或OR的组合。但是这么做要求访问两个索引，因此与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。

     索引扫描可以分为以下几类，其差异在于索引的排序机制。

     • Bitmap Index Scan

       使用位图索引抓取数据页，需要索引扫描获取位图后再到基表上扫描。

     • Index Scan using index_name

       使用简单索引搜索，该方式表的数据行是以索引顺序抓取的，这样就令读取它们的开销更大，但是这里的行少得可怜，因此对行位置的额外排序并不值得。最常见的就是看到这种规划类型只抓取一行，以及那些要求ORDER BY条件匹配索引顺序的查询。因为那时候没有多余的排序步骤是必要的以满足ORDER BY。

2. 表连接方式
   • Nested Loop

     嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。

   • (Sonic) Hash Join

     哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。

   • Merge Join

     归并连接或融合连接，是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。

     因为Merge Join需要做更多的排序，所以消耗的资源更多，因此通常情况下执行性能差于Hash Join。 如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时Merge Join的性能优于Hash Join。

3. 运算符
   • sort

     对结果集进行排序。

   • filter

     EXPLAIN输出显示WHERE子句当作一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。预计的输出行数降低了，因为有WHERE子句。不过，扫描仍将必须访问所有 10000 行，因此开销没有降低；实际上它还增加了一些（确切的说，通过10000 * cpu_operator_cost）以反映检查WHERE条件的额外CPU时间。

   • LIMIT

     LIMIT限定了执行结果的输出记录数。如果增加了LIMIT，那么不是所有的行都会被检索到。

执行计划显示格式

GaussDB(DWS)对执行计划提供了normal、pretty、summary、run四种显示格式。通过设置GUC参数explain_perf_mode，可以显示不同格式的执行计划。

• normal：代表使用默认的打印格式。图1中即为此显示格式。
  图1 normal格式执行计划示例
  
• pretty：代表使用GaussDB(DWS)改进后的新显示格式。新的格式层次清晰，计划包含了plan node id，性能分析简单直接。如图2。
  图2 pretty格式执行计划示例
  
• summary：是在pretty的基础上增加了对打印信息的分析。
• run：在summary的基础上，将统计的信息输出到csv格式的文件中，以便于进一步分析。

常见类型计划

GaussDB(DWS)中当前主要存在三类分布式计划：

• FQS（fast query shipping）计划

  CN直接将原语句下发到DN，各DN单独执行，并将执行结果在CN上进行汇总。

• Stream计划

  CN根据原语句生成计划并将计划下发给DN进行执行，各DN执行过程中使用Stream算子进行数据交互。

• Remote-Query计划

  CN生成计划后，将部分原语句下发到DN，各DN单独执行，执行后将结果发送给CN，CN执行剩余计划。

现有表tt01和tt02定义如下：

1
2

CREATE TABLE tt01(c1 int, c2 int) DISTRIBUTE BY hash(c1);
CREATE TABLE tt02(c1 int, c2 int) DISTRIBUTE BY hash(c2);

类型一：FQS计划，完全下推

两表JOIN，且其连接条件为各表的分布列，在关闭stream算子的情况下，CN会直接将该语句发送至各DN执行，最后结果在CN汇总。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SET enable_stream_operator=off;
SET explain_perf_mode=normal;

EXPLAIN (VERBOSE on,COSTS off) SELECT * FROM tt01,tt02 WHERE tt01.c1=tt02.c2;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"
   Output: tt01.c1, tt01.c2, tt02.c1, tt02.c2
   Node/s: All datanodes
   Remote query: SELECT tt01.c1, tt01.c2, tt02.c1, tt02.c2 FROM dbadmin.tt01, dbadmin.tt02 WHERE tt01.c1 = tt02.c2
(4 rows)

类型二：非FQS计划，部分语句下推

两表JOIN，且连接条件中包含非分布列，此时在关闭stream算子的情况下，CN会将基表扫描语句下发至各DN，然后在CN上进行JOIN。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

SET enable_stream_operator=off;
SET explain_perf_mode=normal;

EXPLAIN (VERBOSE on,COSTS off) SELECT * FROM tt01,tt02 WHERE tt01.c1=tt02.c1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Hash Join
   Output: tt01.c1, tt01.c2, tt02.c1, tt02.c2
   Hash Cond: (tt01.c1 = tt02.c1)
   ->  Data Node Scan on tt01 "_REMOTE_TABLE_QUERY_"
         Output: tt01.c1, tt01.c2
         Node/s: All datanodes
         Remote query: SELECT c1, c2 FROM ONLY dbadmin.tt01 WHERE true
   ->  Hash
         Output: tt02.c1, tt02.c2
         ->  Data Node Scan on tt02 "_REMOTE_TABLE_QUERY_"
               Output: tt02.c1, tt02.c2
               Node/s: All datanodes
               Remote query: SELECT c1, c2 FROM ONLY dbadmin.tt02 WHERE true
(13 rows)

类型三：Stream计划，DN之间无数据交换

两表JOIN，且连接条件为各表的分布列，因此各DN无需数据交换。CN生成stream计划后，将除Gather Stream的计划下发给DN执行，在各个DN上进行基表扫描，并进行哈希连接后，发送给CN。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SET enable_fast_query_shipping=off;
SET enable_stream_operator=on;

EXPLAIN (VERBOSE on,COSTS off) SELECT * FROM tt01,tt02 WHERE tt01.c1=tt02.c2;
                     QUERY PLAN
----------------------------------------------------
 Streaming (type: GATHER)
   Output: tt01.c1, tt01.c2, tt02.c1, tt02.c2
   Node/s: All datanodes
   ->  Hash Join
         Output: tt01.c1, tt01.c2, tt02.c1, tt02.c2
         Hash Cond: (tt01.c1 = tt02.c2)
         ->  Seq Scan on dbadmin.tt01
               Output: tt01.c1, tt01.c2
               Distribute Key: tt01.c1
         ->  Hash
               Output: tt02.c1, tt02.c2
               ->  Seq Scan on dbadmin.tt02
                     Output: tt02.c1, tt02.c2
                     Distribute Key: tt02.c2
(14 rows)

类型四：Stream计划，DN之间存在数据交换

两表JOIN，且连接条件包含非分布列，在开启stream算子（SET enable_stream_operator=on）的情况下，会生成stream计划，其DN间存在数据交换。此时对于tt02表，会在各DN进行基表扫描，扫描后会通过Redistribute Stream算子，按照JOIN条件中的tt02.c1进行哈希计算后重新发送给各DN，然后在各DN上做JOIN，最后汇总到CN。

类型五：Remote-Query计划场景

因unship_func不能下推，且不满足部分下推要求（子查询下推），所以只能发送基表扫描的语句到DN，将基表数据收集到CN上来计算。

EXPLAIN PERFORMANCE详解

在SQL调优过程中经常需要执行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看SQL语句实际执行信息，通过对比实际执行与优化器的估算之间的差别来为优化提供依据。EXPLAIN PERFORMANCE相对于EXPLAIN ANALYZE增加了每个DN上的执行信息。

表定义如下：

1
2

CREATE TABLE tt01(c1 int, c2 int) DISTRIBUTE BY hash(c1);
CREATE TABLE tt02(c1 int, c2 int) DISTRIBUTE BY hash(c2);

以如下SQL查询语句为例：

1

SELECT * FROM tt01,tt02 WHERE tt01.c1=tt02.c2;

执行EXPLAIN PERFORMANCE输出的显示执行信息分为以下8个部分：

1. 执行计划

   

   以表格的形式将计划显示出来，包含有11个字段，分别是：id、operation、A-time、A-rows、E-rows、E-distinct、Peak Memory、E-memory、A-width、E-width和E-costs。字段含义如下表1。

   表1 执行字段说明

   字段	描述
   id	执行算子节点编号。
   operation	具体的执行节点算子名称。 Vector前缀的算子是指向量化执行引擎算子，一般出现含有列存表的Query中。 Streaming是一个特殊的算子，它实现了分布式架构的核心数据shuffle功能，Streaming共有三种形态，分别对应了分布式结构下不同的数据shuffle功能： Streaming (type: GATHER)：作用是coordinator从DN收集数据。 Streaming (type: REDISTRIBUTE)：作用是DN根据选定的列把数据重分布到所有的DN。 Streaming (type: BROADCAST)：作用是把当前DN的数据广播给其他所有的DN。
   A-time	各DN相应算子执行时间，一般DN上执行的算子的A-time是由[]括起来的两个值，分别表示此算子在所有DN上完成的最短时间和最长时间，包括下层算子执行时间。 注意：在整个计划中，除了叶子节点的执行时间是算子本身的执行时间，其余算子的执行时间均包含子节点的执行时间。
   A-rows	表示相应算子输出的全局总行数。
   E-rows	每个算子估算的输出行数。
   E-distinct	表示hashjoin算子的distinct估计值。
   Peak Memory	此算子在每个DN上执行时使用的内存峰值，[]中左侧为最小值，右侧为最大值。
   E-memory	DN上每个算子估算的内存使用量，只有DN上执行的算子会显示。某些场景会在估算的内存使用量后使用括号显示该算子在内存资源充足下可以自动扩展的内存上限。
   A-width	表示当前算子每行元组的实际宽度，仅对于重内存使用算子会显示，包括：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，其中(Vec)HashJoin计算的宽度是其右子树算子的宽度，会显示在其右子树上。
   E-width	每个算子输出元组的估算宽度。
   E-costs	每个算子估算的执行代价。 E-costs是优化器根据成本参数定义的单位来衡量的，习惯上以磁盘页面抓取为1个单位， 其它开销参数将参照它来设置。 每个节点的开销（E-costs值）包括它的所有子节点的开销。 开销只反映了优化器关心的东西，并没有把结果行传递给客户端的时间考虑进去。虽然这个时间可能在实际的总时间里占据相当重要的分量，但是被优化器忽略了，因为它无法通过修改规划来改变。

2. SQL Diagnostic Information

   SQL自诊断信息。优化和执行过程中识别到的性能优化点，当对DML语句进行带VERBOSE属性的EXPLAIN（EXPLAIN PERFORMANCE内置自带VERBOSE属性）时，SQL自诊断信息也会输出，以辅助性能问题定位。

3. Predicate Information (identified by plan id)

   

   谓词过滤这部分主要显示的是对应执行算子节点的过滤条件，即在整个计划执行过程中不会变的信息，主要是一些join条件和一些filter信息。

4. Memory Information (identified by plan id)

   

   内存使用信息这部分显示的是整个计划中会将内存的使用情况打印出来的算子的内存使用信息，主要是Hash、Sort算子，包括算子峰值内存（peak memory），优化器预估的内存（estimate memory），控制内存（control memory），估算内存使用（operator memory），执行时实际宽度（width），内存使用自动扩展次数（auto spread num），是否提前下盘（early spilled），以及下盘信息，包括重复下盘次数（spill Time(s)），内外表下盘分区数（inner/outer partition spill num），下盘文件数（temp file num），下盘数据量及最小和最大分区的下盘数据量（written disk IO [min, max] ）。其中sort算子不会显示具体的下盘文件数，仅在显示排序方法时显示Disk。

5. Targetlist Information (identified by plan id)

   

   这一部分显示的是每一个算子对应的输出目标列信息。

6. DataNode Information (identified by plan id)

   

   这部分将各个算子的执行时间（若包含过滤及投影也会显示对应的执行时间）、CPU、buffer的使用情况全部打印出来。

   • 算子执行信息

     

     每个算子的执行信息都包含三个部分：

     • dn_6001_6002/dn_6003_6004表示具体执行的节点信息，括号中的信息是实际的执行信息。
     • actual time表示实际的执行时间，第一个数字表示执行时进入当前算子到输出第一条数据所花费的时间，第二个数字表示输出所有数据的总执行时间。
     • rows表示当前算子输出数据行数。

     • loops表示当前算子的执行次数。需要注意，对于分区表来说，每一个分区表的扫描就是一次完整的扫描操作，当切换到下一个分区的时候，又是一次新的扫描操作。
   • CPU信息

     

     每个算子执行的过程都有CPU信息，其中cyc代表的是CPU的周期数，ex cyc表示的是当前算子的周期数，不包含其子节点；inc cyc是包含子节点的周期数；ex row是当前算子输出的数据行数；ex c/r则是ex cyc/ex row得到的每条数据所用的平均周期数。

   • Buffer信息

     

     buffers显示缓冲区信息，包括共享块和临时块的读和写。

     共享块包含表和索引，临时块在排序和物化中使用的磁盘块。上层节点显示出来的块数据包含了其所有子节点使用的块数。

   • 磁盘缓存信息（仅9.1.0.100及以上版本且olversion=3.0的存算分离V3表或外表支持）

     

     表示Disk Cache的命中信息和数据读取信息。

     miss代表磁盘缓存未命中的次数，hit表示磁盘缓存命中的次数。scanBytes表示scan查询的数据量，remoteReadBytes表示在OBS上读取的数据量。为了提升OBS的效率会对相邻的请求块合并，或者因为请求写磁盘缓存的最小粒度是block(默认1M)，可能会使得scanBytes会小于remoteReadBytes。

7. User Define Profiling

   

   自定义信息，这一部分显示的是CN和DN、DN和DN建连的时间，以及存储层的一些执行信息。

8. Query Summary

   

   这一部分主要打印总的执行时间和网络流量，包括了各个DN上初始化和结束阶段的最大最小执行时间、CN上的初始化、执行、结束阶段的时间，以及当前语句执行时系统可用内存、语句估算内存等信息。

   • DataNode executor start time：DN执行器开始时间，[min_node_name, max_node_name] : [min_time, max_time]
   • DataNode executor run time：DN执行器运行时间，[min_node_name, max_node_name] : [min_time, max_time]
   • DataNode executor end time：DN执行器结束时间，[min_node_name, max_node_name] : [min_time, max_time]
   • Remote query poll time：接收结果时用于poll等待的时间
   • System available mem：系统可用内存
   • Query Max mem：查询最大内存
   • Enqueue time：入队时间
   • Coordinator executor start time：CN执行器开始时间
   • Coordinator executor run time：CN执行器运行时间
   • Coordinator executor end time：CN执行器结束时间
   • Parser runtime：解析器运行时间
   • Planner runtime：优化器执行时间
   • 网络流量：stream算子发送的数据量
   • Query ID：查询ID
   • Unique SQL ID：约束SQL ID
   • Total runtime：总执行时间

• A-rows和E-rows的差异体现了优化器估算和实际执行的偏差度。一般情况下两者偏差越大，则可以认为优化器生成的计划的越不可信，人工干预调优的必要性越大。
• A-time中的两个值偏差越大，表明此算子的计算偏斜（在不同DN上执行时间差异）越大，人工干预调优的必要性越大。一般来说，两个相邻的算子，上层算子的执行时间包含下层算子的执行时间，但如果上层算子为stream算子，由于各线程不存在驱动关系，上层算子执行时间可能小于下层算子的执行时间，即不存在包含关系。
• Max Query Peak Memory经常用来估算SQL语句耗费内存，也被用来作为SQL语句调优时运行态内存参数设置的重要依据。一般会以EXPLAIN ANALYZE或EXPLAIN PERFORMANCE的输出作为进一步调优的输入。