在SQL调优过程中，经常使用explain performance，查看某个执行比较慢的SQL语句的实际执行信息和估算信息，通过对比实际执行与优化器的估算之间的差别，找到执行中的瓶颈点，来为优化提供依据。

Explain performance的结果包括七部分，分别为：

1. 以表格形式显示的计划

2. Predicate Information (identified by plan id)

3. Memory Information (identified by plan id)

4. Targetlist Information (identified by plan id)

5. DataNode Information (identified by plan id)

6. User Define Profiling

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

下面，以8个DN，数据量为1TB的GaussDB中执行tpcds的Q67为例，对这7部分分别进行详细介绍。具体的SQL语句及查询计划参考附件Q67.txt。

以表格形式显示的计划

表格字段解读：

• id：执行算子节点编号。该编号只是为了唯一标识每个算子，不代表算子的执行顺序。

• operation：具体的执行节点算子名称。常用的算子参见下面的常用算子介绍章节。

• A-time：当前算子执行完成时间，一般DN上执行的算子的A-time是由[]括起来的两个值，分别表示此算子在所有DN上完成的最短时间和最长时间。

• 一般，算子的A-time中包含其下子节点的执行时间。比如：

    Id为20号的Vector Sort算子的A-time中的第二个值，其A-time = 该算子本身的执行时间 + 21号算子的A-time，由此可以计算出来，20号算子的执行时间 = 28201.498 – 4097.214 ≈ 24000，单位为ms。

    通过这种方式，就可以帮我们找到执行时间较长的算子，即瓶颈点，分析该算子是否可以优化。比如下图示例计划中从id为16到21之间部分，如下图所示。rollup函数使用sort耗时长，总耗时达到50s（即17号-20号算子的总耗时），（并行度32的情况下），20号算子Vector sort达24s，19号算子Vector Sore Aggregate达9s，18号算子做重分布达10s，17号算子hashagg达到8s。因此需要考虑对rollup类的分析函数考虑使用其它方式进行优化，比如用hashagg替代sortagg，避免大数据量排序性能降低。

• 另外，A-time中有两个值，第一个是所有DN中执行该算子用时最短的时间，第二个是所有DN中执行该算子用时最长的时间。当这两个值相差较大时，说明存在计算倾斜。这两个值偏差越大，表明此算子的计算倾斜（在不同DN上执行时间差异）越大，人工干预调优的必要性越大。

    导致计算倾斜的原因，可能是数据在各DN上分布存在倾斜（可通过select * from table_distribution(‘schema_name’, ‘table_name’);查看表在各DN上的数据分布情况），也可能是DN之间的资源配置有差异，也可能是分布列设置不合理等。

• 对于stream算子来说，它的A-time比较特殊，stream算子主要是接收子节点的数据。它的子节点是在计划开始执行时，就启动多线程并发执行，而不是等着父节点向其请求数据时才触发执行。所以在stream的父节点向stream节点请求数据时，它的子节点如果已经准好了数据，那么stream节点的实际执行时间就是接收数据的时间，因此有时可以看到stream算子的A-time比其子节点的A-time小，如上图中的12号算子。

• A-rows：表示当前算子实际输出的全局元组数，即所有DN输出的元组数之和。

• 如果是复制表，则此列的值=表中总行数*DN数。

• 当要多次读取某个算子的执行结果，比如，nestloop中，外表中的每条数据都要扫描一遍内表的数据，即内表数据要被多次rescan，此时，A-rows显示的是多次loop之后的行数。

• E-rows：每个算子估算的输出行数。

• A-row和E-row的差异体现了优化器估算和实际执行的偏差度。一般来说，偏差越大，越可以认为优化器生成的计划越不可信，人工干预调优的必要性越大。如果估算不准，可能会导致如下的几种问题：

• 采取不正确的连接方式。比如把内表数据估计太小，可能会导致使用性能不好的nestloop方式进行连接

• 搞反内外表。如果估计的内表数量较小，外表数量较大，但实际内表数量大，外表数量小，就会导致数据量大的表作为了内表，数量小的表作为了外表。导致连接性能不好。

• 采用不正确的重分布方式。如果要被分布的数据被估计的太小，会使得计划采用boardcast方式对数据进行重分布，当实际数据很大时，会导致数据在DN之间传输耗时较多。

• 连接中选取数量大的表做重分布。下图就是一个错误选择大表做重分布的例子。

  

• E-distinct：表示单DN上hashjoin算子的distinct估计值。这一列有两个值，第一个值代表外表的distinct值，第二个值代表内表的distinct值。将来会将这两个值分别显示在内表和外表对应的行数，便于理解。

