Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Optimization/ Introduction to the SQL Execution Plan/ Description
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Description

As described in Overview, EXPLAIN displays the execution plan, 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 the execution plan and execution information in detail.

Execution Plans

The following SQL statement is used as an example:

1
 
select * from t1,t2 where t1.c1=t2.c2;

Run the EXPLAIN command and the output is as follows:

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)

Interpretation of the execution plan column (horizontal):

  • id: ID of a node corresponding to each execution operator
  • operation: name of an execution operator
    Streaming is a special operator. It implements the core data shuffle function of the distributed architecture. Streaming has three types, which correspond to different data shuffle functions in the distributed architecture:
    • Streaming (type: GATHER): The 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. This column is displayed only when the memory usage estimation function is enabled after the enable_dynamic_workload parameter is set to on and the estimated value of an operator is greater than 0.
  • 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 set 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 a very 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 level (vertical):

  1. Layer 1: Seq Scan on t2

    The table scan operator scans the table t2 using Seq Scan. At this layer, data in the table t2 is read from a buffer or disk, and then transferred to the upper-layer node for calculation.

  2. Layer 2: Hash

    Hash operator. It is used to calculate the hash value of the operator transferred from the lower layer for subsequent hash join operations.

  3. Layer 3: Seq Scan on t1

    The table scan operator scans the table t1 using Seq 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 hash join calculation.

  4. Layer 4: Hash Join

    Join operator. It is used to join data in the t1 and t2 tables using the hash join method and output the result data.

