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.
  • ANALYZE — Executes the query and annotates the execution plan with measured runtime metrics.
  • ESTIMATE — Estimated number of rows, marks and parts to be read from the tables while processing the query.
  • TABLE OVERRIDE — Validated result of a table override on a table-function schema.

​
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: 1.
  • 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. Not supported together with json. 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: 1.
  • pretty — Prints the plan tree using line-drawing characters (├──, └──, │) instead of indentation to visualize the hierarchy. Also formats join step properties inline. Default: 1.
By default, explain_query_plan_default = 'pretty', so actions, compact, and pretty are initialized to 1 and the plan is rendered in the compact, pretty, action-annotated form. Specifying any of these options explicitly in the EXPLAIN statement (for example, EXPLAIN actions = 0, compact = 0, pretty = 0 SELECT ...) always overrides the default.Prior to ClickHouse 26.7 the defaults for actions, compact, and pretty were 0. You can still get that output by setting explain_query_plan_default = 'legacy' (globally or in per-query SETTINGS), or by setting compatibility to any version older than 26.7.The json and distributed options do not enable the pretty defaults (actions, compact, and pretty), even when explain_query_plan_default = 'pretty'. To include action details in their output, set actions = 1 manually.
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, Node Id, and Plans. Node Type is a string with the step name, and Node Id is a unique step identifier (the step name with a numeric suffix, e.g. Union_10). 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 = 0 and actions = 1, the Expression steps can be seen along with detailed information about expressions:
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.
distributed is rendered only in the legacy (non-pretty) form, because the pretty output does not integrate the remote shard plans into the plan tree. For this reason, enabling distributed automatically disables the pretty defaults (actions, compact, and pretty), regardless of explain_query_plan_default. You can still set actions=1 manually. The distributed option is also not supported together with json.
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:
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 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.
  • compact_repeated_processor_chains — Compacts adjacent repeated processor chains in text output by showing one copy of the chain with a repetition count. This can make parallel pipelines easier to read when the same chain appears many times, for example in joins. It does not affect graph output. Default: 0.
When compact=0 and graph=1 processor names will contain an additional suffix with unique processor identifier. Example:

​
EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query, discards the result rows, and prints the same plan tree as EXPLAIN PLAN with each step annotated by what really happened at run time. Settings: EXPLAIN ANALYZE accepts the same display options as EXPLAIN PLAN (documented in the EXPLAIN PLAN section).
  • header — see EXPLAIN PLAN section.
  • description — see EXPLAIN PLAN section.
  • projections — see EXPLAIN PLAN section.
  • sorting — see EXPLAIN PLAN section.
  • input_headers — see EXPLAIN PLAN section.
  • column_structure — see EXPLAIN PLAN section.
  • actions — see EXPLAIN PLAN section. Default: 1.
  • indexes — see EXPLAIN PLAN section. Default: 1.
  • compact — see EXPLAIN PLAN section. Default: 1.
  • pretty — see EXPLAIN PLAN section. Default: 1.
  • processors — For EXPLAIN ANALYZE, prints an additional line per stage with the per-processor elapsed time distribution: min, median, max, and sum. Useful to spot load skew across parallel processors. Default: 0.
  • matches — For EXPLAIN ANALYZE, makes join steps do the extra bookkeeping needed for the matched, match rate and fanout metrics in the cases where those numbers cannot be derived from what the join produces anyway. Where they can, they are reported without this option. See Join steps. Default: 0.
Because EXPLAIN ANALYZE actually executes the wrapped query, it behaves like that query — and unlike the non-executing EXPLAIN forms — in several ways:
  • Quotas and limits. It is charged against the same quotas and subject to the same limits (e.g. query_selects, read_rows) as running the query directly. Sources exempt from quotas during planning (such as system.one) are not charged.
  • Failed transactions. Inside a transaction that has already failed (ROLLED_BACK), it is rejected with INVALID_TRANSACTION, just like a plain SELECT — issue ROLLBACK first.
  • Streaming reads. Over a streaming (FROM ... STREAM) read it is rejected with NOT_IMPLEMENTED, because such a read never completes.
  • Distributed queries. It is not supported for queries executed in distributed mode.
Example:
Let’s examine the output. First let’s look at the header.
  • Time — total time split into planning (i.e. creation of plan + optimization of plan + pipeline construction) and execution (running the pipeline) phases.
  • Read — rows and uncompressed bytes read from tables, with throughput - the same numbers the normal query footer reports as “Processed”.
  • Peak memory — peak memory the query used.
