EXPLAIN Statement

Syntax:

Example:

Union
  Expression (Projection)
    Expression (Before ORDER BY and SELECT)
      Aggregating
        Expression (Before GROUP BY)
          SettingQuotaAndLimits (Set limits and quota after reading from storage)
            ReadFromStorage (SystemNumbers)
  Expression (Projection)
    MergingSorted (Merge sorted streams for ORDER BY)
      MergeSorting (Merge sorted blocks for ORDER BY)
        PartialSorting (Sort each block for ORDER BY)
          Expression (Before ORDER BY and SELECT)
            Aggregating
              Expression (Before GROUP BY)
                SettingQuotaAndLimits (Set limits and quota after reading from storage)
                  ReadFromStorage (SystemNumbers)

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

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

Examples:

EXPLAIN AST SELECT 1;

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Literal UInt64_1

EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();

  explain
  AlterQuery  t1 (children 1)
   ExpressionList (children 1)
    AlterCommand 27 (children 1)
     Function equals (children 1)
      ExpressionList (children 2)
       Identifier date
       Function today (children 1)
        ExpressionList

Returns query after syntax optimizations.

Example:

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;

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.
• actions — Prints detailed information about step actions. Default: 0.
• json — Prints query plan steps as a row in format. Default: 0. It is recommended to use TSVRaw format to avoid unnecessary escaping.

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;

Union
  Expression (Projection)
  Expression (Before ORDER BY and SELECT)
    Aggregating
      Expression (Before GROUP BY)
        SettingQuotaAndLimits (Set limits and quota after reading from storage)
          ReadFromStorage (SystemNumbers)

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:

EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

[
  {
    "Plan": {
      "Node Type": "Union",
      "Plans": [
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
        },
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

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

{
  "Node Type": "ReadFromStorage",
  "Description": "SystemOne"
}

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

Example:

EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;

[
  {
    "Plan": {
      "Node Type": "Expression",
      "Header": [
        {
          "Name": "1",
          "Type": "UInt8"
        },
        {
          "Name": "plus(2, dummy)",
          "Type": "UInt16"
        }
      ],
      "Plans": [
        {
          "Node Type": "SettingQuotaAndLimits",
          "Header": [
            {
              "Name": "dummy",
              "Type": "UInt8"
            }
          ],
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Header": [
                {
                  "Name": "dummy",
                  "Type": "UInt8"
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

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:

• — An index name (for now, is used only for Skip index).
• Keys — An array of columns used by the index.
• Description — An index (for now, is used only for Skip index).
• Initial Parts — A number of parts before the index is applied.
• Selected Parts — A number of parts after the index is applied.
• Initial Granules — A number of granules before the index is applied.
• Selected Granulesis — A number of granules after the index is applied.

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

Example:

EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;

[
  {
    "Plan": {
      "Node Type": "Expression",
      "Expression": {
        "Inputs": [],
        "Actions": [
          {
            "Node Type": "Column",
            "Result Type": "UInt8",
            "Result Type": "Column",
            "Column": "Const(UInt8)",
            "Arguments": [],
            "Removed Arguments": [],
            "Result": 0
          }
        ],
        "Outputs": [
          {
            "Name": "1",
            "Type": "UInt8"
          }
        ],
        "Positions": [0],
        "Project Input": true
      },
      "Plans": [
        {
          "Node Type": "SettingQuotaAndLimits",
          "Plans": [
            {
              "Node Type": "ReadFromStorage"
            }
          ]
        }
      ]
    }
  }
]

Settings:

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

Example:

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

(Union)
(Expression)
ExpressionTransform
  (Expression)
  ExpressionTransform
    (Aggregating)
    Resize 2 → 1
      AggregatingTransform × 2
        (Expression)
        ExpressionTransform × 2
          (SettingQuotaAndLimits)
            (ReadFromStorage)
            NumbersMt × 2 0 → 1

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:

CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
INSERT INTO ttt SELECT number FROM numbers(128);
OPTIMIZE TABLE ttt;

Query:

EXPLAIN ESTIMATE SELECT * FROM ttt;

┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
└──────────┴───────┴───────┴──────┴───────┘