Skip to main content
Version: Latest-3.3

EXPLAIN

Description

Shows the logical or physical execution plans for a query statement. For instructions on how to analyze a query plan, refer to Plan analysis.

tip

This operation does not require privileges.

Syntax

EXPLAIN [ LOGICAL | VERBOSE | COSTS ] <query>
tip

The level of execution plan detail returned: EXPLAIN LOGICAL < EXPLAIN < EXPLAIN VERBOSE < EXPLAIN COSTS.

In most cases, using EXPLAIN is sufficient. EXPLAIN VERBOSE and EXPLAIN COSTS will print a lot of internal information, which is mainly used for debugging the execution plan.

Parameters

ParameterDescription
LOGICALShows the simple logical execution plans.
VERBOSEShows the detailed logical execution plans, including data types, nullable information, and optimization strategy.
COSTSShows the detailed logical execution plans, including column statisitics.
queryThe query statement whose execution plan you want to view.

Return value

Returns the execution plan of the input query statement.

ReturnDescription
avgRowSizeThe average size of the scanned data rows.
cardinalityThe total number of data rows in the scanned table.
colocateWhether the table is in colocate mode.
numNodesThe number of nodes to scan.
rollupThe Rollup materialized view that is used.
preaggregationThe status of preaggregation.
predicatesPredicates in the query.
column statisticsThe statictics of the column,sequence: MIN, MAX, NULL numbers,average size,and cardinality.

Examples

EXPLAIN

MySQL tpcds> explain select 
sr_customer_sk as ctr_customer_sk,
sr_store_sk as ctr_store_sk,
sum(SR_RETURN_AMT) as ctr_total_return
from
store_returns,
date_dim
where
sr_returned_date_sk = d_date_sk and d_year = 2000
group by
sr_customer_sk,
sr_store_sk
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:5: sr_customer_sk | 9: sr_store_sk | 49: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 9:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 09 |
| UNPARTITIONED |
| |
| 8:AGGREGATE (merge finalize) |
| | output: sum(49: sum) |
| | group by: 5: sr_customer_sk, 9: sr_store_sk |
| | |
| 7:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 07 |
| HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| |
| 6:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(12: sr_return_amt) |
| | group by: 5: sr_customer_sk, 9: sr_store_sk |
| | |
| 5:Project |
| | <slot 5> : 5: sr_customer_sk |
| | <slot 9> : 9: sr_store_sk |
| | <slot 12> : 12: sr_return_amt |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | colocate: false, reason: |
| | equal join conjunct: 3: sr_returned_date_sk = 21: d_date_sk |
| | |
| |----3:EXCHANGE |
| | |
| 0:OlapScanNode |
| TABLE: store_returns |
| PREAGGREGATION: ON |
| PREDICATES: 3: sr_returned_date_sk IS NOT NULL |
| partitions=1/1 |
| rollup: store_returns |
| tabletRatio=10/10 |
| tabletList=10241,10243,10245,10247,10249,10251,10253,10255,10257,10259 |
| cardinality=277502 |
| avgRowSize=20.0 |
| |
| PLAN FRAGMENT 3 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 03 |
| UNPARTITIONED |
| |
| 2:Project |
| | <slot 21> : 21: d_date_sk |
| | |
| 1:OlapScanNode |
| TABLE: date_dim |
| PREAGGREGATION: ON |
| PREDICATES: 27: d_year = 2000 |
| partitions=1/1 |
| rollup: date_dim |
| tabletRatio=5/5 |
| tabletList=11543,11545,11547,11549,11551 |
| cardinality=362 |
| avgRowSize=8.0 |
+-----------------------------------------------------------------------------+
79 rows in set

EXPLAIN LOGICAL

MySQL tpcds> explain logical select 
sr_customer_sk as ctr_customer_sk,
sr_store_sk as ctr_store_sk,
sum(SR_RETURN_AMT) as ctr_total_return
from
store_returns,
date_dim
where
sr_returned_date_sk = d_date_sk and d_year = 2000
group by
sr_customer_sk,
sr_store_sk