• Peak Memory：此算子在每个DN上执行时使用的内存峰值。当在SMP场景中，该列是单线程的数据。这点需要改进一下，应该显示单DN上所有线程peak memory的和比较合理，便于调优时合理设置内存。SMP的介绍参考SMP特性章节。

• E-memory：DN上每个算子估算的内存使用量，只有DN上执行的算子会显示。某些场景会在估算的内存使用量后使用括号显示该算子在内存资源充足下可以自动扩展的内存上限。

• A-width：表示当前算子每行元组的实际宽度，仅对于重内存使用算子会显示，包括：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，其中(Vec)HashJoin计算的宽度是其右子树算子的宽度，会显示在其右子树上。使用括号显示该算子输出元组中的最小和最大宽度。

• E-width：每个算子输出元组的估算宽度。

• E-costs：每个算子估算的执行代价。

Predicate Information (identified by plan id)

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

  下图中，对这部分进行了说明。

Memory Information (identified by plan id)

  这一部分显示的是整个计划中会将内存的使用情况打印出来的算子的内存使用信息，主要是Hash、Sort算子，包括：

• 算子峰值内存（peak memory）

• DataNode Query Peak Memory

  显示每个DN节点在整个查询过程中使用的内存峰值。它们中的最大值经常用来估算SQL语句耗费的内存，也被用来作为SQL语句调优时运行态内存参数设置的重要依据。

• 算子内的peak memory

  显示算子内每个DN节点使用的内存峰值。

• 控制内存（control memory）

• 一般出现在hash join、hash agg算子中，用于创建hash表和hash探测所使用的内存

• 估算的内存使用（estimate memory）

• 执行时实际宽度（width）

• 内存使用自动扩展次数（auto spread times）

• 是否提前下盘（early spilled），及下盘信息，包括重复下盘次数（spill Time(s)），内外表下盘分区数（inner/outer partition spill num），下盘文件数（temp file num），下盘数据量及最小和最大分区的下盘数据量（written disk IO [min, max] ）。提前下盘是指还有可用内存，但不足以支撑接下来的操作。主要在以下情况会发生：

• 当hash表中剩余的内存小于要插入数据申请的内存

• 根据当前操作的内存使用判断该操作是一个非常耗内存操作时。比如大数据量的sort操作。

  此时就要看一下当前配置的work_mem是否太小。



Targetlist Information (identified by plan id)

这部分显示的是每一个算子输出的目标列。格式如下：



输出的目标列一般是：

• 查询中显示指定的输出列

• 分组、排序、过滤条件、连接条件中出现的列。这些非显示指定的输出列，在查询的最后不会输出，但是在执行的中间过程中被输出给其它算子用于以上操作。比如下图中的store_sales.ss_sold_date_sk列就是只用于连接条件。

  

  对于输出列信息中还有一点需要说明的是，某些需要经过计算的表达式列，比如下图19号算子中的sum表达式列，在19号算子中每个DN计算了这个表达式的结果，在上层18号streaming算子中只是引用19号算子的结果进行重分布数据，不对其进行计算，所以在18号算子中在该sum表达式两边加了一对圆括号，表示是对下层节点中该表达式结果的引用，不再重复计算。

DataNode Information (identified by plan id)

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

  如果算子采用了SMP执行，则会详细列出DN上每个线程的情况，如下图：

  Buffer有三种类型，分别为：

• Shared buffer：用于普通表数据

• Temp buffer：用于临时文件中的数据

• Local buffer：用于临时表数据

  Buffer的使用情况包括read（从外存读入buffer数）、written（从buffer写出到外存数）、hit（数据被读入buffer后，再次被访问次数）、dirtied（buffer中内容被修改的buffer数）。其中temp buffer只会统计read和written情况。如下是一个对buffer使用情况的说明。

User Define Profiling

  这部分显示的是CN和DN、DN和DN建连的时间，以及存储层的一些执行信息。这里涉及到很多名词，此处做个简单介绍：

• 建连（build connection）是指，不同节点间（CN和DN、DN和DN）需要传输数据，而建立的连接。这一般只有stream算子才会有。

• CU（Compression Unit），压缩单元。列存表的最小存储单位。

• Vector batch，向量批。存放参与向量计算的一批数据。

  下面分别是Q67的查询计划树和user define profiling，可以通过对照计划树，便于了解user define profiling中各个plan node id对应的算子操作。

