详解

执行计划

以如下SQL语句为例：

1
2
3
4
5

select 
    cjxh, 
    count(1) 
from dwcjk
group by cjxh;

执行EXPLAIN的输出为：

openGauss=# EXPLAIN SELECT * FROM t1,t2 WHERE t1.c1 = t2.c2;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Hash Join  (cost=23.73..341.30 rows=16217 width=180)
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1  (cost=0.00..122.17 rows=5317 width=76)
   ->  Hash  (cost=16.10..16.10 rows=610 width=104)
         ->  Seq Scan on t2  (cost=0.00..16.10 rows=610 width=104)
(5 rows)

执行计划字段解读（横向）：

• id：执行算子节点编号。
• operation：具体的执行节点算子名称。

  。

  Streaming是一个特殊的算子，它实现了分布式架构的核心数据shuffle功能，Streaming共有三种形态，分别对应了分布式结构下不同的数据shuffle功能：

  • Streaming (type: GATHER)：作用是coordinator从DN收集数据。
  • Streaming(type: REDISTRIBUTE)：作用是DN根据选定的列把数据重分布到所有的DN。
  • Streaming(type: BROADCAST)：作用是把当前DN的数据广播给其他所有的DN
• E-rows：每个算子估算的输出行数。
• E-memory：DN上每个算子估算的内存使用量，只有DN上执行的算子会显示。某些场景会在估算的内存使用量后使用括号显示该算子在内存资源充足下可以自动扩展的内存上限。
• E-width：每个算子输出元组的估算宽度。
• E-costs：每个算子估算的执行代价。
  • E-costs是优化器根据成本参数定义的单位来衡量的，习惯上以磁盘页面抓取为1个单位， 其它开销参数将参照它来设置。
  • 每个节点的开销（E-costs值）包括它的所有子节点的开销。
  • 开销只反映了优化器关心的东西，并没有把结果行传递给客户端的时间考虑进去。虽然这个时间可能在实际的总时间里占据相当重要的分量，但是被优化器忽略了，因为它无法通过修改规划来改变。

执行计划层级解读（纵向）：

1. 第一层：Seq Scan on t2

   表扫描算子，用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从buffer或者磁盘上读上来输送给上层节点参与计算。

2. 第二层：Hash

   Hash算子，作用是把下层计算输送上来的算子计算hash值，为后续hash join操作做数据准备。

3. 第三层：Seq Scan on t1

   表扫描算子，用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从buffer或者磁盘上读上来输送给上层节点参与hash join计算。

4. 第四层：Hash Join

   join算子，主要作用是将t1表和t2表的数据通过hash join的方式连接，并输出结果数据。

执行计划中的主要关键字说明：

1. 表访问方式
   • Seq Scan

     全表顺序扫描。

   • Index Scan

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

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

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

     • Bitmap Index Scan

       使用位图索引抓取数据页。

     • Index Scan using index_name

       使用简单索引搜索，该方式按照索引键的顺序在索引表中抓取数据。该方式最常用于在大数据量表中只抓取少量数据的情况，或者通过ORDER BY条件匹配索引顺序的查询，以减少排序时间。

     • Index-Only Scan

       当需要的所有信息都包含在索引中时，仅索引扫描便可获取所有数据，不需要引用表。

   • Bitmap Heap Scan

     从其他操作创建的位图中读取页面，过滤掉不符合条件的行。位图堆扫描可避免随机I/O，加快读取速度。

   • TID Scan

     通过TupleID扫描表。

   • Index Ctid Scan

     通过Ctid上的索引对表进行扫描。

   • CTE Scan

     CTE对子查询的操作进行评估并将查询结果临时存储，相当于一个临时表。CTE Scan算子对该临时表进行扫描。

   • Foreign Scan

     从远程数据源读取数据。

   • Function Scan

     获取函数返回的结果集，将它们作为从表中读取的行并返回。

   • Sample Scan

     查询并返回采样数据。

   • Subquery Scan

     读取子查询的结果。

   • Values Scan

     作为VALUES命令的一部分读取常量。

   • WorkTable Scan

     工作表扫描。在操作中间阶段读取，通常是使用WITH RECURSIVE声明的递归操作。

   • CStore Index Ctid Scan

     按照索引的条件进行扫描，返回满足条件的tid集合。

   • CStore Index Heap Scan

     实现tid集合的交、差、并运算，并通过集合的结果获取对应元组。

2. 表连接方式
   • Nested Loop

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

   • (Sonic) Hash Join

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

   • Merge Join

     归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时融合连接的性能优于哈希连接。

