Description

As described in Overview, EXPLAIN displays execution plans, but will not actually run SQL statements. EXPLAIN ANALYZE and EXPLAIN PERFORMANCE both will actually run SQL statements and return the execution information. This section describes execution plans and execution information in detail.

Execution Plans

The following SQL statements are used as an example.

    
         select d, avg(a::numeric(7, 2)) from t_distinct group by d;

Run the EXPLAIN command and the output is as follows.

gaussdb=#  explain select d, avg(a::numeric(7, 2)) from t_distinct group by d;
 id |                     operation                     | E-rows | E-width | E-costs 
----+---------------------------------------------------+--------+---------+---------
  1 | ->  Row Adapter                                   |     20 |      40 | 14.52
  2 |    ->  Vector Streaming (type: GATHER)            |     20 |      40 | 14.52
  3 |       ->  Vector Hash Aggregate                   |     20 |      40 | 13.59
  4 |          ->  Vector Streaming(type: REDISTRIBUTE) |     20 |       8 | 13.33
  5 |             ->  CStore Scan on t_distinct         |     20 |       8 | 13.01
(5 rows)

Interpretation of the execution plan fields (horizontal):

id: execution operator node ID
operation: name of an execution operator
An operator prefixed with Vector is a vectorized executor operator, usually used in a query containing a column-store table.

Streaming is a special operator. It has three types, which correspond to different data shuffle functions in the distributed architecture.
- Streaming (type: GATHER): A CN collects data from DNs.
- Streaming (type: REDISTRIBUTE): Data is redistributed to all the DNs based on selected columns.
- Streaming (type: BROADCAST): Data on the current DN is broadcast to other DNs.
E-rows: number of output rows estimated by each operator
E-memory: estimated memory used by each operator on a DN. Only operators executed on DNs are displayed. In certain scenarios, the memory upper limit enclosed in parentheses will be displayed following the estimated memory usage.
E-width: estimated width of an output tuple of each operator
E-costs: execution cost estimated by each operator
- E-costs is measured by the optimizer based on an overhead unit. Usually, fetching a disk page is defined as a unit. Other overhead parameters are configured based on the unit.
- The overhead of each node (specified by E-costs) includes the overheads of all its child nodes.
- Such an overhead reflects only what the optimizer is concerned about, but does not consider the time for transferring result rows to the client. Although the time may play an important role in the actual total time, it is ignored by the optimizer because it cannot be changed by modifying the plan.

Interpretation of the execution plan layers (vertical):

Layer 1: CStore Scan on t1
The table scan operator scans the table t1 using CStore Scan. At this layer, data in the table t1 is read from a buffer or disk, and then transferred to the upper-layer node for calculation.
Layer 2: Vector Hash Aggregate
Aggregation operator is used to aggregate (GROUP BY) data transferred from the lower layer.
Layer 3: Vector Streaming (type: GATHER)
The GATHER-typed Shuffle operator aggregates data from DNs to the CN.
Layer 4: Row Adapter
Storage format conversion operator is used to convert memory data from column storage to row storage for client display.

If the operator in the top layer is Data Node Scan, set enable_fast_query_shipping to off to view the detailed execution plan.

    
         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)

The execution plan will be displayed as follows.

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)

Keywords in the execution plan:

Table access modes
- Seq Scan
  Scans all rows of the table in sequence.
- Index Scan
  The optimizer uses a two-step plan: the lower-layer plan node visits an index to find the locations of rows matching the index condition, and then the upper-layer plan node actually fetches those rows from the table itself. Fetching rows separately is much more expensive than reading them sequentially, but because not all pages of the table have to be visited, this is still cheaper than a sequential scan. The upper-layer plan node sorts index-identified rows based on their physical locations before reading them. This minimizes the independent capturing overhead.
  
  If there are separate indexes on multiple columns referenced in WHERE, the optimizer might use an AND or OR combination of the indexes. However, this requires the visiting of both indexes, so it is not necessarily a win compared with the method using just one index and treating the other condition as a filter.
  
  Index scans are featured with different sorting mechanisms and can be classified into the following types.
  - Bitmap Index Scan
    Fetches data pages using a bitmap.
  - Index Scan using index_name
    Fetches table rows in index order, which makes them even more expensive to read. However, there are so few rows that the extra cost of sorting the row locations is unnecessary. This plan type is used mainly for queries fetching just a single row and queries having an ORDER BY condition that matches the index order, because no extra sorting step is needed to satisfy ORDER BY.
