详解
如SQL执行计划概述节中所说，EXPLAIN会显示执行计划，但并不会实际执行SQL语句。EXPLAIN ANALYZE和EXPLAIN PERFORMANCE两者都会实际执行SQL语句并返回执行信息。在这一节将详细解释执行计划及执行信息。
执行计划
以如下SQL语句为例：
1
|
SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2; |
执行EXPLAIN的输出为：
gaussdb=# 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：具体的执行节点算子名称。
- E-rows：每个算子估算的输出行数。
- E-memory：DN上每个算子估算的内存使用量，只有DN上执行的算子会显示。某些场景会在估算的内存使用量后使用括号显示该算子在内存资源充足下可以自动扩展的内存上限。需要开启enable_dynamic_workload参数后开启内存使用量估算，且存在估算值大于0的算子时才会显示该字段。
- E-width：每个算子输出元组的估算宽度。
- E-costs：每个算子估算的执行代价。
- E-costs是优化器根据成本参数定义的单位来衡量的，习惯上以磁盘页面抓取为1个单位，其他开销参数将参照它来设置。
- 每个节点的开销（E-costs值）包括它的所有子节点的开销。
- 开销只反映了优化器关心的问题，并没有把结果行传递给客户端的时间考虑进去。虽然这个时间可能在实际的总时间里占据相当重要的分量，但是被优化器忽略了，因为它无法通过修改规划来改变。
执行计划层级解读（纵向）：
- 第一层：Seq Scan on t2
表扫描算子，用Seq Scan的方式扫描表t2。这一层的作用是把表t2的数据从buffer或者磁盘上读上来输送给上层节点参与计算。
- 第二层：Hash
- 第三层：Seq Scan on t1
表扫描算子，用Seq Scan的方式扫描表t1。这一层的作用是把表t1的数据从buffer或者磁盘上读上来输送给上层节点参与hash join计算。
- 第四层：Hash Join
最顶层算子为Data Node Scan时，需要设置enable_fast_query_shipping为off才能看到具体的执行计划，如下计划：
1 2 3 4 5 6 |
gaussdb=# explain select c1,count(1) from t1 group by c1; QUERY PLAN -------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes (2 rows) |
设置enable_fast_query_shipping参数之后，执行计划显示如下：
gaussdb=# set enable_fast_query_shipping=off; SET gaussdb=# explain select c1,count(1) from t1 group by c1; id | operation | E-rows | E-width | E-costs ----+------------------------------+--------+---------+--------- 1 | -> Streaming (type: GATHER) | 20 | 12 | 14.23 2 | -> HashAggregate | 20 | 12 | 13.30 3 | -> Seq Scan on t1 | 20 | 4 | 13.13 (3 rows)
执行计划中的主要关键字说明：
- 表访问方式
- Seq Scan
- Index Scan
优化器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取开销高很多，但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是，上层规划节点在读取索引标识出来的行位置之前，会先将它们按照物理位置排序，这样可以最小化独立抓取的开销。
如果在WHERE语句中的存在多个字段上都有索引，那么优化器可能会使用索引的AND或OR的组合。
索引扫描可以分为以下几类，差异在于索引的排序机制。
- Bitmap Heap Scan
- TID Scan
- Index Ctid Scan
- CTE Scan
- Foreign Scan
- Function Scan
- Sample Scan
- Subquery Scan
- Values Scan
- WorkTable Scan
- 表连接方式
- Nested Loop
嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。
- (Sonic) Hash Join
哈希连接，适用于数据量大的表连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。
- Merge Join
归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行归并连接时，并不需要再排序，此时归并连接的性能优于哈希连接。
- Nested Loop
- 运算符
- sort
- filter
EXPLAIN输出显示WHERE子句当作一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。因为有WHERE子句，预计的输出行数降低了。不过，扫描仍将必须访问所有 10000 行，因此开销没有降低，实际上还增加了（确切的说，通过10000 * cpu_operator_cost）以反映检查WHERE条件的额外CPU时间。
- LIMIT
- Append
- Aggregate
- BitmapAnd
- BitmapOr
- Gather
- Group
- GroupAggregate
- Hash
- HashAggregate
- Merge Append
- ProjectSet
- Recursive Union
- SetOp
- Unique
-
一种用于INTERSECT或EXCEPT等集合操作的策略，使用Append来避免预排序的输入。
- LockRows
- Materialize
- Result
- WindowAgg
- Merge
- StartWith Operator
- Rownum
- Index Cond
- Unpivot
- 分区剪枝相关信息
- 其他关键字
执行信息
在SQL调优过程中经常需要执行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看SQL语句实际执行信息，通过对比实际执行与优化器估算之间的差别来为优化提供依据。EXPLAIN PERFORMANCE相对于EXPLAIN ANALYZE增加了每个DN上的执行信息。
以如下SQL语句为例：
select count(1) from t1;
执行EXPLAIN PERFORMANCE输出为：
gaussdb=# explain performance select count(1) from t1;
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+------------------------------------+---------------+--------+--------+------------+--------------+----------+---------+---------+---------
1 | -> Aggregate | 9.326 | 1 | 1 | | 14KB | | | 8 | 209.10
2 | -> Streaming (type: GATHER) | 9.281 | 2 | 2 | | 80KB | | | 8 | 209.10
3 | -> Aggregate | [5.981,6.491] | 2 | 2 | | [13KB, 13KB] | 1MB | | 8 | 209.01
4 | -> Seq Scan on public.t1 | [2.553,2.909] | 20000 | 20000 | | [15KB, 15KB] | 1MB | | 0 | 184.00
(4 rows)
Memory Information (identified by plan id)
--------------------------------------------------------------
Coordinator Query Peak Memory:
Query Peak Memory: 0MB
DataNode Query Peak Memory
datanode1 Query Peak Memory: 2MB
datanode2 Query Peak Memory: 0MB
1 --Aggregate
Peak Memory: 14KB, Estimate Memory: 64MB
2 --Streaming (type: GATHER)
Peak Memory: 80KB, Estimate Memory: 64MB
3 --Aggregate
datanode1 Peak Memory: 13KB, Estimate Memory: 1024KB
datanode2 Peak Memory: 13KB, Estimate Memory: 1024KB
4 --Seq Scan on public.t1
datanode1 Peak Memory: 15KB, Estimate Memory: 1024KB
datanode2 Peak Memory: 15KB, Estimate Memory: 1024KB
(15 rows)
Targetlist Information (identified by plan id)
------------------------------------------------
1 --Aggregate
Output: count((count(1)))
2 --Streaming (type: GATHER)
Output: (count(1))
Node/s: All datanodes
3 --Aggregate
Output: count(1)
4 --Seq Scan on public.t1
Output: c1, c2, c3, c4, c5
Distribute Key: c1
(10 rows)
Datanode Information (identified by plan id)
------------------------------------------------------------------------------------------------------------------------------
1 --Aggregate
(actual time=9.326..9.326 rows=1 loops=1)
(Buffers: 0)
(CPU: ex c/r=-17813058098842432, ex row=2, ex cyc=-35626116197684864, inc cyc=71252232399791904)
2 --Streaming (type: GATHER)
(actual time=8.628..9.281 rows=2 loops=1)
(Buffers: 0)
(CPU: ex c/r=53439174298738384, ex row=2, ex cyc=106878348597476768, inc cyc=106878348597476768)
3 --Aggregate
datanode1 (actual time=5.980..5.981 rows=1 loops=1)
datanode2 (actual time=6.491..6.491 rows=1 loops=1)
datanode1 (Buffers: shared hit=85)
datanode2 (Buffers: shared hit=84)
datanode1 (CPU: ex c/r=-35622581151734248, ex row=10078, ex cyc=-359004372847177760768, inc cyc=71252232395610160)
datanode2 (CPU: ex c/r=-35622525572390744, ex row=9922, ex cyc=-353446698729260974080, inc cyc=71252232398542704)
4 --Seq Scan on public.t1
datanode1 (actual time=0.018..2.553 rows=10078 loops=1)
datanode2 (actual time=0.017..2.909 rows=9922 loops=1)
datanode1 (Buffers: shared hit=85)
datanode2 (Buffers: shared hit=84)
datanode1 (CPU: ex c/r=35629651228376004, ex row=10078, ex cyc=359075625079573381120, inc cyc=359075625079573381120)
datanode2 (CPU: ex c/r=35629706809278324, ex row=9922, ex cyc=353517950961659543552, inc cyc=353517950961659543552)
(22 rows)
User Define Profiling
---------------------------------------------------------------------------
Plan Node id: 2 Track name: coordinator get datanode connection
coordinator1: (time=0.019 total_calls=1 loops=1)
Plan Node id: 2 Track name: Coordinator serialize plan
coordinator1: (time=1.059 total_calls=1 loops=1)
Plan Node id: 2 Track name: Coordinator send begin command
coordinator1: (time=0.003 total_calls=1 loops=1)
Plan Node id: 2 Track name: Coordinator start transaction and send query
coordinator1: (time=0.045 total_calls=1 loops=1)
(8 rows)
====== Query Summary =====
--------------------------------------------------------------------------
Datanode executor start time [datanode1, datanode2]: [0.421 ms,0.450 ms]
Datanode executor run time [datanode1, datanode2]: [6.002 ms,6.528 ms]
Datanode executor end time [datanode2, datanode1]: [0.027 ms,0.028 ms]
Remote query poll time: 0.000 ms, Deserialze time: 0.000 ms
System available mem: 8222310KB
Query Max mem: 8310784KB
Query estimated mem: 2048KB
Coordinator executor start time: 0.181 ms
Coordinator executor run time: 9.340 ms
Coordinator executor end time: 0.052 ms
Planner runtime: 0.421 ms
Plan size: 3122 byte
Query Id: 72339069014648468
Total runtime: 9.657 ms
(14 rows)
上述示例中显示执行信息分为以下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)：
- 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)：
- User Define Profiling
- ====== Query Summary =====：
这一部分主要打印总的执行时间和网络流量，包括了各个DN上初始化和结束阶段的最大最小执行时间、CN上的初始化、执行、结束阶段的时间，以及当前语句执行时系统可用内存、语句估算内存等信息。
- A-rows和E-rows的差异体现了优化器估算和实际执行的偏差度。一般来说，偏差越大，优化器生成的计划越不可信，人工干预调优的必要性越大。
- A-time中的两个值偏差越大，表明此算子的计算偏斜（在不同DN上执行时间差异）越大，人工干预调优的必要性越大。
- Max Query Peak Memory经常用来估算SQL语句耗费内存，也被用来作为SQL语句调优时运行态内存参数设置的重要依据。一般会以EXPLAIN ANALYZE或EXPLAIN PERFORMANCE的输出作为进一步调优的输入。
