EXPLAIN Statement

Shows the execution plan of a statement.

Syntax:

EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

Example:

EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;

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)

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.

Settings:

• graph – Prints AST as a graph described in the DOT graph description language. Default: 0.

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

EXPLAIN SYNTAX

Shows the Abstract Syntax Tree (AST) of a query after syntax analysis.

It's done by parsing the query, constructing query AST and query tree, optionally running query analyzer and optimization passes, and then converting the query tree back to the query AST.

Settings:

• oneline – Print the query in one line. Default: 0.
• run_query_tree_passes – Run query tree passes before dumping the query tree. Default: 0.
• query_tree_passes – If run_query_tree_passes is set, specifies how many passes to run. Without specifying query_tree_passes it runs all the passes.

Examples:

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c WHERE a.number = b.number AND b.number = c.number;

Output:

SELECT *
FROM system.numbers AS a, system.numbers AS b, system.numbers AS c
WHERE (a.number = b.number) AND (b.number = c.number)

With run_query_tree_passes:

EXPLAIN SYNTAX run_query_tree_passes = 1 SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c WHERE a.number = b.number AND b.number = c.number;

Output:

SELECT
    __table1.number AS `a.number`,
    __table2.number AS `b.number`,
    __table3.number AS `c.number`
FROM system.numbers AS __table1
ALL INNER JOIN system.numbers AS __table2 ON __table1.number = __table2.number
ALL INNER JOIN system.numbers AS __table3 ON __table2.number = __table3.number

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.
• dump_tree — Display the query tree. Default: 1.
• dump_ast — Display the query AST generated from the query tree. Default: 0.

Example:

EXPLAIN QUERY TREE SELECT id, value FROM test_table;

QUERY id: 0
  PROJECTION COLUMNS
    id UInt64
    value String
  PROJECTION
    LIST id: 1, nodes: 2
      COLUMN id: 2, column_name: id, result_type: UInt64, source_id: 3
      COLUMN id: 4, column_name: value, result_type: String, source_id: 3
  JOIN TREE
    TABLE id: 3, table_name: default.test_table

EXPLAIN PLAN

Dump query plan steps.

Settings:

• optimize — Controls whether query plan optimizations are applied before displaying the plan. Default: 1.
• 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. Starting from ClickHouse >= v25.9, this statement only shows reasonable output when used with SETTINGS use_query_condition_cache = 0, use_skip_indexes_on_data_read = 0.
• 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.
• sorting — Prints the sort description for each plan step that produces sorted output. Default: 0.
• keep_logical_steps — Keeps logical plan steps for joins instead of converting them to physical join implementations. Default: 0.
• json — Prints query plan steps as a row in JSON format. Default: 0. It is recommended to use TabSeparatedRaw (TSVRaw) format to avoid unnecessary escaping.
• input_headers — Prints input headers for step. Default: 0. Mostly useful only for developers to debug issues related to input-output header mismatch.
• column_structure — Prints also the structure of columns in headers on top of their name and type. Default: 0. Mostly useful only for developers to debug issues related to input-output header mismatch.
• distributed — Shows query plans executed on remote nodes for distributed tables or parallel replicas. Default: 0.
• compact — When enabled, hides expression steps and detailed action info (inputs, functions, aliases, and output positions) from the plan. Only has an effect when actions = 1. Default: 0.
• pretty — Prints the plan tree using line-drawing characters (├──, └──, │) instead of indentation to visualize the hierarchy. Also formats join step properties inline. Default: 0.

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

