EXPLAIN

Description

The EXPLAIN statement is used to provide logical/physical plans for an input statement. By default, this clause provides information about a physical plan only.

Syntax

  1. EXPLAIN [ EXTENDED | CODEGEN | COST | FORMATTED ] statement

Parameters

  • EXTENDED

    Generates parsed logical plan, analyzed logical plan, optimized logical plan and physical plan. Parsed Logical plan is a unresolved plan that extracted from the query. Analyzed logical plans transforms which translates unresolvedAttribute and unresolvedRelation into fully typed objects. The optimized logical plan transforms through a set of optimization rules, resulting in the physical plan.

  • CODEGEN

    Generates code for the statement, if any and a physical plan.

  • COST

    If plan node statistics are available, generates a logical plan and the statistics.

  • FORMATTED

    Generates two sections: a physical plan outline and node details.

  • statement

    Specifies a SQL statement to be explained.

Examples

  1. -- Default Output
  2. EXPLAIN select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
  3. +----------------------------------------------------+
  4. |                                                plan|
  5. +----------------------------------------------------+
  6. | == Physical Plan ==
  7.  *(2) HashAggregate(keys=[k#33], functions=[sum(cast(v#34 as bigint))])
  8.  +- Exchange hashpartitioning(k#33, 200), true, [id=#59]
  9.     +- *(1) HashAggregate(keys=[k#33], functions=[partial_sum(cast(v#34 as bigint))])
  10.        +- *(1) LocalTableScan [k#33, v#34]
  11. |
  12. +----------------------------------------------------
  14. -- Using Extended
  15. EXPLAIN EXTENDED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
  16. +----------------------------------------------------+
  17. |                                                plan|
  18. +----------------------------------------------------+
  19. | == Parsed Logical Plan ==
  20.  'Aggregate ['k], ['k, unresolvedalias('sum('v), None)]
  21.  +- 'SubqueryAlias `t`
  22.     +- 'UnresolvedInlineTable [k, v], [List(1, 2), List(1, 3)]
  24.  == Analyzed Logical Plan ==
  25.  k: int, sum(v): bigint
  26.  Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
  27.  +- SubqueryAlias `t`
  28.     +- LocalRelation [k#47, v#48]
  30.  == Optimized Logical Plan ==
  31.  Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
  32.  +- LocalRelation [k#47, v#48]
  34.  == Physical Plan ==
  35.  *(2) HashAggregate(keys=[k#47], functions=[sum(cast(v#48 as bigint))], output=[k#47, sum(v)#50L])
  36. +- Exchange hashpartitioning(k#47, 200), true, [id=#79]
  37.    +- *(1) HashAggregate(keys=[k#47], functions=[partial_sum(cast(v#48 as bigint))], output=[k#47, sum#52L])
  38.     +- *(1) LocalTableScan [k#47, v#48]
  39. |
  40. +----------------------------------------------------+
  42. -- Using Formatted
  43. EXPLAIN FORMATTED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
  44. +----------------------------------------------------+
  45. |                                                plan|
  46. +----------------------------------------------------+
  47. | == Physical Plan ==
  48.  * HashAggregate (4)
  49.  +- Exchange (3)
  50.     +- * HashAggregate (2)
  51.        +- * LocalTableScan (1)
  54.  (1) LocalTableScan [codegen id : 1]
  55.  Output: [k#19, v#20]
  57.  (2) HashAggregate [codegen id : 1]
  58.  Input: [k#19, v#20]
  60.  (3) Exchange
  61.  Input: [k#19, sum#24L]
  63.  (4) HashAggregate [codegen id : 2]
  64.  Input: [k#19, sum#24L]
  65. |
  66. +----------------------------------------------------+