执行信息

更新时间：2025-03-13 GMT+08:00

查看PDF

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

以如下SQL语句在pretty模式下的执行结果为例：

--建表及插入数据。
gaussdb=# CREATE TABLE t1 (c1 int, c2 int);
gaussdb=# CREATE TABLE t2 (c1 int, c2 int);
gaussdb=# INSERT INTO t1 SELECT generate_series(1,10), generate_series(1,10);
gaussdb=# INSERT INTO t2 SELECT generate_series(1,10), generate_series(1,10);
gaussdb=# ANALYZE t1;
gaussdb=# ANALYZE t2;
gaussdb=# SELECT sum(t2.c1) FROM t1,t2 WHERE t1.c1 = t2.c2 GROUP BY t1.c2;

执行EXPLAIN PERFORMANCE输出为：

gaussdb=# EXPLAIN PERFORMANCE SELECT sum(t2.c1) FROM t1,t2 WHERE t1.c1 = t2.c2 GROUP BY t1.c2;
 id |                   operation                   |     A-time      | A-rows | E-rows | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs
----+-----------------------------------------------+-----------------+--------+--------+------------+----------------+----------+---------+---------+---------
  1 | ->  Streaming (type: GATHER)                  | 64.022          |     10 |     10 |            | 82KB           |          |         |      16 | 3.31
  2 |    ->  HashAggregate                          | [54.464,58.410] |     10 |     10 |            | [27KB, 29KB]   | 16MB     | [20,20] |      16 | 2.84
  3 |       ->  Streaming(type: REDISTRIBUTE)       | [54.385,58.318] |     10 |     10 |            | [36KB, 36KB]   | 2MB      |         |       8 | 2.76
  4 |          ->  Hash Join (5,7)                  | [0.471,0.906]   |     10 |     10 | 5, 5       | [8KB, 8KB]     | 1MB      |         |       8 | 2.64
  5 |             ->  Streaming(type: REDISTRIBUTE) | [0.053,0.385]   |     10 |     10 |            | [36KB, 36KB]   | 2MB      |         |       8 | 1.46
  6 |                ->  Seq Scan on public.t2      | [0.014,0.021]   |     10 |     10 |            | [13KB, 13KB]   | 1MB      |         |       8 | 1.05
  7 |             ->  Hash                          | [0.055,0.071]   |     10 |     10 |            | [293KB, 293KB] | 16MB     | [24,24] |       8 | 1.05
  8 |                ->  Seq Scan on public.t1      | [0.019,0.024]   |     10 |     10 |            | [13KB, 13KB]   | 1MB      |         |       8 | 1.05
(8 rows)

 Predicate Information (identified by plan id)
-----------------------------------------------
   4 --Hash Join (5,7)
         Hash Cond: (t2.c2 = t1.c1)
(2 rows)

                          Memory Information (identified by plan id)
-----------------------------------------------------------------------------------------------
 Coordinator Query Peak Memory:
         Query Peak Memory: 1MB
 DataNode Query Peak Memory
         datanode1 Query Peak Memory: 6MB
         datanode2 Query Peak Memory: 6MB
   1 --Streaming (type: GATHER)
         Peak Memory: 82KB, Estimate Memory: 64MB
   2 --HashAggregate
         datanode1 Peak Memory: 29KB, Estimate Memory: 16MB, Width: 20
         datanode2 Peak Memory: 27KB, Estimate Memory: 16MB, Width: 20
   3 --Streaming(type: REDISTRIBUTE)
         datanode1 Peak Memory: 36KB, Estimate Memory: 2MB
         datanode2 Peak Memory: 36KB, Estimate Memory: 2MB
         datanode1 Stream Network: 1kB, Network Poll Time: 0.000; Data Deserialize Time: 0.000
         datanode2 Stream Network: 1kB, Network Poll Time: 0.000; Data Deserialize Time: 0.000
   4 --Hash Join (5,7)
         datanode1 Peak Memory: 8KB, Estimate Memory: 1024KB
         datanode2 Peak Memory: 8KB, Estimate Memory: 1024KB
   5 --Streaming(type: REDISTRIBUTE)
         datanode1 Peak Memory: 36KB, Estimate Memory: 2MB
         datanode2 Peak Memory: 36KB, Estimate Memory: 2MB
         datanode1 Stream Network: 1kB, Network Poll Time: 0.000; Data Deserialize Time: 0.000
         datanode2 Stream Network: 1kB, Network Poll Time: 0.000; Data Deserialize Time: 0.000
   6 --Seq Scan on public.t2
         datanode1 Peak Memory: 13KB, Estimate Memory: 1024KB
         datanode2 Peak Memory: 13KB, Estimate Memory: 1024KB
   7 --Hash
         datanode1 Peak Memory: 293KB, Estimate Memory: 16MB, Width: 24
         datanode2 Peak Memory: 293KB, Estimate Memory: 16MB, Width: 24
         datanode1  Buckets: 32768  Batches: 1  Memory Usage: 1kB
         datanode2  Buckets: 32768  Batches: 1  Memory Usage: 1kB
   8 --Seq Scan on public.t1
         datanode1 Peak Memory: 13KB, Estimate Memory: 1024KB
         datanode2 Peak Memory: 13KB, Estimate Memory: 1024KB