Example:

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",
      "Node Id": "Union_10",
      "Plans": [
        {
          "Node Type": "Expression",
          "Node Id": "Expression_13",
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Node Id": "ReadFromStorage_0"
            }
          ]
        },
        {
          "Node Type": "Expression",
          "Node Id": "Expression_16",
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Node Id": "ReadFromStorage_4"
            }
          ]
        }
      ]
    }
  }
]

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",
      "Node Id": "Expression_5",
      "Header": [
        {
          "Name": "1",
          "Type": "UInt8"
        },
        {
          "Name": "plus(2, dummy)",
          "Type": "UInt16"
        }
      ],
      "Plans": [
        {
          "Node Type": "ReadFromStorage",
          "Node Id": "ReadFromStorage_0",
          "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 Partition Min-Max, Partition, Statistics, 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 after/before the index is applied.
• Granules — The number of granules after/before the index is applied.
• Ranges — The number of granules ranges after the index is applied.

Example:

"Node Type": "ReadFromMergeTree",
"Indexes": [
  {
    "Type": "Partition Min-Max",
    "Keys": ["y"],
    "Condition": "(y in [1, +inf))",
    "Parts": 4/5,
    "Granules": 11/12
  },
  {
    "Type": "Partition",
    "Keys": ["y", "bitAnd(z, 3)"],
    "Condition": "and((bitAnd(z, 3) not in [1, 1]), and((y in [1, +inf)), (bitAnd(z, 3) not in [1, 1])))",
    "Parts": 3/4,
    "Granules": 10/11
  },
  {
    "Type": "PrimaryKey",
    "Keys": ["x", "y"],
    "Condition": "and((x in [11, +inf)), (y in [1, +inf)))",
    "Parts": 2/3,
    "Granules": 6/10,
    "Search Algorithm": "generic exclusion search"
  },
  {
    "Type": "Skip",
    "Name": "t_minmax",
    "Description": "minmax GRANULARITY 2",
    "Parts": 1/2,
    "Granules": 2/6
  },
  {
    "Type": "Skip",
    "Name": "t_set",
    "Description": "set GRANULARITY 2",
    "": 1/1,
    "Granules": 1/2
  }
]

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:

"Node Type": "ReadFromMergeTree",
"Projections": [
  {
    "Name": "region_proj",
    "Description": "Projection has been analyzed and is used for part-level filtering",
    "Condition": "(region in ['us_west', 'us_west'])",
    "Search Algorithm": "binary search",
    "Selected Parts": 3,
    "Selected Marks": 3,
    "Selected Ranges": 3,
    "Selected Rows": 3,
    "Filtered Parts": 2
  },
  {
    "Name": "user_id_proj",
    "Description": "Projection has been analyzed and is used for part-level filtering",
    "Condition": "(user_id in [107, 107])",
    "Search Algorithm": "binary search",
    "Selected Parts": 1,
    "Selected Marks": 1,
    "Selected Ranges": 1,
    "Selected Rows": 1,
    "Filtered Parts": 2
  }
]

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",
      "Node Id": "Expression_5",
      "Expression": {
        "Inputs": [
          {
            "Name": "dummy",
            "Type": "UInt8"
          }
        ],
        "Actions": [
          {
            "Node Type": "INPUT",
            "Result Type": "UInt8",
            "Result Name": "dummy",
            "Arguments": [0],
            "Removed Arguments": [0],
            "Result": 0
          },
          {
            "Node Type": "COLUMN",
            "Result Type": "UInt8",
            "Result Name": "1",
            "Column": "Const(UInt8)",
            "Arguments": [],
            "Removed Arguments": [],
            "Result": 1
          }
        ],
        "Outputs": [
          {
            "Name": "1",
            "Type": "UInt8"
          }
        ],
        "Positions": [1]
      },
      "Plans": [
        {
          "Node Type": "ReadFromStorage",
          "Node Id": "ReadFromStorage_0"
        }
      ]
    }
  }
]

With compact = 1, each Expression step is removed. Along with that, if actions = 1 is set, then Actions and Positions lines are hidden, leaving only the step descriptions:

EXPLAIN actions = 1, compact = 1 SELECT sum(number) FROM numbers(10) GROUP BY number % 4 FORMAT Raw;

Aggregating
Keys: modulo(__table1.number, 4_UInt8)
Aggregates:
    sum(__table1.number)
      Function: sum(UInt64) → UInt64
      Arguments: __table1.number
Skip merging: 0
  ReadFromSystemNumbers

With distributed = 1, the output includes not only the local query plan but also the query plans that will be executed on remote nodes. This is useful for analyzing and debugging distributed queries.

Example with distributed table:

EXPLAIN distributed=1 SELECT * FROM remote('127.0.0.{1,2}', numbers(2)) WHERE number = 1;

Union
  Expression ((Project names + (Projection + (Change column names to column identifiers + (Project names + Projection)))))
    Filter ((WHERE + Change column names to column identifiers))
      ReadFromSystemNumbers
  Expression ((Project names + (Projection + Change column names to column identifiers)))
    ReadFromRemote (Read from remote replica)
      Expression ((Project names + Projection))
        Filter ((WHERE + Change column names to column identifiers))
          ReadFromSystemNumbers