Table connection modes
- Nested Loop
  A nested loop is used for queries that have a smaller data set connected. In a nested loop, the foreign table drives the internal table and each row returned from the foreign table should have a matching row in the internal table. The returned result set of all queries should be less than 10,000. The table that returns a smaller subset will work as a foreign table, and indexes are recommended for connection columns of the internal table.
- (Sonic) Hash Join
  A hash join is used for large tables. The optimizer uses the join key and the smaller table among the two tables to build a hash table in memory, and then scans the larger table and probes the hash to find the rows that match the hash. Sonic and non-Sonic hash joins differ in their hash table structures, which do not affect the execution result set.
- Merge Join
  In most cases, merge join is inferior to hash join in execution performance. If the source data has been sorted, the data does not need to be sorted again when the merge join is performed. In this case, the performance of the merge join is better than that of the hash join.
Operators
- sort
  Sorts the result set.
- filter
  The EXPLAIN output shows that the WHERE clause is attached to the sequential scan plan node as a Filter condition. This means that the plan node checks the condition for each row it scans and outputs only the rows that meet the condition. The estimated number of output rows has been reduced because of the WHERE clause. However, the scan will still have to visit all 10,000 rows, as a result, the cost is not decreased. It increases a bit (by 10,000 x cpu_operator_cost) to reflect the extra CPU time spent on checking the WHERE condition.
- LIMIT
  Limits the number of output execution results. If a LIMIT condition is added, not all rows are retrieved.

Task Execution

In SQL optimization process, you can use EXPLAIN ANALYZE or EXPLAIN PERFORMANCE to check the SQL statement execution information. By comparing estimation differences between actual implementation and the optimizer, basis for service optimization is provided. EXPLAIN PERFORMANCE provides the execution information on each DN, whereas EXPLAIN ANALYZE does not.

The following SQL statement is used as an example:

select count(1) from t1;

The output of running EXPLAIN PERFORMANCE is as follows:

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)

In the above figures, the execution information is classified into the following seven parts.

The plan is displayed as a table, which contains 11 columns: id, operation, A-time, A-rows, E-rows, E-distinct, Peak Memory, E-memory, A-width, E-width, and E-costs. The definition of the plan-type columns (columns started with id, operation, or E) is the same as that of running EXPLAIN. For details, see Execution Plans. The definition of A-time, A-rows, E-distinct, Peak Memory, and A-width are as follows:
- A-time: indicates the execution completion time of the current operator. Generally, the A-time of the operator executed on DNs is two values enclosed by square brackets ([]), indicating the shortest time and longest time for completing the operator on all DNs, respectively.
- A-rows: number of actual output tuples of the operator
- E-distinct: estimated distinct value of the hash join operator
- Peak Memory: peak memory of the operator on each DN
- A-width: Current operator tuple actual width of each line. This parameter is valid only for the heavy memory operator, including: (Vec)HashJoin, (Vec)HashAgg, (Vec) HashSetOp, (Vec)Sort, and (Vec)Materialize operator. The (Vec)HashJoin calculation of width is the width of the right subtree operator and it will be displayed in the right subtree.
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.
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, operator memory, width, auto spread num, and early spilled; and spill details, including spill Time(s), inner/outer partition spill num, temp file num, spilled data volume, and written disk IO [min, max].
Targetlist Information (identified by plan id):
This part displays the target columns provided by each operator.
DataNode Information (identified by plan id):
The execution time, CPU, and buffer usage of each operator are printed in this part.
User Define Profiling:
This part displays CNs and DNs, DN and DN connection time, and some execution information in the storage layer.
====== Query Summary =====:
The total execution time and network traffic, including the maximum and minimum execution time in the initialization and end phases on each DN, initialization, execution, and time in the end phase on each CN, and the system available memory during the current statement execution, and statement estimation memory information.
- The difference between A-rows and E-rows shows the deviation between the optimizer estimation and actual execution. Generally, if the deviation is larger, the plan generated by the optimizer is more improper, and more manual intervention and optimization are required.
- If the difference of the A-time values is large, it indicates that the operator computing skew (difference between execution time on DNs) is large and that manual performance tuning is required.
- Max Query Peak Memory is often used to estimate the consumed memory of SQL statements, and is also used as an important basis for configuring a running memory parameter during SQL tuning. Generally, the output from EXPLAIN ANALYZE or EXPLAIN PERFORMANCE is provided for the input for further tuning.