0001 ---
0002 layout: global
0003 title: EXPLAIN
0004 displayTitle: EXPLAIN
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `EXPLAIN` statement is used to provide logical/physical plans for an input statement.
0025 By default, this clause provides information about a physical plan only.
0026
0027 ### Syntax
0028
0029 ```sql
0030 EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement
0031 ```
0032
0033 ### Parameters
0034
0035 * **EXTENDED**
0036
0037 Generates parsed logical plan, analyzed logical plan, optimized logical plan and physical plan.
0038 Parsed Logical plan is a unresolved plan that extracted from the query.
0039 Analyzed logical plans transforms which translates unresolvedAttribute and unresolvedRelation into fully typed objects.
0040 The optimized logical plan transforms through a set of optimization rules, resulting in the physical plan.
0041
0042 * **CODEGEN**
0043
0044 Generates code for the statement, if any and a physical plan.
0045
0046 * **COST**
0047
0048 If plan node statistics are available, generates a logical plan and the statistics.
0049
0050 * **FORMATTED**
0051
0052 Generates two sections: a physical plan outline and node details.
0053
0054 * **statement**
0055
0056 Specifies a SQL statement to be explained.
0057
0058 ### Examples
0059
0060 ```sql
0061 -- Default Output
0062 EXPLAIN select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
0063 +----------------------------------------------------+
0064 | plan|
0065 +----------------------------------------------------+
0066 | == Physical Plan ==
0067 *(2) HashAggregate(keys=[k#33], functions=[sum(cast(v#34 as bigint))])
0068 +- Exchange hashpartitioning(k#33, 200), true, [id=#59]
0069 +- *(1) HashAggregate(keys=[k#33], functions=[partial_sum(cast(v#34 as bigint))])
0070 +- *(1) LocalTableScan [k#33, v#34]
0071 |
0072 +----------------------------------------------------
0073
0074 -- Using Extended
0075 EXPLAIN EXTENDED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
0076 +----------------------------------------------------+
0077 | plan|
0078 +----------------------------------------------------+
0079 | == Parsed Logical Plan ==
0080 'Aggregate ['k], ['k, unresolvedalias('sum('v), None)]
0081 +- 'SubqueryAlias `t`
0082 +- 'UnresolvedInlineTable [k, v], [List(1, 2), List(1, 3)]
0083
0084 == Analyzed Logical Plan ==
0085 k: int, sum(v): bigint
0086 Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
0087 +- SubqueryAlias `t`
0088 +- LocalRelation [k#47, v#48]
0089
0090 == Optimized Logical Plan ==
0091 Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
0092 +- LocalRelation [k#47, v#48]
0093
0094 == Physical Plan ==
0095 *(2) HashAggregate(keys=[k#47], functions=[sum(cast(v#48 as bigint))], output=[k#47, sum(v)#50L])
0096 +- Exchange hashpartitioning(k#47, 200), true, [id=#79]
0097 +- *(1) HashAggregate(keys=[k#47], functions=[partial_sum(cast(v#48 as bigint))], output=[k#47, sum#52L])
0098 +- *(1) LocalTableScan [k#47, v#48]
0099 |
0100 +----------------------------------------------------+
0101
0102 -- Using Formatted
0103 EXPLAIN FORMATTED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
0104 +----------------------------------------------------+
0105 | plan|
0106 +----------------------------------------------------+
0107 | == Physical Plan ==
0108 * HashAggregate (4)
0109 +- Exchange (3)
0110 +- * HashAggregate (2)
0111 +- * LocalTableScan (1)
0112
0113
0114 (1) LocalTableScan [codegen id : 1]
0115 Output: [k#19, v#20]
0116
0117 (2) HashAggregate [codegen id : 1]
0118 Input: [k#19, v#20]
0119
0120 (3) Exchange
0121 Input: [k#19, sum#24L]
0122
0123 (4) HashAggregate [codegen id : 2]
0124 Input: [k#19, sum#24L]
0125 |
0126 +----------------------------------------------------+
0127 ```