Example with parallel replicas:

SET enable_parallel_replicas = 2, max_parallel_replicas = 2, cluster_for_parallel_replicas = 'default';

EXPLAIN distributed=1 SELECT sum(number) FROM test_table GROUP BY number % 4;

Expression ((Project names + Projection))
  MergingAggregated
    Union
      Aggregating
        Expression ((Before GROUP BY + Change column names to column identifiers))
          ReadFromMergeTree (default.test_table)
      ReadFromRemoteParallelReplicas
        BlocksMarshalling
          Aggregating
            Expression ((Before GROUP BY + Change column names to column identifiers))
              ReadFromMergeTree (default.test_table)

In both examples, the query plan shows the complete execution flow including local and remote steps.

With pretty = 1, the plan tree is displayed using line-drawing characters instead of indentation, and additional information is shown for key steps:

• Query output columns are printed at the top of the plan.
• Expressions in filters, aggregation keys, sort descriptions, and window functions are displayed in human-readable SQL-like notation (e.g., a + 1 > 5 instead of greater(plus(a, 1), 5)). Internal column identifier prefixes (such as __table1.) are removed for clarity.
• Source steps (such as ReadFromMergeTree) display their output columns.
• Filter steps display the filter condition in SQL notation. When runtime join filters are present, they are shown separately.
• Aggregation steps display keys and aggregate functions with their arguments (e.g., sum(c), count()).
• IN sets from tuple literals show their values (truncated for large sets), subquery-based sets are labeled subquery1, subquery2, etc., and sets from Set engine tables show the table name.
• Join steps display the join relation using mathematical notation, estimated result row count, and which output columns come from the left vs. right side. The following symbols are used to represent different join types:

Symbol	Join Type
⋈	Inner Join
⟕	Left Join
⟖	Right Join
⟗	Full Join
⋉	Left Semi Join
⋊	Right Semi Join
⋉ with strikethrough	Left Anti Join
⋊ with strikethrough	Right Anti Join
×	Cross Join

For example, t1 ⟕ t2 means a left join between tables t1 and t2. The number in brackets after the table name (e.g., t1[100]) indicates the estimated row count when table statistics are available.

The pretty option works well together with compact = 1, which hides Expression steps and detailed action info, making the plan easier to read.

EXPLAIN pretty = 1 SELECT sum(number) FROM numbers(10) GROUP BY number % 4 FORMAT Raw;

Expression ((Project names + Projection))
└──Aggregating
   └──Expression ((Before GROUP BY + Change column names to column identifiers))
      └──ReadFromSystemNumbers

A more detailed example with joins:

CREATE TABLE t1 (id UInt64, value String) ENGINE = MergeTree ORDER BY id;
CREATE TABLE t2 (id UInt64, value String) ENGINE = MergeTree ORDER BY id;
INSERT INTO t1 SELECT number, toString(number) FROM numbers(100);
INSERT INTO t2 SELECT number, toString(number) FROM numbers(100);

EXPLAIN actions = 1, compact = 1, pretty = 1
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id FORMAT Raw;

Output: id, value, t2.id, t2.value

Join (JOIN FillRightFirst)
│  t1[100] ⋈ t2[100]
│  Type: inner | Strictness: all | Algorithm: ConcurrentHashJoin
│  Result rows: 100
│  Output:
│    Left:  id, value
│    Right: id, value
│  Join conditions: id = id
├──ReadFromMergeTree (default.t1)
│     Read type: Default
│     Parts: 1 | Granules: 1
│     Output: id, value
└──ReadFromMergeTree (default.t2)
      Read type: Default
      Parts: 1 | Granules: 1
      Output: id, value

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 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)
            NumbersRange × 2 0 → 1

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:

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;

Result:

┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ default  │ ttt   │     1 │  128 │     8 │
└──────────┴───────┴───────┴──────┴───────┘

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:

CREATE TABLE db.tbl (
    id INT PRIMARY KEY,
    created DATETIME DEFAULT now()
)

EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'tbl', 'root', 'clickhouse')
PARTITION BY toYYYYMM(assumeNotNull(created))

Result:

┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: `created` Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘

Note

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