Skip to main content
Skip to main content
Edit this page

EXPLAIN Statement

Shows the execution plan of a statement.

Syntax:

Example:

EXPLAIN Types​

  • AST — Abstract syntax tree.
  • SYNTAX — Query text after AST-level optimizations.
  • QUERY TREE — Query tree after Query Tree level optimizations.
  • PLAN — Query execution plan.
  • PIPELINE — Query execution pipeline.

EXPLAIN AST​

Dump query AST. Supports all types of queries, not only SELECT.

Examples:

EXPLAIN SYNTAX​

Returns query after syntax optimizations.

Example:

EXPLAIN QUERY TREE​

Settings:

  • run_passes — Run all query tree passes before dumping the query tree. Default: 1.
  • dump_passes — Dump information about used passes before dumping the query tree. Default: 0.
  • passes — Specifies how many passes to run. If set to -1, runs all the passes. Default: -1.

Example:

EXPLAIN PLAN​

Dump query plan steps.

Settings:

  • header — Prints output header for step. Default: 0.
  • description — Prints step description. Default: 1.
  • indexes — Shows used indexes, the number of filtered parts and the number of filtered granules for every index applied. Default: 0. Supported for MergeTree tables.
  • projections — Shows all analyzed projections and their effect on part-level filtering based on projection primary key conditions. For each projection, this section includes statistics such as the number of parts, rows, marks, and ranges that were evaluated using the projection's primary key. It also shows how many data parts were skipped due to this filtering, without reading from the projection itself. Whether a projection was actually used for reading or only analyzed for filtering can be determined by the description field. Default: 0. Supported for MergeTree tables.
  • actions — Prints detailed information about step actions. Default: 0.
  • json — Prints query plan steps as a row in JSON format. Default: 0. It is recommended to use TSVRaw format to avoid unnecessary escaping.

When json=1 step names will contain an additional suffix with unique step identifier.

Example:

note

Step and query cost estimation is not supported.

When json = 1, the query plan is represented in JSON format. Every node is a dictionary that always has the keys Node Type and Plans. Node Type is a string with a step name. Plans is an array with child step descriptions. Other optional keys may be added depending on node type and settings.

Example:

With description = 1, the Description key is added to the step:

With header = 1, the Header key is added to the step as an array of columns.

Example:

With indexes = 1, the Indexes key is added. It contains an array of used indexes. Each index is described as JSON with Type key (a string MinMax, Partition, PrimaryKey or Skip) and optional keys:

  • Name — The index name (currently only used for Skip indexes).
  • Keys — The array of columns used by the index.
  • Condition — The used condition.
  • Description — The index description (currently only used for Skip indexes).
  • Parts — The number of parts before/after the index is applied.
  • Granules — The number of granules before/after the index is applied.

Example:

With projections = 1, the Projections key is added. It contains an array of analyzed projections. Each projection is described as JSON with following keys:

  • Name — The projection name.
  • Condition — The used projection primary key condition.
  • Description — The description of how the projection is used (e.g. part-level filtering).
  • Selected Parts — Number of parts selected by the projection.
  • Selected Marks — Number of marks selected.
  • Selected Ranges — Number of ranges selected.
  • Selected Rows — Number of rows selected.
  • Filtered Parts — Number of parts skipped due to part-level filtering.

Example:

With actions = 1, added keys depend on step type.

Example:

EXPLAIN PIPELINE​

Settings:

  • header — Prints header for each output port. Default: 0.
  • graph — Prints a graph described in the DOT graph description language. Default: 0.
  • compact — Prints graph in compact mode if graph setting is enabled. Default: 1.

When compact=0 and graph=1 processor names will contain an additional suffix with unique processor identifier.

Example:

EXPLAIN ESTIMATE​

Shows the estimated number of rows, marks and parts to be read from the tables while processing the query. Works with tables in the MergeTree family.

Example

Creating a table:

Query:

Result:

EXPLAIN TABLE OVERRIDE​

Shows the result of a table override on a table schema accessed through a table function. Also does some validation, throwing an exception if the override would have caused some kind of failure.

Example

Assume you have a remote MySQL table like this:

Result:

note

The validation is not complete, so a successful query does not guarantee that the override would not cause issues.