1 问题现象

SQL执行计划是GaussDB性能分析及调优的核心，它输出三个关键信息：

• 访问路径:扫描表数据的路径。
• 连接顺序：多表连接顺序。
• 连接方式：多表连接方式。

2 技术背景

GaussDB SQL语句执行计划是数据库为运行SQL语句而执行的操作步骤序列。它显示了数据库如何访问数据，包括表的扫描方式、索引的使用、连接方法等。通过执行计划，我们可以了解SQL语句引用表的顺序、SQL语句中每个表的访问方法、针对SQL语句中连接操作相关表的连接方法，where条件过滤、order排序及count聚合等操作，从而找到性能瓶颈并进行优化。

通过分析执行计划，可以识别出性能瓶颈，如全表扫描、索引使用不当等，并采取相应的优化措施，比如创建或调整索引、重写查询语句等，执行计划是SQL性能分析及调优的核心，可以帮助开发者和数据库管理员提升查询效率和系统性能。

2.1 用explain查看执行计划

GaussDB可以使用EXPLAIN命令可以查看优化器为每个查询生成的具体执行计划。

explain select 投影列 FROM 表名 WHERE 条件;

2.1.1 explain分析示例

EXPLAIN给每个执行节点都输出一行，显示基本的节点类型和优化器为执行这个节点预计的开销值。

• 最底层节点是表扫描节点，它扫描表并返回原始数据行。不同的表访问模式有不同的扫描节点类型：顺序扫描、索引扫描等。最底层节点的扫描对象也可能是非表行数据（不是直接从表中读取的数据），如VALUES子句和返回行集的函数，它们有自己的扫描节点类型。
•  如果查询需要连接、聚集、排序或者对原始行做其他操作，那么就会在扫描节点上添加其他节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的执行节点类型。
• 第一行(最上层节点)是执行计划总执行开销的预计。这个数值就是优化器试图最小化的数值。

2.1.2 语法格式与选项

1) 显示SQL语句执行计划格式:

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

2) 其中选项option子句的语法：

ANALYZE [ boolean ] -- 执行语句，并显示实际运行时间和其他统计数据
| PERFORMANCE [ Boolean ] -- 执行语句，并显示性能开销
| VERBOSE [ Boolean ] -- 显示计划额外信息
| COSTS [ Boolean ] -- 显示代价
| CPU [ boolean ] -- 显示cpu使用
| DETAIL [ boolean ] -- 打印节点信息
| NODES [ boolean ] -- 显示执行节点
| NUM_NODES [ boolean ] -- 显示节点数量
| BUFFERS [ boolean ] -- 显示buffer使用
| TIMING [ boolean ] -- 显示耗时
| PLAN [ boolean ] -- 显示计划
| FORMAT { TEXT | XML | JSON | YAML }

3) 参数说明

• statement

指定要分析的SQL语句。

• ANALYZE boolean

显示实际运行时间和其他统计数据。当两个参数同时使用时，在option中排在后面的一个生效。

取值范围：

○ TRUE（缺省值）：显示实际运行时间和其他统计数据。

○ FALSE：不显示。

•  VERBOSE boolean

显示有关计划的额外信息。

取值范围：

○ TRUE（缺省值）：显示额外信息。

○ FALSE：不显示。

• COSTS boolean

包括每个规划节点的估计总成本，以及估计的行数和每行的宽度。

取值范围：

○ TRUE（缺省值）：显示估计总成本和宽度。

○ FALSE：不显示。

• CPU boolean

打印CPU的使用情况的信息。需要结合ANALYZE选项一起使用。

取值范围：

○ TRUE（缺省值）：显示CPU的使用情况。

○ FALSE：不显示。

•  DETAIL boolean

打印数据库节点上的信息。需要结合ANALYZE选项一起使用。

取值范围：

○ TRUE（缺省值）：打印数据库节点的信息。

○ FALSE：不打印。

• NODES boolean（仅分布式模式可用，集中式模式不可用）

打印query执行的节点信息。

