Skip to main content
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. Settings:
  • graph – Prints AST as a graph described in the DOT graph description language. Default: 0.
Examples:

​
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:
Query
Response
With run_query_tree_passes:
Query
Response

​
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 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:
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 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:
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:
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:
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:
Example with parallel replicas:
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:
SymbolJoin Type
⋈Inner Join
⟕Left Join
⟖Right Join
⟗Full Join
⋉Left Semi Join
⋊Right Semi Join
⋉ with strikethroughLeft Anti Join
⋊ with strikethroughRight 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.
A more detailed example with joins:

​
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
Query
Response

​
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:
Query
Query
Response
The validation is not complete, so a successful query does not guarantee that the override would not cause issues.
Last modified on June 19, 2026