+---------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------------------------------------------------------+
| - Output => [5:sr_customer_sk, 9:sr_store_sk, 49:sum] |
| - AGGREGATE(GLOBAL) [5:sr_customer_sk, 9:sr_store_sk] |
| Estimates: {row: 49728, cpu: 795660.58, memory: 795660.58, network: 0.00, cost: 8405375.45} |
| 49:sum := sum(49:sum) |
| - EXCHANGE(SHUFFLE) [5, 9] |
| Estimates: {row: 49728, cpu: 79566.06, memory: 0.00, network: 0.00, cost: 6416223.99} |
| - AGGREGATE(LOCAL) [5:sr_customer_sk, 9:sr_store_sk] |
| Estimates: {row: 49728, cpu: 119349.09, memory: 79566.06, network: 0.00, cost: 6376440.96} |
| 49:sum := sum(12:sr_return_amt) |
| - HASH/INNER JOIN [3:sr_returned_date_sk = 21:d_date_sk] => [5:sr_customer_sk, 9:sr_store_sk, 12:sr_return_amt] |
| Estimates: {row: 49728, cpu: 6744977.39, memory: 1446.51, network: 0.00, cost: 6157634.30} |
| - SCAN [store_returns] => [3:sr_returned_date_sk, 5:sr_customer_sk, 9:sr_store_sk, 12:sr_return_amt] |
| Estimates: {row: 277502, cpu: 5550040.00, memory: 0.00, network: 0.00, cost: 2775020.00} |
| partitionRatio: 1/1, tabletRatio: 10/10 |
| predicate: 3:sr_returned_date_sk IS NOT NULL |
| - EXCHANGE(BROADCAST) |
| Estimates: {row: 361, cpu: 1446.51, memory: 1446.51, network: 1446.51, cost: 7232.57} |
| - SCAN [date_dim] => [21:d_date_sk] |
| Estimates: {row: 361, cpu: 2893.03, memory: 0.00, network: 0.00, cost: 1446.51} |
| partitionRatio: 1/1, tabletRatio: 5/5 |
| predicate: 27:d_year = 2000 |
+---------------------------------------------------------------------------------------------------------------------------------+
21 rows in set

EXPLAIN VERBOSE

MySQL tpcds> explain verbose select 
sr_customer_sk as ctr_customer_sk,
sr_store_sk as ctr_store_sk,
sum(SR_RETURN_AMT) as ctr_total_return
from
store_returns,
date_dim
where
sr_returned_date_sk = d_date_sk and d_year = 2000
group by
sr_customer_sk,
sr_store_sk