取值范围：

○ TRUE（缺省值）：打印执行的节点的信息。

○ FALSE：不打印。

• NUM_NODES boolean（仅分布式模式可用，集中式模式不可用）

打印执行中的节点的个数信息。

取值范围：

○ TRUE（缺省值）：打印数据库节点个数的信息。

○ FALSE：不打印。

•  BUFFERS boolean

包括缓冲区的使用情况的信息。需要结合ANALYZE选项一起使用。

取值范围：

○ TRUE：显示缓冲区的使用情况。

○ FALSE（缺省值）：不显示。

•  TIMING boolean

包括实际的启动时间和花费在输出节点上的时间信息。需要结合ANALYZE选项一起使用。

取值范围：

○ TRUE（缺省值）：显示启动时间和花费在输出节点上的时间信息。

○ FALSE：不显示。

•  PLAN

是否将执行计划存储在plan_table中。当该选项开启时，会将执行计划存储在plan_table中，不打印到当前屏幕，因此该选项为on时，不能与其他选项同时使用。

取值范围：

○ ON（缺省值）：将执行计划存储在plan_table中，不打印到当前屏幕。执行成功返回EXPLAIN SUCCESS。

○ OFF：不存储执行计划，将执行计划打印到当前屏幕。

•  BLOCKNAME boolean

是否显示计划的每个操作所处于的查询块。当该选项开启时，会将每个操作所处于的查询块的名字输出在Query Block列上，方便用户获取查询块名字，并使用Hint修改执行计划：

○ TRUE（缺省值）：显示计划时，将每个操作所处于的查询块的名字输出在新增列Query Block列上。该选项需要在pretty模式下使用。

○ FALSE：不对计划显示产生影响。

•  OUTLINE boolean

是否显示计划的Outline Hint信息。

○ ON：显示计划时，将Outline Hint显示在计划下方 。

○ OFF（缺省值）：不显示计划的Outline Hint信息。

• ADAPTCOST boolean

在Normal模式下是否显示计划的基数估计方式信息。

○ ON（缺省值）：Normal模式下，在计划节点上展示基数估计的方式，包含默认方式和反馈方式，不对预备语句生效。

○ OFF：不展示基数估计的方式信息。

•  FORMAT

指定输出格式。

取值范围：TEXT，XML，JSON和YAML。

默认值：TEXT。

• PERFORMANCE

使用此选项时，即打印执行中的所有相关信息。下述为部分信息描述：

○ ex c/r：代表平均每行使用cpu周期数，等于（ex cyc）/（ex row）。

○ ex row：执行行数。

○ ex cyc：代表使用的cpu周期数。

○ inc cyc：代表包含子节点使用的总cpu周期数。

○ shared hit：代表算子的share buffer命中情况。

○ loops：算子循环执行次数。

○ total_calls：生成元素总数。

○ remote query poll time stream gather：算子用于侦听各DN数据到达CN的网络poll时间。

○ deserialize time：反序列化所需时间。

○ estimated time：估计时间。

• OPTEVAL boolean

是否显示SCAN算子（当前仅支持seqscan、indexscan、indexonlyscan、bitmapheapscan）的代价淘汰明细，当开启此开关的时候，会在执行计划中显示一个名字为Cost Evaluation Info (identified by plan id)的计划块，该选项仅仅可以和COSTS、VERBOSE、FORMAT三个选项共存。

取值范围：

○ TRUE：显示SCAN算子的代价淘汰明细。

○ FALSE（缺省值）：不显示。

实际查看执行计划时，这些参数根据实际需要进行增加，绝大部分场景下使用EXPLAIN ANALYZE statement就行。

2.1.3 执行计划显示格式

GaussDB对执行计划提供了normal、pretty、summary、run四种显示格式：

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

pretty格式执行计划示例：

通过设置GUC参数explain_perf_mode，可以显示不同格式的执行计划。pretty格式是我们通常使用的。

2.1.4 查看执行计划

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

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

以如下SQL语句为例：

SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;