(34 rows)

 Targetlist Information (identified by plan id)
------------------------------------------------
   1 --Streaming (type: GATHER)
         Output: (sum(t2.c1)), t1.c2
         Node/s: All datanodes
   2 --HashAggregate
         Output: sum(t2.c1), t1.c2
         Group By Key: t1.c2
   3 --Streaming(type: REDISTRIBUTE)
         Output: t1.c2, t2.c1
         Distribute Key: t1.c2
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
   4 --Hash Join (5,7)
         Output: t1.c2, t2.c1
   5 --Streaming(type: REDISTRIBUTE)
         Output: t2.c1, t2.c2
         Distribute Key: t2.c2
         Spawn on: All datanodes
         Consumer Nodes: All datanodes
   6 --Seq Scan on public.t2
         Output: t2.c1, t2.c2
         Distribute Key: t2.c1
   7 --Hash
         Output: t1.c2, t1.c1
   8 --Seq Scan on public.t1
         Output: t1.c2, t1.c1
         Distribute Key: t1.c1
(26 rows)

                      Datanode Information (identified by plan id)
-----------------------------------------------------------------------------------------
   1 --Streaming (type: GATHER)
         (actual time=57.765..64.022 rows=10 loops=1)
         (Buffers: 0)
         (CPU: ex c/r=16607284, ex row=10, ex cyc=166072848, inc cyc=166072848)
   2 --HashAggregate
         datanode1 (actual time=54.460..54.464 rows=6 loops=1)
         datanode2 (actual time=58.407..58.410 rows=4 loops=1)
         datanode1 (Buffers: 0)
         datanode2 (Buffers: 0)
         datanode1 (CPU: ex c/r=34274, ex row=6, ex cyc=205644, inc cyc=141279464)
         datanode2 (CPU: ex c/r=59030, ex row=4, ex cyc=236120, inc cyc=151512902)
   3 --Streaming(type: REDISTRIBUTE)
         datanode1 (actual time=19.021..54.385 rows=6 loops=1)
         datanode2 (actual time=14.622..58.318 rows=4 loops=1)
         datanode1 (Buffers: 0)
         datanode2 (Buffers: 0)
         datanode1 (CPU: ex c/r=23512303, ex row=6, ex cyc=141073820, inc cyc=141073820)
         datanode2 (CPU: ex c/r=37819195, ex row=4, ex cyc=151276782, inc cyc=151276782)
   4 --Hash Join (5,7)
         datanode1 (actual time=0.451..0.471 rows=6 loops=1)
         datanode2 (actual time=0.869..0.906 rows=4 loops=1)
         datanode1 (Buffers: shared hit=1)
         datanode2 (Buffers: shared hit=1)
         datanode1 (CPU: ex c/r=78319, ex row=12, ex cyc=939828, inc cyc=1220560)
         datanode2 (CPU: ex c/r=145606, ex row=8, ex cyc=1164852, inc cyc=2347916)
   5 --Streaming(type: REDISTRIBUTE)
         datanode1 (actual time=0.046..0.053 rows=6 loops=1)
         datanode2 (actual time=0.358..0.385 rows=4 loops=1)
         datanode1 (Buffers: 0)
         datanode2 (Buffers: 0)
         datanode1 (CPU: ex c/r=22796, ex row=6, ex cyc=136780, inc cyc=136780)
         datanode2 (CPU: ex c/r=249361, ex row=4, ex cyc=997444, inc cyc=997444)
   6 --Seq Scan on public.t2
         datanode1 (actual time=0.016..0.021 rows=6 loops=1)
         datanode2 (actual time=0.010..0.014 rows=4 loops=1)
         datanode1 (Buffers: shared hit=1)
         datanode2 (Buffers: shared hit=1)
         datanode1 (CPU: ex c/r=8410, ex row=6, ex cyc=50460, inc cyc=50460)
         datanode2 (CPU: ex c/r=8769, ex row=4, ex cyc=35076, inc cyc=35076)
   7 --Hash
         datanode1 (actual time=0.055..0.055 rows=6 loops=1)
         datanode2 (actual time=0.071..0.071 rows=4 loops=1)
         datanode1 (Buffers: shared hit=1)
         datanode2 (Buffers: shared hit=1)
         datanode1 (CPU: ex c/r=16183, ex row=6, ex cyc=97100, inc cyc=143952)
         datanode2 (CPU: ex c/r=32248, ex row=4, ex cyc=128992, inc cyc=185620)
   8 --Seq Scan on public.t1
         datanode1 (actual time=0.014..0.019 rows=6 loops=1)
         datanode2 (actual time=0.018..0.024 rows=4 loops=1)
         datanode1 (Buffers: shared hit=1)
         datanode2 (Buffers: shared hit=1)
         datanode1 (CPU: ex c/r=7808, ex row=6, ex cyc=46852, inc cyc=46852)
         datanode2 (CPU: ex c/r=14157, ex row=4, ex cyc=56628, inc cyc=56628)
