You can execute the EXPLAIN statement to query the execution plan of SQL statements such as SELECT, DELETE, INSERT, REPLACE, and UPDATE.
Syntax
Use the following syntax to query the information about an SQL execution plan:
EXPLAIN
{LOGICALVIEW | LOGIC | SIMPLE | DETAIL | EXECUTE | PHYSICAL | OPTIMIZER | SHARDING
| COST | ANALYZE | BASELINE | JSON_PLAN | ADVISOR}
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE statement}Examples
- EXPLAIN: displays the basic information about an SQL execution plan. The execution
plan consists of operators. The information shows how an SQL statement is executed
on a compute node.
EXPLAIN select count(*) from lineitem group by L_ORDERKEY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project(count(*)="count(*)") | | HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") | | Gather(concurrent=true) | | LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") | | HitCache:false | | | TemplateId: 5819c807 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+HitCacheindicates whether the query hitsPlanCache. Valid values arefalseandtrue.TemplateIdglobally identifies an SQL execution plan. - EXPLAIN LOGICALVIEW: displays the information about the LogicalView operator in the
SQL execution plan that is pushed down to a data node.
EXPLAIN LOGICALVIEW select count(*) from lineitem group by L_ORDERKEY; +----------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------+ | Project(count(*)="count(*)") | | HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") | | Gather(concurrent=true) | | LogicalView | | MysqlAgg(group="L_ORDERKEY", count(*)="COUNT()") | | MysqlTableScan(name=[ads, lineitem]) | | HitCache:true | | Source:PLAN_CACHE | | TemplateId: 5819c807 - EXPLAIN EXECUTE: displays the information about an SQL execution plan that is pushed
down to MySQL. This statement is equivalent to the EXPLAIN statement of MySQL. You
can execute the EXPLAIN EXECUTE statement to check whether an SQL execution plan that
is pushed down to a data node uses indexes or performs full table scans.
EXPLAIN EXECUTE select count(*) from lineitem group by L_ORDERKEY; +----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+ | 1 | SIMPLE | lineitem | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 1 | 100 | Using index; Using temporary; Using filesort | +----+-------------+----------+------------+-------+---------------+---------+---------+-----+------+----------+----------------------------------------------+ 1 row in set (0.24 sec) - EXPLAIN SHARDING: displays the information about physical shards that are scanned
on a data node to handle the current query.
EXPLAIN SHARDING select count(*) from lineitem group by L_ORDERKEY; +---------------+----------------------------------+-------------+-----------+-----------+ | LOGICAL_TABLE | SHARDING | SHARD_COUNT | BROADCAST | CONDITION | +---------------+----------------------------------+-------------+-----------+-----------+ | lineitem | [000000-000003].lineitem_[00-15] | 16 | false | | +---------------+----------------------------------+-------------+-----------+-----------+ 1 row in set (0.04 sec) - EXPLAIN COST: provides more details than the EXPLAIN statement. This statement also
displays the estimated costs of each operator and the workload of the query that is
identified by the optimizer.
EXPLAIN COST select count(*) from lineitem group by L_ORDERKEY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, id = 182 | | HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, id = 180 | | Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, id = 178 | | LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, id = 109 | | HitCache:true | | Source:PLAN_CACHE | | WorkloadType: TP | | TemplateId: 5819c807 - EXPLAIN ANALYZE: provides more details than the EXPLAIN COST statement. This statement
also displays the output of each operator, such as the rowCount information.
EXPLAIN ANALYZE select count(*) from lineitem group by L_ORDERKEY; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL EXECUTIONPLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Project(count(*)="count(*)"): rowcount = 2508.0, cumulative cost = value = 2.4867663E7, cpu = 112574.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.001 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 182 | | HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))"): rowcount = 2508.0, cumulative cost = value = 2.4867662E7, cpu = 112573.0, memory = 88984.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 2506, actual memory = 0, instances = 1, id = 180 | | Gather(concurrent=true): rowcount = 2508.0, cumulative cost = value = 2.4860069E7, cpu = 105039.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.000 + 0.000, actual rowcount = 0, actual memory = 0, instances = 0, id = 178 | | LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`"): rowcount = 2508.0, cumulative cost = value = 2.4860068E7, cpu = 105038.0, memory = 29796.0, io = 201.0, net = 4.75, actual time = 0.030 + 0.025, actual rowcount = 10000, actual memory = 0, instances = 0, id = 109 | | HitCache:true | | Source:PLAN_CACHE | | TemplateId: 5819c807 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (1.08 sec) - EXPLAIN PHYSICAL: displays the execution mode of the query, the dependencies of fragments,
and the degrees of parallelism of fragments. In the following example, the execution
mode of the query is TP_LOCAL. In this mode, the query is processed by a single thread
on a single node. The execution plan consists the following fragments: Fragment-0,
Fragment-1, and Fragment-2. The system performs a pre-aggregation on each fragment
before it aggregates the data of all fragments. Therefore, the degrees of parallelism
of the fragments can be different.
EXPLAIN PHYSICAL select count(*) from lineitem group by L_ORDERKEY; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ExecutorMode: TP_LOCAL | | Fragment 0 dependency: [] parallelism: 4 | | Gather(concurrent=true) | | LogicalView(tables="[000000-000003].lineitem_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem` AS `lineitem` GROUP BY `L_ORDERKEY`") | | Fragment 1 dependency: [] parallelism: 8 | | LocalBuffer | | RemoteSource(sourceFragmentIds=[0], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*))) | | Fragment 2 dependency: [0, 1] parallelism: 8 | | Project(count(*)="count(*)") | | HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") | | RemoteSource(sourceFragmentIds=[1], type=RecordType(INTEGER L_ORDERKEY, BIGINT count(*))) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.10 sec) - EXPLAIN ADVISOR: analyzes the execution plan of the query based on the collected statistical
information, and then returns global secondary indexes that can help accelerate the
query.
EXPLAIN ADVISOR select count(*) from lineitem group by L_ORDERKEY \G; *************************** 1. row *************************** IMPROVE_VALUE: 4.4% IMPROVE_CPU: 340.8% IMPROVE_MEM: 0.0% IMPROVE_IO: 1910.0% IMPROVE_NET: 0.0% BEFORE_VALUE: 2.48676627E7 BEFORE_CPU: 112573.7 BEFORE_MEM: 88983.8 BEFORE_IO: 201 BEFORE_NET: 4.7 AFTER_VALUE: 2.38256249E7 AFTER_CPU: 25536 AFTER_MEM: 88983.8 AFTER_IO: 10 AFTER_NET: 4.7 ADVISE_INDEX: ALTER TABLE `ads`.`lineitem` ADD GLOBAL INDEX `__advise_index_gsi_lineitem_L_ORDERKEY`(`L_ORDERKEY`) DBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITION BY HASH(`L_ORDERKEY`) TBPARTITIONS 4; NEW_PLAN: Project(count(*)="count(*)") HashAgg(group="L_ORDERKEY", count(*)="SUM(count(*))") Gather(concurrent=true) IndexScan(tables="[000000-000003].lineitem__what_if_gsi_L_ORDERKEY_[00-15]", shardCount=16, sql="SELECT `L_ORDERKEY`, COUNT(*) AS `count(*)` FROM `lineitem__what_if_gsi_L_ORDERKEY` AS `lineitem__what_if_gsi_L_ORDERKEY` GROUP BY `L_ORDERKEY`") INFO: GLOBAL_INDEX 1 row in set (0.13 sec)