====== Query Summary =====

 这部分主要打印一些查询总结信息。包括：

• DataNode executor start time：DN上执行器的开始时间

• 第一个括号中是两个DN的名字，第一个是执行器开始时间最短的DN，第二个是执行器开始时间最长的DN

• 第二个括号中是对应第一个括号中两个DN的执行器启动时间

• Datanode executor end time: DN上执行器的结束时间

• 后面两个括号中的含义类似DN上执行器的开始时间，分别代表执行器结束时间最短和最长的DN名字，及其对应的结束用时

• Remote query poll time: stream gather算子用于监听是否有各DN数据到达CN的网络poll时间

• System available mem: 当前预计执行时系统可用内存

• Query Max mem: 查询执行所需最大内存

• Query estimated mem: 查询执行所需使用内存估计

• Avail/Max core: 可用/最大CPU核数

• Cpu util: 最大CPU数

• Active statement: 当前语句生成计划时，GaussDB(DWS)上正在运行的其它语句数

• Query estimated cpu: 计划的CPU使用估计，它是首先找出所有stream算子中cpu使用最多的cpu使用值（此处简写为max_cpu_usage），然后用所有stream算子的cpu使用之和/max_cpu_usage计算得出

• Mem allowed dop: 内存允许的dop限制。是根据DN所在主机的空闲内存情况、主机上DN个数、计划中stream个数、当前可用的CPU数及估计要使用的CPU数计算出来最大dop数。如果设置了query_dop不为0的值，则dop不能超过query_dop的限制，具体参考如何开启SMP。如果query_dop设置为自适应，则dop不能超过GaussDB(DWS)支持的最大dop（即Min non-spill dop）。

• Min non-spill dop: dop不能超过此限制。限制为64

• Initial dop: 初始dop

• Final dop: 最终dop

• Coordinator executor start time: CN上执行器的启动用时

• Coordinator executor run time: CN上执行器的运行用时

• Coordinator executor end time: CN上执行器的结束用时

• Total network : 网络通信总共传输的数据量

• Planner runtime: 生成计划的时间

• Query Id: 该查询的query id，用来唯一标识该查询，CN和DN上同一个查询的query id相同，以便于查询其他视图中标识属于同一查询的不同节点上的信息

• Total runtime: 整个查询的总用时

相关知识介绍

常用算子介绍

  算子主要分为以下五类:

• 控制算子。控制算子是一类用于处理特殊情况的节点，用于实现特殊的执行流程。

  算子类型	描述
  Result	处理含有仅需一次计算的条件表达式或INSERT语句中 VALUES子句指定的将要插入的元组
  Append	用于表示和组织需要包含多个子查询执行流程
  BitmapAnd	用于需要对两个或多个位图进行并操作的流程
  BitmapOr	用于需要对两个或多个位图进行或操作的流程
  RecursiveUnion	用于处理WITH子句中递归定义的UNION子查询

• 扫描算子。扫描类算子一般是执行计划树的叶子节点，不仅可以扫描表，还可以扫描函数的结果集、Values链表结构、子查询结果集等，每次获取一条元组作为上层节点的输入。

  算子类型	描述
  SeqScan	顺序扫描，用于行存表
  Cstore Scan	列存表扫描
  IndexScan	基于索引扫描，通过索引找到物理表中的元组
  IndexOnlyScan	基于索引扫描，直接从索引返回元组
  BitmapScan（BitmapIndexScan, BitmapHeapScan）	利用bitmap获取元组
  TidScan	通过元组tid获取元组
  SubqueryScan	子查询扫描
  FunctionScan	处理范围表中含有函数的扫描
  ValuesScan	用于Values链表扫描
  CteScan	用于扫描CommonTableExpr
  WorkTableScan	用于扫描RecursiveUnion迭代的中间数据
  ForeignScan	外部表扫描

• 物化算子。物化算子是一类可以缓存元组的算子。在执行过程中，很多扩展的物理操作符需要首先获取所有的元组才能进行操作（例如聚集函数操作、没有索 引辅助的排序等），这是要用物化算子将元组缓存起来。

  算子类型	描述
  Material	对子查询结果进行缓存。对于需要重复多次扫描的子节点，特别是扫描 结果每次都相同时，可以减少执行的代价
  Sort	对下层算子返回的元组进行排序，该算子只有左子节点
  Group	对下层排序元组进行分组操作。用于处理GROUP BY子句，分组后只返回该分组的第一个元组。该算子只有一个左子节点，且子节点必须返回在分组属性上已排好序的元组
  Agg	用于执行聚集函数
  Unique	执行去重操作
  Hash	Hashjoin辅助节点，将从Hashjoin左子节点获取的元组放入构造好的Hash表中，Hash算子也只有一个左子节点
  SetOp	处理集合操作Exist、Intersect查询
  Limit	处理Limit/offset子句，从下层节点的输出中挑选处于一定范围内的元组
  WindowAgg	处理窗口函数