+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| RESOURCE GROUP: default_wg |
| |
| PLAN FRAGMENT 0(F04) |
| Fragment Cost: 0.0 |
| Output Exprs:5: sr_customer_sk | 9: sr_store_sk | 49: sum |
| Input Partition: UNPARTITIONED |
| RESULT SINK |
| |
| 9:EXCHANGE |
| cardinality: 49729 |
| |
| PLAN FRAGMENT 1(F03) |
| Fragment Cost: 2028934.4876271961 |
| |
| Input Partition: HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 09 |
| |
| 8:AGGREGATE (merge finalize) |
| | aggregate: sum[([49: sum, DECIMAL128(38,2), true]); args: DECIMAL64; result: DECIMAL128(38,2); args nullable: true; result nullable: true] |
| | group by: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| | cardinality: 49729 |
| | |
| 7:EXCHANGE |
| distribution type: SHUFFLE |
| partition exprs: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| cardinality: 49729 |
| |
| PLAN FRAGMENT 2(F00) |
| Fragment Cost: 6376440.959353934 |
| |
| Input Partition: RANDOM |
| OutPut Partition: HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| OutPut Exchange Id: 07 |
| |
| 6:AGGREGATE (update serialize) |
| | STREAMING |
| | aggregate: sum[([12: sr_return_amt, DECIMAL64(7,2), true]); args: DECIMAL64; result: DECIMAL128(38,2); args nullable: true; result nullable: true] |
| | group by: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| | cardinality: 49729 |
| | |
| 5:Project |
| | output columns: |
| | 5 <-> [5: sr_customer_sk, INT, true] |
| | 9 <-> [9: sr_store_sk, INT, true] |
| | 12 <-> [12: sr_return_amt, DECIMAL64(7,2), true] |
| | cardinality: 49729 |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | equal join conjunct: [3: sr_returned_date_sk, INT, true] = [21: d_date_sk, INT, false] |
| | build runtime filters: |
| | - filter_id = 0, build_expr = (21: d_date_sk), remote = false |
| | output columns: 5, 9, 12 |
| | can local shuffle: true |
| | cardinality: 49729 |
| | |
| |----3:EXCHANGE |
| | distribution type: BROADCAST |
| | cardinality: 362 |
| | |
| 0:OlapScanNode |
| table: store_returns, rollup: store_returns |
| preAggregation: on |
| Predicates: 3: sr_returned_date_sk IS NOT NULL |
| partitionsRatio=1/1, tabletsRatio=10/10 |
| tabletList=10241,10243,10245,10247,10249,10251,10253,10255,10257,10259 |
| actualRows=287514, avgRowSize=20.0 |
| cardinality: 277502 |
| probe runtime filters: |
| - filter_id = 0, probe_expr = (3: sr_returned_date_sk) |
| |
| PLAN FRAGMENT 3(F01) |
| Fragment Cost: 1446.5148514851485 |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 03 |
| |
| 2:Project |
| | output columns: |
| | 21 <-> [21: d_date_sk, INT, false] |
| | cardinality: 362 |
| | |
| 1:OlapScanNode |
| table: date_dim, rollup: date_dim |
| preAggregation: on |
| Predicates: [27: d_year, INT, false] = 2000 |
| partitionsRatio=1/1, tabletsRatio=5/5 |
| tabletList=11543,11545,11547,11549,11551 |
| actualRows=73049, avgRowSize=8.0 |
| cardinality: 362 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN COSTS