Keywords in the execution plan:

  1. Table access modes
    • Seq Scan

      Scans all rows of the table in sequence.

    • Index Scan

      The optimizer uses a two-step plan: the child plan node visits an index to find the locations of rows matching the index condition, and then the upper 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 planning 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 choose to 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 to using just one index and treating the other condition as a filter.

      The following index scans featured with different sorting mechanisms are involved:

      • Bitmap index scan

        Fetches data pages using a bitmap.

      • Index scan using index_name

        Uses simple index search, which fetches data from an index table in the sequence of index keys. This mode is commonly used when only a small amount of data needs to be fetched from a large data table or when the ORDER BY condition is used to match the index sequence to reduce the sorting time.

      • Index-Only Scan

        Scans the index that contains all required data, instead of referencing a table.

    • Bitmap Heap Scan

      Reads pages from bitmaps created by other operations and filters out the rows that do not meet the conditions. Bitmap heap scan can avoid random I/Os and accelerate read speed.

    • TID Scan

      Scans a table by a tuple ID.

    • Index Ctid Scan

      Scans a table based on the CTID index.

    • CTE Scan

      Specifies that CTE evaluates subquery operations and stores query results as a temporary table. The temporary table is scanned by the CTE Scan operator.

    • Foreign Scan

      Reads data from a remote data source.

    • Function Scan

      Obtains result sets returned by functions and returns them as the rows read from tables.

    • Sample Scan

      Queries and returns sampled data.

    • Subquery Scan

      Reads subquery results.

    • Values Scan

      Reads constants as part of the VALUES command.

    • WorkTable Scan

      Scans a work table. Data is read in the middle of an operation which is usually a recursive operation declared using WITH RECURSIVE.

  2. Table connection modes
    • Nested Loop

      A nested loop is used for queries that have a smaller dataset connected. In a nested loop join, 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 a hash join, in which rows of one table are entered into an in-memory hash table, after which the other table is scanned and the hash table is probed for matches to each row. 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, the execution performance of a merge join is lower than that of a hash join. However, if the source data has been pre-sorted and no more sorting is required during the merge join, its performance excels.

  3. Operators
    • sort

      Sorts the result set.

    • filter

      The EXPLAIN output shows the WHERE clause being applied as a filter condition attached to the Seq Scan plan node. This means that the plan node checks the condition for each row it scans, and returns only the ones 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.

    • Append

      Appends sub-operation results.

    • Aggregate

      Aggregates the results generated from querying rows. It can be an aggregation of statements such as GROUP BY, UNION, and SELECT DISTINCT.

    • BitmapAnd

      Specifies the AND operation of a bitmap, which is used to form a bitmap that matches more complex conditions.

    • BitmapOr

      Specifies the OR operation of a bitmap, which is used to form a bitmap that matches more complex conditions.

    • Gather

      Gathers data of parallel threads.

    • Group

      Groups rows to perform the GROUP BY operation.

    • GroupAggregate

      Aggregates the pre-sorted rows of the GROUP BY operation.

    • Hash

      Hashes rows for the parent query. It is usually used to perform the JOIN operation.

    • HashAggregate

      Aggregates the result rows of GROUP BY by using a hash table.

    • Merge Append

      Merges subquery results in a way that preserves the sort order. It can be used to merge sorted rows in a table partition.

    • ProjectSet

      Executes a function on the returned result set.

    • Recursive Union

      Performs a union operation on all steps of a recursive function.

    • SetOp

      Specifies a set operation, such as INTERSECT or EXCEPT.

    • Unique

      Removes duplicates from an ordered result set.

    • HashSetOp

      Specifies a strategy for set operations such as INTERSECT or EXCEPT. It uses Append to avoid pre-sorted input.

    • LockRows

      Locks problematic rows to prevent other queries from writing, but allows reading.

    • Materialize

      Stores subquery results in the memory so that the parent query can quickly access and obtain the subquery results.

    • Result

      Returns a value (such as a hard-coded value) without scanning.

    • WindowAgg

      Specifies a window aggregate function, which is generally triggered by the OVER statement.

    • Merge

      Performs a merge operation.

    • StartWith Operator

      Specifies the hierarchical query operator, which is used to perform recursive query operations.

    • Rownum

      Filters the row number in the query result. It usually appears in the ROWNUM clause.

    • Index Cond

      Specifies the index scan conditions.

    • Unpivot

      Specifies a transpose operator.

  4. Partition pruning
    • Iterations

      Specifies the number of iterations performed by the partition iteration operator on level-1 partitions. If PART is displayed, dynamic pruning is used.

      For example, Iterations: 4 indicates that the iteration operator needs to traverse four level-1 partitions. Iterations: PART indicates that the number of level-1 partitions to be traversed is determined by parameter conditions of the partition key.

    • Selected Partitions

      Specifies the selected level-1 partitions for pruning. m..n indicates that partitions m to n are selected. Multiple consecutive partitions are separated by commas (,).

      For example, Selected Partitions: 2..4,7 indicates that partitions 2, 3, 4, and 7 are selected.

  5. Other keywords
    • Partitioned

      Indicates operations on a specific partition.

    • Partition Iterator

      Partition iterator, which usually indicates that a subquery is an operation on a partition.

    • InitPlan

      Indicates a non-related subplan.

    • Remote Query

      Indicates a query statement pushed down to a DN.

    • Exec Nodes

      Indicates nodes where plans are executed.

    • Data Node Scan on

      Indicates that a statement has been pushed down to a DN for execution.

Execution Information

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)

The preceding example shows that the execution information can be classified into the following seven aspects.

  1. 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 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 operator. Generally, A-time of the operator is two values enclosed with 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: 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, 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].

  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. User Define Profiling:

    This part displays CNs and DNs, DN and DN connection time, and some execution information at the storage layer.

  7. ====== 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, the time in the initialization, execution, and end phases on each CN, the system available memory and statement estimation memory information during the current statement execution, are printed in this part.

  • 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 larger, the operator computing skew (difference between execution time on different DNs) is larger, and more manual intervention and optimization are 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 setting a running memory parameter during SQL statement optimization. Generally, the output from EXPLAIN ANALYZE or EXPLAIN PERFORMANCE is provided for the input for further optimization.