3. 运算符
   • sort

     对结果集进行排序。

   • filter

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

   • LIMIT

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

   • Append

     合并子操作的结果。

   • Aggregate

     将查询行产生的结果进行组合。可以是GROUPBY、UNION、SELECT DISTINCT子句等函数的组合。

   • BitmapAnd

     位图的AND操作，通过该操作组成匹配更复杂条件的位图。

   • BitmapOr

     位图的OR操作，通过该操作组成匹配更复杂条件的位图。

   • Gather

     将并行线程的数据汇总。

   • Group

     对行进行分组，以进行GROUP BY操作。

   • GroupAggregate

     聚合GROUP BY操作的预排序行。

   • Hash

     对查询行进行散列操作，以供父查询使用。通常用于执行JOIN操作。

   • HashAggregate

     使用哈希表聚合GROUP BY的结果行。

   • Merge Append

     以保留排序顺序的方式对子查询结果进行组合，可用于组合表分区中已排序的行。

   • Recursive Union

     对递归函数的所有步骤进行并集操作。

   • SetOp

     集合运算，如INTERSECT或EXCEPT。

   • Unique

     从有序的结果集中删除重复项。

   • HashSetOp

     一种用于 INTERSECT 或 EXCEPT 等集合操作的策略，它使用 Append 来避免预排序的输入。

   • LockRows

     锁定有问题的行以阻止其他查询写入，但允许读。

   • Materialize

     将子查询的结果存储在内存里，以方便父查询快速访问获取。

   • Result

     在不进行扫描的情况下返回一个值（比如硬编码的值）。

   • WindowAgg

     窗口聚合函数，一般由OVER语句触发。

   • Merge

     归并操作。

   • StartWith Operator

     层次查询算子，用于执行递归查询操作。

   • Index Cond

     索引扫描条件。

   • Cstore Index And

     实现tid集合的交运算，和CStore Index Heap Scan搭配使用。

   • Cstore Index Or

     实现tid集合的并运算，和CStore Index Heap Scan搭配使用。

4. 其他关键字
   • Partitioned

     对具体分区的操作。

   • Partition Iterator

     分区迭代器，通常代表子查询是对分区的操作。

   • InitPlan

     非相关子计划。

   • Remote Query

     下推到数据节点上的查询语句。

   • Exec Nodes

     具体执行计划的节点。

   • Data Node Scan on

     说明语句已下推给DN执行。

执行信息

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

以如下SQL语句为例：

select count(1) from tb1;

执行EXPLAIN PERFORMANCE输出为：

图中显示执行信息分为以下7个部分

1. 以表格的形式将计划显示出来，包含有11个字段，分别是：id、operation、A-time、A-rows、E-rows、E-distinct、Peak Memory、E-memory、A-width、E-width和E-costs。其中计划类字段（id、operation以及E开头字段）的含义与执行EXPLAIN时的含义一致，详见执行计划小节中的说明。A-time、A-rows、E-distinct、Peak Memory、A-width的含义说明如下：
   • A-time：当前算子执行完成时间，一般DN上执行的算子的A-time是由[]括起来的两个值，分别表示此算子在所有DN上完成的最短时间和最长时间。
   • A-rows：表示当前算子的实际输出元组数。
   • E-distinct：表示hashjoin算子的distinct估计值。
   • Peak Memory：此算子在每个DN上执行时使用的内存峰值。
   • A-width：表示当前算子每行元组的实际宽度，仅对于重内存使用算子会显示，包括：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，其中(Vec)HashJoin计算的宽度是其右子树算子的宽度，会显示在其右子树上。
2. Predicate Information (identified by plan id)：

   这一部分主要显示的是静态信息，即在整个计划执行过程中不会变的信息，主要是一些join条件和一些filter信息。

3. Memory Information (identified by plan id)：

   这一部分显示的是整个计划中会将内存的使用情况打印出来的算子的内存使用信息，主要是Hash、Sort算子，包括算子峰值内存（peak 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] ）。

4. Targetlist Information (identified by plan id)

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

5. DataNode Information (identified by plan id)：

   这一部分会将各个算子的执行时间、CPU、buffer的使用情况全部打印出来。

6. User Define Profiling

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

7. ====== Query Summary =====：

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

• A-rows和E-rows的差异体现了优化器估算和实际执行的偏差度。一般来说，偏差越大，越可以认为优化器生成的计划越不可信，人工干预调优的必要性越大。
• A-time中的两个值偏差越大，表明此算子的计算偏斜（在不同DN上执行时间差异）越大，人工干预调优的必要性越大。
• Max Query Peak Memory经常用来估算SQL语句耗费内存，也被用来作为SQL语句调优时运行态内存参数设置的重要依据。一般会以EXPLAIN ANALYZE或EXPLAIN PERFORMANCE的输出作为进一步调优的输入。