Now let’s look at the new lines that appear in the query plan.
Rows and bytes are reported once for the whole step (the I/O line). Time and parallelism are reported per stage of the step on the following indented line(s).
  • rows <in> → <out> — rows that entered and left the step; (<selectivity>%) shows how much the step filtered (out/in) or expanded the data, it is hidden when input rows equals output rows and when input rows equals 0.
  • <bytes_in> → <bytes_out> — uncompressed in-memory bytes flowing through the step (omitted when both are zero).
  • time <t> (<share>%) — wall-clock time the stage was active, and its share of query execution time (i.e. without build time). Note shares can add up to more than 100% because stages and steps run concurrently.
  • parallelism <avg>/<max> — average number of CPU threads working within this stage at once, out of the maximum it could use. A value near max means the stage was well parallelized; near 1 means it ran mostly serially.
  • Stage (<stage>) — the name of the stage. A step with a single stage prints the time line directly, without a Stage (...) label. Steps with several stages print one labeled line per stage, e.g. Aggregating shows Stage (partial aggregation) and Stage (final aggregation), and a hash join shows Stage (build) and Stage (probe).
ClickHouse parallelizes not only execution of tasks within a plan step, but also the execution of plan steps. The parallelism metric reflects only the work of this step. Other steps may run concurrently, so this number does not show how the step’s parallelism compares to the whole query.
The maximum number in parallelism is computed as a minimum between:
  1. total number of tasks within the plan step;
  2. The maximum number of query processing threads set in max_threads.

​
Join steps

For a join step EXPLAIN ANALYZE prints per-side participation lines — Left and Right — followed by any lines specific to the join implementation. Left and Right correspond to logical SQL sides. In most of the cases Left would also be the probe side of the join, and Right would be the build side of the join. However this is not always the case due to the swap that can happen during execution of the join. Every value of join_algorithm is covered (hash, parallel_hash, grace_hash, partial_merge, full_sorting_merge, parallel_full_sorting_merge, direct), and so are the two implementations that setting cannot select: a CROSS or COMMA join and any ON section without a key equality, and the Join table engine. Most of them report both sides; some report only the side they materialize (for example direct prints only Left:). The per-side lines share the same shape:
For each side EXPLAIN ANALYZE reports:
  • rows <rows> — the total number of rows of that side that passed through the join.
  • matched <matched_rows> — the number of rows of that side that found at least one join partner on the other side. This counts rows, not keys: if a key occurs three times on the right and matches, all three right rows count as matched.
  • match rate <match_rate>% — the percentage of that side’s rows that matched, computed as 100 * <matched_rows> / <rows>.
  • fanout <fanout> — how many output rows an average matched row of that side produced.
A number that cannot be derived exactly is reported as not collected rather than as 0. match rate and fanout are derived from matched, so a side without it reports all three as not collected.

​
Fanout

fanout measures row multiplication:
An outer join emits one NULL-padded output row for every row of a preserved side that found no partner. Those rows are subtracted so that they do not dilute the ratio. Only a preserved side has them — the right side for RIGHT and FULL, the left one for LEFT and FULL:
  • fanout = 0 — the matched rows produced no output row at all, which is what an ANTI join does: it emits only the rows that found no partner.
  • fanout = 1 — a clean 1:1 join; every matched row produced exactly one output row.
  • fanout > 1 — a 1:N join; duplicate keys on the other side multiplied the rows. A large value on both sides at once is the signature of an unintended Cartesian blowup.

​
When the numbers require matches = 1

Most of these numbers fall out of data the join builds anyway and are reported by a plain EXPLAIN ANALYZE. The rest need bookkeeping the join would otherwise not do, so they are only reported with EXPLAIN ANALYZE matches = 1. Which ones those are depends on the algorithm; in the hash family they are two cases:
  • the right side of ALL INNER and ALL LEFT, which requires marking every matched right row;
  • the left side of ALL LEFT and ALL FULL, but only when the query selects nothing from the right table and the ON section is a plain key equality. Otherwise the probe already records which left rows matched — either to materialize the right columns or to evaluate the residual condition — and the count is exact without the option.