• 连接算子。连接算子对应于关系代数中的连接操作。连接算子按实现方式可以分为：Nestloop、HashJoin、 MergeJoin

• 算子类型	执行方式	限制	优势	劣势	适用情况
  Nestloop	循环连接。对于外表的每一行，嵌套扫描内表返回结果	无	当内表使用索引时，可以快速定位连接元组	每个外表均需重新执行内节点操作	外表结果集小
  HashJoin	Hash连接。内表根据join列建立hash表，外表使用相同hash值仅匹配相应hash桶	连接两端必须为类型相同的 等值连接，且支持hash散列	通过哈希散列，一次性定位 连接元组	内表在内存放不下可能导致 使用多轮hash，列重复值太 多可能导致分桶	内表可在内存里放下，列重 复值和倾斜不要太多
  MergeJoin	归并连接。内外表均排序后，进行排序后的归并连接操作	（1）等值连接 （2）内外表有序（否则需要排序）	通过归并连接，一次性定位连接元组	内外表需要有序，因此必须承受内外表IndexScan或Sort的代价	内外表已有有序，不需要重新排序

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

• Streaming (type: GATHER)：作用是CN从DN收集数据。

  

• Streaming(type: REDISTRIBUTE)：作用是DN根据选定的分布列把数据重分布到所有的DN。

  

• Streaming(type: BROADCAST)：作用是把当前DN的数据广播给其他所有的DN

  

针对以上算子，如果出现了Vector前缀的算子是指向量化执行引擎算子，一般出现在含有列存表的Query中。

SMP特性

  SMP特性通过算子并行来提升性能，同时会占用更多的系统资源，包括CPU、内存、网络、I/O等等。本质上SMP是一种以资源换取时间的方式，计划并行之后必定会引起资源消耗的增加，当上述资源成为瓶颈的情况下，SMP无法提升性能，反而可能导致性能的劣化。在出现资源瓶颈的情况下，建议关闭SMP。

  在使用了SMP特性的查询计划中，可能会看到类似Streaming(type: SPLIT REDISTRIBUTE dop: 10/10)的算子，这其中的dop是degree of parallel的缩写，后面的两个数字分别表示该算子在每个DN上执行时的线程数，和其子节点执行时的线程数。

SMP适用的场景


• 支持并行的算子

  计划中存在以下算子支持并行：

• Scan：支持行存普通表和行存分区表顺序扫描、列存普通表和列存分区表顺序扫描、HDFS内外表顺序扫描；支持GDS数据导入的外表扫描并行。以上均不支持复制表。

• Join：HashJoin、NestLoop

• Agg：HashAgg、SortAgg、PlainAgg、WindowAgg(只支持partition by，不支持order by)。

• Stream：Redistribute、Broadcast

• 其他：Result、Subqueryscan、Unique、Material、Setop、Append、VectoRow、RowToVec

• SMP特有算子

  为了实现并行，新增了并行线程间的数据交换Stream算子供SMP特性使用。这些新增的算子可以看做Stream算子的子类。

• Local Gather：实现DN内部并行线程的数据汇总

• Local Redistribute：在DN内部各线程之间，按照分布键进行数据重分布

• Local Broadcast：将数据广播到DN内部的每个线程

• Local RoundRobin：在DN内部各线程之间实现数据轮询分发

• Split Redistribute：在集群跨DN的并行线程之间实现数据重分布

• Split Broadcast：将数据广播到集群所有DN的并行线程

上述新增算子可以分为Local与非Local两类，Local类算子实现了DN内部并行线程间的数据交换，而非Local类算子（即stream算子）实现了跨DN的并行线程间的数据交换。

如何开启SMP

使用SMP特性通过设置query_dop参数的值来控制。

1. query_dop=1（默认值），表示不启用SMP。

2. query_dop=0（自适应），系统会根据资源情况和计划特征，动态为每个查询选取[1,8]之间的最优的并行度，最大化提升查询性能。

3. query_dop=-value，在考虑资源情况和计划特征基础上，限制dop选取的范围为[1,value]。

4. query_dop=value，不考虑资源情况和计划特征，强制选取dop为1或value。