执行EXPLAIN的输出为：

2.1.5 执行计划字段解读

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

• 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上执行的算子会显示。

某些场景会在估算的内存使用量后使用括号显示该算子在内存资源充足下可以自动扩展的内存上限。需要开启enable_dynamic_workload参数后开启内存使用量估算，且存在估算值大于0的算子时才会显示该字段。

• E-width：每个算子输出元组的估算宽度。
•  E-costs：每个算子估算的执行代价。

○ E-costs是优化器根据成本参数定义的单位来衡量的，习惯上以磁盘页面抓取为1个单位，其他开销参数将参照它来设置。

○ 每个节点的开销（E-costs值）包括它的所有子节点的开销。

○ 开销只反映了优化器关心的问题，并没有把结果行传递给客户端的时间考虑进去。虽然这个时间可能在实际的总时间里占据相当重要的分量，但是被优化器忽略了，因为它无法通过修改规划来改变。

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

• 第一层：Seq Scan on t2

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

• 第二层：Hash

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

•  第三层：Seq Scan on t1

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

• 第四层：Hash Join

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

说明：

最顶层算子为Data Node Scan时，需要设置enable_fast_query_shipping为off才能看到具体的执行计划，如下计划：

设置enable_fast_query_shipping参数之后，执行计划显示如下：

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

a)表访问方式

• 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声明的递归操作。

b) 表连接方式

•  Nested Loop

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

• (Sonic) Hash Join

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

• Merge Join

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

c) 运算符

•  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

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

•  ProjectSet

对返回的结果集执行函数。

•  Recursive Union

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

• SetOp

集合运算，如INTERSECT或EXCEPT。

•  Unique

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

• HashSetOp

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

•  LockRows

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

•  Materialize

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

•  Result

在不进行扫描的情况下返回一个值。

• WindowAgg

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

•  Merge

归并操作。

• StartWith Operator

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

•  Rownum

对查询结果的行编号进行条件过滤。通常出现在rownum子句里。

•  Index Cond

索引扫描条件。

•  Unpivot

转置算子。

d) 分区剪枝相关信息

•  Iterations

分区迭代算子对一级分区的迭代次数。如果显示PART则为动态剪枝场景。

例如：Iterations：4表示迭代算子需要遍历4个一级分区。Iterations：PART表示遍历一级分区个数需要由分区键上的参数条件决定。

•  Selected Partitions

一级分区剪枝的结果，m..n表示m到n号分区被剪枝选中，多个不连续的分区由逗号连接。

例如：Selected Partitions: 2..4,7表示2、3、4、7四个分区被选中。

e) 其他关键字

•  Partitioned

对具体分区的操作。

• Partition Iterator

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

• InitPlan

非相关子计划。

• Remote Query

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

•  Exec Nodes

具体执行计划的节点。

•  Data Node Scan on

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

f)  执行信息

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

以如下SQL语句为例：

select count(*) from t1;

执行EXPLAIN PERFORMANCE输出为：

上述示例中显示执行信息分为以下7个部分：

•  计划显示

以表格的形式将计划显示出来，包含有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计算的宽度是其右子树算子的宽度，会显示在其右子树上。

•  Predicate Information (identified by plan id)：

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

•  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] ）。

• Targetlist Information (identified by plan id)

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

• DataNode Information (identified by plan id)

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

• User Define Profiling

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

• ====== Query Summary =====

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

说明：

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

2.2 explain使用事项

在指定ANALYZE选项时，语句会被执行。如果用户想使用EXPLAIN分析INSERT，UPDATE，DELETE，CREATE TABLE AS或EXECUTE语句，而不想改动数据（执行这些语句会影响数据），请使用如下方法。

START TRANSACTION;
EXPLAIN ANALYZE ...;
ROLLBACK;

3 总结

使用EXPLAIN命令可以查看优化器为每个查询生成的具体执行计划。EXPLAIN给每个执行节点都输出一行，显示基本的节点类型和关注优化器为执行这个节点预计的开销值。