partial_merge needs it for the right side of the four ALL kinds, for the same reason. full_sorting_merge and parallel_full_sorting_merge need it for both sides of the ANY kinds. The ALL kinds need nothing.
The option is off by default because the extra bookkeeping is not free, and what it costs is measurement fidelity. The work is done inside the probe loop and grows with the number of output rows. Use matches = 1 when you need to know the exact matches that left and right rows found on the other side.
matches = 1 does not make every combination collectable. Which side a join can report follows from what that join has to do anyway, so it depends on the algorithm as well as on the kind and strictness. Hash family. hash, parallel_hash and grace_hash always agree with each other: The right side is unavailable whenever the join keeps only one row per key in its hash table, which ANY, SEMI and ANTI joins do: the duplicate right rows are never stored, so they cannot be counted. The left side is unavailable when the join suppresses the output of a left row whose partner was already claimed by another left row, which makes the emitted rows an undercount of the matched ones. Enabling any_join_distinct_right_table_keys switches ANY to the older RightAny semantics, which emits one row per left row and therefore keeps both counts. ANY RIGHT and ANY FULL then report both sides, and ANY INNER is rewritten to SEMI LEFT. The Join table engine follows the same table, using the kind and strictness declared in the engine: Join(ALL, INNER, …) reports both sides, Join(ANY, LEFT, …) neither. Merge algorithms. full_sorting_merge and parallel_full_sorting_merge accept the four ALL kinds, ANY INNER, ANY LEFT, ANY RIGHT, ASOF and ASOF LEFT. They report both sides for every kind except ASOF and ASOF LEFT, where the right side is not collected, and without matches = 1 — they walk the two sorted inputs and see every row of an equal range as they consume it, so nothing has to be reconstructed afterwards. partial_merge accepts ALL INNER, ALL LEFT, ALL RIGHT, ALL FULL, ANY INNER, ANY LEFT and SEMI LEFT. It reports both sides for the four ALL kinds, the right one with matches = 1; for ANY INNER, ANY LEFT and SEMI LEFT the right side is not collected. direct. The left side only. The right side is a key-value store that is never materialized into rows, so it has no Right: line at all. CROSS, COMMA and a constant ON. Neither side, as described above. Where two algorithms both report a number, the numbers agree. The merge algorithms simply have more information; they do not disagree about what a match is.

​
Algorithm-specific lines

Let’s take a look at the lines each join implementation adds on top of those. For hash and parallel_hash joins, and for the Join table engine, a Hash table: line describes the hash table built from the right table:
  • unique keys <unique_keys> — the number of unique keys stored in the hash table during the build phase.
  • memory <peak_memory> — the peak memory used by the hash table during the build phase.
For grace_hash join the Hash table: line additionally reports how the join adapted to the memory limit, and a Spill: line reports whether data was spilled to disk:
  • buckets <buckets> — the number of buckets the grace hash join ended up with by the end of execution. This is always a power of 2.
  • rehashes <rehashes> — how many times the number of buckets had to be doubled in order to fit into the memory limit.
  • Spill: — a yes/no flag telling whether any spilling to disk happened. When it did, left spilled <left_spilled_bytes> and right spilled <right_spilled_bytes> report the compressed bytes spilled from the left (probe) and right (build) sides; when nothing was spilled the line is simply Spill: no.
For partial_merge join the Right: line carries extra information about how the right table was buffered and sorted, and the sorting time is shown on the Stage (build) and Stage (probe) lines:
  • size <right_size> — the memory size of the blocks of right table.
  • blocks <right_blocks> — the number of blocks the right table was buffered into.
  • storage <in-memory|external> — whether the right table fit into memory (in-memory) or had to be spilled to disk (external). When it is external, an extra spilled <spilled_bytes> reports the compressed bytes written to disk.
  • sort time <sort_time> — the time spent sorting the right table (on the build stage) and each incoming left block (on the probe stage).
  • sort share <sort_share>% — sort time as a share of that stage’s own busy time (the sum of its processors’ elapsed time), unlike the stage time percentage, which is a share of the whole query’s execution time.
For full_sorting_merge join only the common Left: and Right: lines are printed. For direct join only the Left: line is printed, since the right side is a key-value store that is looked up directly rather than materialized into rows. For a CROSS or COMMA join, and for any ON section without a key equality, a Buffer: line describes how the right table was held in memory and a Spill: line reports whether it went to disk:
  • memory <peak_memory> — the peak memory the buffered right table occupied.
  • compressed <yes|no> — whether at least one buffered block was compressed; readers then decompress every stored block.
  • Spill: — the same yes/no flag as for grace_hash, with right spilled <right_spilled_bytes> reporting the compressed bytes written to disk.
