Updated on 2025-05-29 GMT+08:00

Execution Information

The execution result of the following SQL statement in pretty mode is used as an example:

-- Create a table and insert data into the table.
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;

The output of running EXPLAIN PERFORMANCE is as follows:

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 | A-width | E-width |     E-costs      
----+------------------------------------+--------+--------+--------+------------+-------------+---------+---------+------------------
  1 | ->  HashAggregate                  | 0.574  |      0 |    200 |            | 29KB        |         |       8 | 396.113..398.113
  2 |    ->  Hash Join (3,4)             | 0.358  |      0 |  18915 | 200, 200   | 12KB        |         |       8 | 53.763..301.538
  3 |       ->  Seq Scan on public.t1    | 0.037  |      1 |   1945 |            | 22KB        |         |       8 | 0.000..29.450
  4 |       ->  Hash                     | 0.038  |      0 |   1945 |            | 264KB       |         |       8 | 29.450..29.450
  5 |          ->  Seq Scan on public.t2 | 0.029  |     30 |   1945 |            | 22KB        |         |       8 | 0.000..29.450
(5 rows)

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

    Memory Information (identified by plan id)    
--------------------------------------------------
   1 --HashAggregate
         Peak Memory: 29KB, Estimate Memory: 64MB
   2 --Hash Join (3,4)
         Peak Memory: 12KB, Estimate Memory: 64MB
   3 --Seq Scan on public.t1
         Peak Memory: 22KB, Estimate Memory: 64MB
   4 --Hash
         Peak Memory: 264KB
  Buckets: 32768  Batches: 1  Memory Usage: 0kB
   5 --Seq Scan on public.t2
         Peak Memory: 22KB, Estimate Memory: 64MB
(11 rows)

 Targetlist Information (identified by plan id) 
------------------------------------------------
   1 --HashAggregate
         Output: sum(t2.c1), t1.c2
         Group By Key: t1.c2
   2 --Hash Join (3,4)
         Output: t1.c2, t2.c1
   3 --Seq Scan on public.t1
         Output: t1.c1, t1.c2, t1.c3
   4 --Hash
         Output: t2.c1, t2.c2
   5 --Seq Scan on public.t2
         Output: t2.c1, t2.c2
(11 rows)

                               Datanode Information (identified by plan id)                               
----------------------------------------------------------------------------------------------------------
   1 --HashAggregate
         (actual time=0.574..0.574 rows=0 loops=1)
         (Buffers: shared hit=2)
         (CPU: ex c/r=0, ex row=0, ex cyc=527797, inc cyc=8385141377087373)
   2 --Hash Join (3,4)
         (actual time=0.358..0.358 rows=0 loops=1)
         (Buffers: shared hit=2)
         (CPU: ex c/r=-8385141375712241, ex row=1, ex cyc=-8385141375712241, inc cyc=8385141376559576)
   3 --Seq Scan on public.t1
         (actual time=0.037..0.037 rows=1 loops=1)
         (Buffers: shared hit=1)
         (CPU: ex c/r=8385141375728512, ex row=1, ex cyc=8385141375728512, inc cyc=8385141375728512)
   4 --Hash
         (actual time=0.038..0.038 rows=0 loops=1)
         (Buffers: shared hit=1)
         (CPU: ex c/r=0, ex row=0, ex cyc=-251554241295571040, inc cyc=8385141376543305)
   5 --Seq Scan on public.t2
         (actual time=0.019..0.029 rows=30 loops=1)
         (Buffers: shared hit=1)
         (CPU: ex c/r=8664646089070478, ex row=30, ex cyc=259939382672114336, inc cyc=259939382672114336)
(20 rows)

       ====== Query Summary =====       
----------------------------------------
 Datanode executor start time: 0.180 ms
 Datanode executor run time: 0.590 ms
 Datanode executor end time: 0.051 ms
 Planner runtime: 0.366 ms
 Query Id: 844424930141239
 Total runtime: 0.866 ms
(6 rows)

-- Delete the example table.
gaussdb=# DROP TABLE t1,t2; 

In the preceding example, the execution information consists of the following parts:

  1. The plan is displayed as a table, which contains the following columns: id, operation, A-time, A-rows, E-rows, E-distinct, Peak Memory, A-width, E-width, and E-costs. The meanings of the plan-type columns (id, operation, or columns started with E) are the same as those when EXPLAIN is executed. For details, see Execution Plans. The definition of A-time, A-rows, E-distinct, Peak Memory, and A-width are described as follows:
    • A-time: execution completion time of the current operator.
    • A-rows: number of actual output tuples of the operator
    • E-distinct: estimated distinct value of the hash join operator
    • Peak Memory: peak memory used by the operator during execution.
    • A-width: actual tuple width in each row of the current operator. This parameter is valid only for heavy memory operators, including (Vec)HashJoin, (Vec)HashAgg, (Vec)HashSetOp, (Vec)Sort, and (Vec)Materialize. The (Vec)HashJoin calculation width is the width of its right subtree operator and will be displayed on the right subtree.
  2. Predicate Information (identified by plan id):

    This part displays the static information that does not change in the plan execution process, such as some join conditions and filter information.

  3. Memory Information (identified by plan id):

    This part displays the memory usage information printed by certain operators (mainly Hash and Sort), including peak memory, control memory, estimate memory, width, auto spread num, and early spilled, as well as spill details, including spill Time(s), inner/outer partition spill num, temp file num, spilled data volume, and written disk IO [min, max].

  4. Targetlist Information (identified by plan id):

    This part displays the target columns provided by each operator.

  5. DataNode Information (identified by plan id):

    The execution time, CPU, and buffer usage of each operator are printed in this part.

  6. ====== Query Summary =====:

    This part displays the total execution time and network traffic, including the maximum and minimum execution time in the initialization and end phases, available system memory when the current statement is executed, and estimated statement memory.