+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0(F04) |
| Output Exprs:5: sr_customer_sk | 9: sr_store_sk | 49: sum |
| Input Partition: UNPARTITIONED |
| RESULT SINK |
| |
| 9:EXCHANGE |
| cardinality: 49729 |
| |
| PLAN FRAGMENT 1(F03) |
| |
| Input Partition: HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 09 |
| |
| 8:AGGREGATE (merge finalize) |
| | aggregate: sum[([49: sum, DECIMAL128(38,2), true]); args: DECIMAL64; result: DECIMAL128(38,2); args nullable: true; result nullable: true] |
| | group by: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| | cardinality: 49729 |
| | column statistics: |
| | * sr_customer_sk-->[1.0, 100000.0, 2.010867271218169E-5, 4.0, 49728.786461450894] ESTIMATE |
| | * sr_store_sk-->[1.0, 10.0, 0.14285714285714285, 4.0, 6.0] ESTIMATE |
| | * sum-->[0.0, 16917.12, 0.034878301578357924, 8.0, 49728.786461450894] ESTIMATE |
| | |
| 7:EXCHANGE |
| distribution type: SHUFFLE |
| partition exprs: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| cardinality: 49729 |
| |
| PLAN FRAGMENT 2(F00) |
| |
| Input Partition: RANDOM |
| OutPut Partition: HASH_PARTITIONED: 5: sr_customer_sk, 9: sr_store_sk |
| OutPut Exchange Id: 07 |
| |
| 6:AGGREGATE (update serialize) |
| | STREAMING |
| | aggregate: sum[([12: sr_return_amt, DECIMAL64(7,2), true]); args: DECIMAL64; result: DECIMAL128(38,2); args nullable: true; result nullable: true] |
| | group by: [5: sr_customer_sk, INT, true], [9: sr_store_sk, INT, true] |
| | cardinality: 49729 |
| | column statistics: |
| | * sr_customer_sk-->[1.0, 100000.0, 2.010867271218169E-5, 4.0, 49728.786461450894] ESTIMATE |
| | * sr_store_sk-->[1.0, 10.0, 0.14285714285714285, 4.0, 6.0] ESTIMATE |
| | * sum-->[0.0, 16917.12, 0.034878301578357924, 8.0, 49728.786461450894] ESTIMATE |
| | |
| 5:Project |
| | output columns: |
| | 5 <-> [5: sr_customer_sk, INT, true] |
| | 9 <-> [9: sr_store_sk, INT, true] |
| | 12 <-> [12: sr_return_amt, DECIMAL64(7,2), true] |
| | cardinality: 49729 |
| | column statistics: |
| | * sr_customer_sk-->[1.0, 100000.0, 0.034836564480338346, 4.0, 49728.786461450894] ESTIMATE |
| | * sr_store_sk-->[1.0, 10.0, 0.034996556689413386, 4.0, 6.0] ESTIMATE |
| | * sr_return_amt-->[0.0, 16917.12, 0.034878301578357924, 8.0, 49728.786461450894] ESTIMATE |
| | |
| 4:HASH JOIN |
| | join op: INNER JOIN (BROADCAST) |
| | equal join conjunct: [3: sr_returned_date_sk, INT, true] = [21: d_date_sk, INT, false] |
| | build runtime filters: |
| | - filter_id = 0, build_expr = (21: d_date_sk), remote = false |
| | output columns: 5, 9, 12 |
| | can local shuffle: true |
| | cardinality: 49729 |
| | column statistics: |
| | * sr_returned_date_sk-->[2450820.0, 2452822.0, 0.0, 4.0, 361.6287128712871] ESTIMATE |
| | * sr_customer_sk-->[1.0, 100000.0, 0.034836564480338346, 4.0, 49728.786461450894] ESTIMATE |
| | * sr_store_sk-->[1.0, 10.0, 0.034996556689413386, 4.0, 6.0] ESTIMATE |
| | * sr_return_amt-->[0.0, 16917.12, 0.034878301578357924, 8.0, 49728.786461450894] ESTIMATE |
| | * d_date_sk-->[2450820.0, 2452822.0, 0.0, 4.0, 361.6287128712871] ESTIMATE |
| | |
| |----3:EXCHANGE |
| | distribution type: BROADCAST |
| | cardinality: 362 |
| | |
| 0:OlapScanNode |
| table: store_returns, rollup: store_returns |
| preAggregation: on |
| Predicates: 3: sr_returned_date_sk IS NOT NULL |
| partitionsRatio=1/1, tabletsRatio=10/10 |
| tabletList=10241,10243,10245,10247,10249,10251,10253,10255,10257,10259 |
| actualRows=287514, avgRowSize=20.0 |
| cardinality: 277502 |
| probe runtime filters: |
| - filter_id = 0, probe_expr = (3: sr_returned_date_sk) |
| column statistics: |
| * sr_returned_date_sk-->[2450820.0, 2452822.0, 0.0, 4.0, 2018.0] ESTIMATE |
| * sr_customer_sk-->[1.0, 100000.0, 0.034836564480338346, 4.0, 86524.0] ESTIMATE |
| * sr_store_sk-->[1.0, 10.0, 0.034996556689413386, 4.0, 6.0] ESTIMATE |
| * sr_return_amt-->[0.0, 16917.12, 0.034878301578357924, 8.0, 113968.0] ESTIMATE |
| |
| PLAN FRAGMENT 3(F01) |
| |
| Input Partition: RANDOM |
| OutPut Partition: UNPARTITIONED |
| OutPut Exchange Id: 03 |
| |
| 2:Project |
| | output columns: |
| | 21 <-> [21: d_date_sk, INT, false] |
| | cardinality: 362 |
| | column statistics: |
| | * d_date_sk-->[2415022.0, 2488070.0, 0.0, 4.0, 361.6287128712871] ESTIMATE |
| | |
| 1:OlapScanNode |
| table: date_dim, rollup: date_dim |
| preAggregation: on |
| Predicates: [27: d_year, INT, false] = 2000 |
| partitionsRatio=1/1, tabletsRatio=5/5 |
| tabletList=11543,11545,11547,11549,11551 |
| actualRows=73049, avgRowSize=8.0 |
| cardinality: 362 |
| column statistics: |
| * d_date_sk-->[2415022.0, 2488070.0, 0.0, 4.0, 361.6287128712871] ESTIMATE |
| * d_year-->[2000.0, 2000.0, 0.0, 4.0, 202.0] ESTIMATE |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
114 rows in set