(53 rows)

                           User Define Profiling
---------------------------------------------------------------------------
 Segment Id: 2  Track name: Datanode build connection
        datanode1 (time=0.117 total_calls=1 loops=1)
        datanode2 (time=0.161 total_calls=1 loops=1)
 Plan Node id: 1  Track name: coordinator get datanode connection
        coordinator1: (time=0.023 total_calls=1 loops=1)
 Plan Node id: 1  Track name: Coordinator check and update node definition
        coordinator1: (time=0.001 total_calls=1 loops=1)
 Plan Node id: 1  Track name: Coordinator serialize plan
        coordinator1: (time=1.289 total_calls=1 loops=1)
 Plan Node id: 1  Track name: Coordinator send query id with sync
        coordinator1: (time=0.091 total_calls=1 loops=1)
 Plan Node id: 1  Track name: Coordinator send begin command
        coordinator1: (time=0.002 total_calls=1 loops=1)
 Plan Node id: 1  Track name: Coordinator start transaction and send query
        coordinator1: (time=0.044 total_calls=1 loops=1)
 Plan Node id: 2  Track name: Datanode start up stream thread
        datanode1 (time=0.172 total_calls=1 loops=1)
        datanode2 (time=0.173 total_calls=1 loops=1)
(18 rows)

                        ====== Query Summary =====
--------------------------------------------------------------------------
 Datanode executor start time [datanode1, datanode2]: [0.451 ms,0.475 ms]
 Datanode executor run time [datanode1, datanode2]: [54.512 ms,58.450 ms]
 Datanode executor end time [datanode2, datanode1]: [0.098 ms,0.105 ms]
 Remote query poll time: 0.000 ms, Deserialze time: 0.000 ms
 System available mem: 8190361KB
 Query Max mem: 8280473KB
 Query estimated mem: 6464KB
 Coordinator executor start time: 0.285 ms
 Coordinator executor run time: 64.083 ms
 Coordinator executor end time: 0.117 ms
 Total network : 1kB
 Planner runtime: 1.242 ms
 Plan size: 4251 byte
 Query Id: 72620543991481051
 Total runtime: 64.786 ms
(15 rows)

--删除表。
gaussdb=# DROP TABLE t1,t2;

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

以表格的形式将计划显示出来，包含有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-rows：表示当前算子的实际输出元组数。
- E-distinct：表示hashjoin算子两端的distinct估计值。
- Peak Memory：此算子在执行时使用的内存峰值。
- A-width：表示当前算子每行元组的实际宽度，仅对于重内存使用算子会显示，包括：(Vec)HashJoin、(Vec)HashAgg、(Vec) HashSetOp、(Vec)Sort、(Vec)Materialize算子等，其中(Vec)HashJoin计算的宽度是其右子树算子的宽度，会显示在其右子树上。
Predicate Information (identified by plan id)：
这一部分主要显示的是静态信息，即在整个计划执行过程中不会变的信息，主要是一些join条件和一些filter信息。

其中本例中：
- 4 --Hash Join (5,7)：其中4表示id为4的算子，(5,7) 表示id为5的算子和id为7的算子做join。
- Hash Cond: (t2.c2 = t1.c1) 表示hashjoin的条件。
Memory Information (identified by plan id)：
这一部分显示的是整个计划中会将内存的使用情况打印出来的算子的内存使用信息，主要是Hash、Sort算子，包括算子峰值内存（peak memory），控制内存（control memory），估算内存使用（estimate 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：
这一部分显示的是Stream计划中CN和DN之间的线程交互情况。
====== Query Summary =====：
这一部分主要打印总的执行时间和网络流量，包括了初始化和结束阶段的最大最小执行时间，以及当前语句执行时系统可用内存、语句估算内存等信息。

父主题： 执行计划详解

上一篇：分布式计划

下一篇：算子详解

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

执行信息

意见反馈

文档内容是否对您有帮助？

文档反馈