Both sides report matched not collected here: a constant predicate either pairs every left row with every right row or with none, so asking which individual rows matched has no answer. For a join against the Join table engine both sides are reported, together with the Hash table: line describing the pre-built table. The right side counts the rows stored in the engine, not the rows of some per-query build.

​
Per-processor times

With processors = 1, an extra line is printed under each stage, showing the distribution of elapsed time across the stage’s processors:
<n> is the number of processors in the stage. A large gap between median and max points to load skew between parallel processors.

​
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 WHATIF

Estimates the benefit a hypothetical skip index would have on a SELECT query, without materializing the index on disk. Define one or more candidates with CREATE HYPOTHETICAL INDEX, then run EXPLAIN WHATIF SELECT ... to see, for each candidate: applicability, estimated marks read, estimated bytes, and skip ratio. Syntax
Settings
  • empirical — 1 (default) runs the index over the baseline-pruned granules in memory to measure the skip ratio (an upper bound). 0 skips that path. Either way, if empirical doesn’t produce a result (disabled, or the index can’t be evaluated in memory) the estimator falls back to column statistics, and finally to an applicability-only summary if neither is available.
Output
  • source — how the estimate was produced.
    • empirical: built the index in memory over the baseline-pruned granules and counted the granules the index would skip. This is an upper bound — see the limitations in CREATE HYPOTHETICAL INDEX.
    • statistical: derived from column statistics. Used when empirical is disabled (empirical = 0) or empirical couldn’t produce a result, and column statistics are defined on the relevant columns.
    • applicability_only: the index is applicable to the predicate but neither empirical nor statistical estimation produced a result (e.g. empirical = 0 and no column statistics defined). Reports skip_ratio: 0.0% as a conservative bound.
  • sampled_parts / sampled_marks — <baseline-pruned> / <total in the table>. Shows what fraction of the table survived PK, partition, and existing-index pruning, i.e. the input to the hypothetical index.
  • est_bytes — an estimate of the bytes read, derived from the table’s average row size, so it is approximate and varies with storage and compression. The baseline line appears only when the query reads rows; the per-candidate line only when the baseline byte estimate is known.
The setting is written inline between WHATIF and the SELECT — there is no SETTINGS keyword (this matches how other EXPLAIN variants accept their options). If no hypothetical indexes are defined for the table, EXPLAIN WHATIF reports status: not_applicable with a hint to create one. Combined row (multiple candidates) When two or more candidates are evaluated empirically, EXPLAIN WHATIF appends one extra block named (combined: idx_a, idx_b, ...) after the per-candidate rows. It reports the joint benefit of having all of those indexes at once: a real read keeps a granule only if it survives every skip index, so the combined estimate is the intersection of the candidates’ surviving granules. Its skip_ratio is therefore at least as high as the best single candidate — complementary indexes prune more together, while redundant ones leave it unchanged. Only candidates with source: empirical contribute, because the combined row is built by intersecting their per-granule survival sets. Candidates estimated statistical or applicability_only have no per-granule data and are excluded; consequently the combined block appears only when at least two candidates produced an empirical estimate, and is omitted otherwise (for example under empirical = 0). Its estimation fields read the same as a per-candidate empirical block, except elapsed_us is 0 — the combined estimate is derived from the per-candidate scans, not a new scan. The synthetic (combined: ...) name is a report label only and cannot be used with force_data_skipping_indices. Empirical example
The hypothetical minmax would prune from 100 marks down to 1 — skip_ratio: 99.0%. (est_bytes is an estimate from the average row size, so the exact figure varies.) Statistical example Column statistics are off by default. To exercise the statistical path, define them on the relevant columns first and wait for the materialize mutation to finish:
Then disable the empirical path so the estimator falls back to column statistics:
The number comes from the column-statistic selectivity of b < 10 (about 10 rows out of 10000) and is reported as an upper bound on skip_ratio. There are no sampled_parts / sampled_marks — no data was read. If neither path is available (e.g. empirical = 0 and no column statistics defined), the estimator reports source: applicability_only and a conservative skip_ratio: 0.0%.

​
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 August 12, 2026