> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Documentation for Explain
# EXPLAIN
Shows the execution plan of a statement.
Syntax:
```sql theme={null}
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ANALYZE | ESTIMATE | TABLE OVERRIDE | WHATIF] [setting = value, ...]
[
SELECT ... |
tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
]
[FORMAT ...]
```
Example:
```sql theme={null}
EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;
```
```sql theme={null}
Output: sum(number)
Union
├──Aggregating
│ │ Keys:
│ │ Aggregates: sum(number)
│ │ Skip merging: 0
│ └──ReadFromSystemNumbers
│ Output: number
└──Sorting (Sorting for ORDER BY)
│ Sort description: sum(number) ASC
└──Aggregating
│ Keys:
│ Aggregates: sum(number)
│ Skip merging: 0
└──ReadFromSystemNumbers
Output: number
```
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](https://en.wikipedia.org/wiki/DOT_\(graph_description_language\)) graph description language. Default: 0.
Examples:
```sql theme={null}
EXPLAIN AST SELECT 1;
```
```sql theme={null}
SelectWithUnionQuery (children 1)
ExpressionList (children 1)
SelectQuery (children 1)
ExpressionList (children 1)
Literal UInt64_1
```
```sql theme={null}
EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();
```
```sql theme={null}
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.
* `single_record` – Return the reformatted query as a single multi-line record instead of one record per line. Default: `1` (controlled by the `explain_syntax_single_record` setting). Set to `0` to restore the historical one-record-per-line output, or set `explain_syntax_single_record = 0` (globally or in per-query `SETTINGS`), or set `compatibility` to any version older than `26.8`.
Examples:
```sql title="Query" theme={null}
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;
```
```sql title="Response" theme={null}
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`:
```sql title="Query" theme={null}
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;
```
```sql title="Response" theme={null}
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:
```sql theme={null}
EXPLAIN QUERY TREE SELECT id, value FROM test_table;
```
```sql theme={null}
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](/reference/engines/table-engines/mergetree-family/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](/reference/engines/table-engines/mergetree-family/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](/reference/formats/JSON/JSON) format. Default: 0. It is recommended to use [TabSeparatedRaw (TSVRaw)](/reference/formats/TabSeparated/TabSeparatedRaw) 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:
```sql theme={null}
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4 LIMIT 1;
```
```sql theme={null}
Output: sum(number)
Limit (preliminary LIMIT)
│ Limit 1
│ Offset 0
└──Aggregating
│ Keys: number MOD 4
│ Aggregates: sum(number)
│ Skip merging: 0
└──ReadFromSystemNumbers
Output: number
```
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:
```sql theme={null}
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
```
```json theme={null}
[
{
"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:
```json theme={null}
{
"Node Type": "ReadFromStorage",
"Description": "SystemOne"
}
```
With `header` = 1, the `Header` key is added to the step as an array of columns.
Example:
```sql theme={null}
EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;
```
```json theme={null}
[
{
"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:
The `Statistics` index uses per-part column statistics (min/max values, and the number of `NULL` values for `Nullable` columns) to skip parts that cannot match the query filter.
* `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:
```json theme={null}
"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:
```json theme={null}
"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:
```sql theme={null}
EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;
```
```json theme={null}
[
{
"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 = 0` and `actions = 1`, the `Expression` steps can be seen along with detailed information about expressions:
```sql theme={null}
EXPLAIN actions = 1, compact = 0 SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
```
```text theme={null}
Output: sum(number)
Expression ((Project names + Projection))
│ Actions: INPUT : 0 -> sum(__table1.number) UInt64 : 0
│ INPUT :: 1 -> modulo(__table1.number, 4_UInt8) UInt8 : 1
│ ALIAS sum(__table1.number) :: 0 -> sum(number) UInt64 : 2
│ Positions: 2
└──Aggregating
│ Keys: number MOD 4
│ Aggregates: sum(number)
│ Skip merging: 0
└──Expression ((Before GROUP BY + Change column names to column identifiers))
│ Actions: INPUT : 0 -> number UInt64 : 0
│ COLUMN Const(UInt8) -> 4_UInt8 UInt8 : 1
│ ALIAS number :: 0 -> __table1.number UInt64 : 2
│ FUNCTION modulo(__table1.number : 2, 4_UInt8 :: 1) -> modulo(__table1.number, 4_UInt8) UInt8 : 0
│ Positions: 0 2
└──ReadFromSystemNumbers
Output: number
```
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:
```sql theme={null}
EXPLAIN distributed=1 SELECT * FROM remote('127.0.0.{1,2}', numbers(2)) WHERE number = 1;
```
```sql theme={null}
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:
```sql theme={null}
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;
```
```sql theme={null}
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, the estimates the
join-order optimizer produced for the step (cost, selectivity, output rows, and per-side rows),
and the input columns of each 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.
Below the join relation, each join step prints the estimates the join-order optimizer produced for it:
```txt theme={null}
Cost: estimated
Selectivity: estimated (NDV)
Output rows: estimated
Left: rows estimated
Right: rows estimated
```
* `Cost` — the cost of the whole join subtree under this step, which is the value the optimizer
minimizes when it compares candidate join orders. The cost of a join is its estimated number of
matched row pairs, ` * * `, plus the cost of its inputs.
* `Selectivity` — the estimated fraction of the Cartesian product of the two sides that survives
the join condition. It is derived from the number of distinct values (NDV) of the join keys:
a key equality keeps about `1 / max(NDV_left, NDV_right)` of the pairs, and the smallest
fraction over the join conditions is used.
* `Output rows` — the estimated number of rows the join produces:
` * * ` for an inner join, floored at `` for
`LEFT`, at `` for `RIGHT`, and at ` + ` for `FULL`, because an
outer join keeps every row of its preserved side. When a `SEMI` or `ANTI` join takes part in the
reordering, it is estimated as a fraction of its preserved side,
` * min(1, * )` for `SEMI` and the remaining rows for
`ANTI`; otherwise it uses the formula of its join kind.
* `Left` / `Right` — the estimated number of rows entering the join from each side.
A value the optimizer could not estimate is reported as `no stats`. This happens when the
join-order optimization did not run — for example, when
[`query_plan_optimize_join_order_limit`](/reference/settings/session-settings/query-plan#query_plan_optimize_join_order_limit)
is `0` — or when there is no basis for the estimate.
The `Input (left):` and `Input (right):` lines list the columns each side feeds into the join.
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. The
[`join_runtime_filter_min_probe_rows`](/reference/settings/session-settings/join-runtime#join_runtime_filter_min_probe_rows)
setting is lowered only so that a table this small still builds a runtime join filter:
```sql theme={null}
SET join_runtime_filter_min_probe_rows = 10;
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;
```
```text theme={null}
Output: id, value, id, value
Join (JOIN FillRightFirst)
│ t1[100] ⋈ t2[100]
│ Type: inner | Strictness: all | Algorithm: SpillingHashJoin(HashJoin)
│ Cost: estimated 100.00
│ Selectivity: estimated (NDV) 0.01
│ Output rows: estimated 100.00
│ Left: rows estimated 100.00
│ Right: rows estimated 100.00
│ Join conditions: id = id
│ Input (left): id, value
│ Input (right): id, value
├──ReadFromMergeTree (default.t1)
│ Read type: Default
│ Parts: 1 | Granules: 1
│ Output: id, value
│ Runtime filters: RF1(id, id from default.t2)
└──BuildRuntimeFilter (Build runtime join filter on id)
│ Filter id: RF1
│ Source table: default.t2
└──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](https://en.wikipedia.org/wiki/DOT_\(graph_description_language\)) 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.
```text theme={null}
Resize 16 → 1
FillingRightJoinSide │
SimpleSquashingTransform │ × 16
Resize 1 → 16
```
When `compact=0` and `graph=1` processor names will contain an additional suffix with unique processor identifier.
Example:
```sql theme={null}
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;
```
```sql theme={null}
(Union)
(Expression)
ExpressionTransform
(Expression)
ExpressionTransform
(Aggregating)
Resize 2 → 1
AggregatingTransform × 2
(Expression)
ExpressionTransform × 2
(SettingQuotaAndLimits)
(ReadFromStorage)
NumbersRange × 2 0 → 1
```
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](#explain-plan) section).
* `header` — see [EXPLAIN PLAN](#explain-plan) section.
* `description` — see [EXPLAIN PLAN](#explain-plan) section.
* `projections` — see [EXPLAIN PLAN](#explain-plan) section.
* `sorting` — see [EXPLAIN PLAN](#explain-plan) section.
* `input_headers` — see [EXPLAIN PLAN](#explain-plan) section.
* `column_structure` — see [EXPLAIN PLAN](#explain-plan) section.
* `actions` — see [EXPLAIN PLAN](#explain-plan) section. Default: 1.
* `indexes` — see [EXPLAIN PLAN](#explain-plan) section. Default: 1.
* `compact` — see [EXPLAIN PLAN](#explain-plan) section. Default: 1.
* `pretty` — see [EXPLAIN PLAN](#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](#explain-analyze-join-steps). Default: 0.
* `time` — For `EXPLAIN ANALYZE`, prints per-step `Time` and `Concurrency` lines with the wall-clock time and the concurrency level of the step and of its branch. See [Step and branch wall-clock time and concurrency levels](#explain-analyze-concurrency). Default: 1.
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](/concepts/features/configuration/server-config/quotas)
and subject to the same [limits](/concepts/features/configuration/settings/query-complexity)
(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](/concepts/features/operations/insert/transactions)
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](/reference/engines/table-engines/special/distributed) mode.
Example:
```sql theme={null}
EXPLAIN ANALYZE SELECT number % 10 AS k, count() FROM numbers_mt(1000000) GROUP BY k;
```
```text theme={null}
Query summary:
Time: 22.65 ms (planning 7.98 ms · execution 14.66 ms)
Execution: in steps 3.46 ms (23.60%) · outside steps 16.60 us (0.11%) · idle 11.19 ms (76.29%)
Read: 1.00 million rows, 8.00 MB (68.20 million rows/s., 545.59 MB/s.)
Peak memory: 16.00 KiB
Output: number MOD 10, count()
Expression ((Project names + Projection))
│ I/O: rows 10 → 10 · 90 B → 90 B
│ Time: step 12.09 us (0.08%) · branch 3.46 ms (23.60%)
│ Concurrency: step 1.00/16 · branch 4.96/16
│ time 12.36 us (0.08%) · parallelism 0.98/1
└──Aggregating
│ Keys: number MOD 10
│ Aggregates: count()
│ Skip merging: 0
│ I/O: rows 1.00 million → 10 (0.00%) · 1.00 MB → 90 B
│ Time: step 2.03 ms (13.85%) · branch 3.45 ms (23.52%)
│ Concurrency: step 5.93/16 · branch 4.97/16
│ Stage (partial aggregation): time 1.69 ms (11.50%) · parallelism 1.80/15
│ Stage (final aggregation): time 351.39 us (2.40%) · parallelism 1.10/16
└──Expression ((Before GROUP BY + Change column names to column identifiers))
│ I/O: rows 1.00 million → 1.00 million · 8.00 MB → 1.00 MB
│ Time: step 2.02 ms (13.78%) · branch 2.76 ms (18.81%)
│ Concurrency: step 7.13/16 · branch 5.87/16
│ time 2.02 ms (13.81%) · parallelism 2.26/11
└──ReadFromSystemNumbers
Output: number
I/O: rows 0 → 1.00 million · 0 B → 8.00 MB
Time: step 2.58 ms (17.57%) · branch 2.58 ms (17.57%)
Concurrency: step 5.98/16 · branch 5.98/16
time 2.58 ms (17.58%) · parallelism 3.55/15
```
Let's examine the output. First let's look at the header.
```txt theme={null}
Query summary:
Time: (planning · execution )
Execution: in steps (%) · outside steps (%) · idle (%)
Read: rows, (, )
Peak memory:
```
* `Time` — total time split into planning (i.e. creation of plan + optimization of plan + pipeline construction) and execution (running the pipeline) phases.
* `Execution` — not printed with `time = 0`. Splits the execution time into `in steps`, `outside steps` and `idle`. The parts add up to `execution`. See [Step and branch wall-clock time and concurrency levels](#explain-analyze-concurrency).
* `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.
```txt theme={null}
I/O: rows → (%) · →
[Stage (): ]time (%) · parallelism /
```
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 → ` — rows that entered and left the step; (``%) 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`.
* ` → ` — uncompressed in-memory bytes flowing through the step (omitted when both are zero).
* `time (%)` — 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 /` — average number of CPU threads working within this stage at once, out of the maximum it could use. This metric is local to the stage: threads that work on other steps of the query at the same time do not count. A value near max means the stage was well parallelized; near 1 means it ran mostly serially.
* `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. For that query-wide view, see the `Concurrency` metric printed with `time = 1` ([Step and branch wall-clock time and concurrency levels](#explain-analyze-concurrency)).
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 lines comparing the join-order optimizer's estimates with what actually happened (see [Estimated vs. actual join metrics](#explain-analyze-join-estimation)) and 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`](/reference/settings/session-settings/join#join_algorithm) is covered (`hash`, `parallel_hash`, `grace_hash`, `partial_merge`, `full_sorting_merge`, `parallel_full_sorting_merge`, `direct`), and so are the three implementations that setting cannot select: a `CROSS` or `COMMA` join, the [block nested loop join](/reference/statements/select/join#join-with-an-arbitrary-on-condition) that takes an `ON` section determining no join key, and the [`Join`](/reference/engines/table-engines/special/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:
```txt theme={null}
Left: rows estimated · rows · matched · match rate % · fanout
Right: rows estimated · rows · matched · match rate % · fanout
```
For each side `EXPLAIN ANALYZE` reports:
* `rows estimated ` — the join-order optimizer's estimate of that side's rows, printed for comparison with the actual `rows` next to it; `no stats` when the optimizer produced no estimate (see [Estimated vs. actual join metrics](#explain-analyze-join-estimation)).
* `rows ` — the total number of rows of that side that passed through the join.
* `matched ` — 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 %` — the percentage of that side's rows that matched, computed as `100 * / `.
* `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`.
Estimated vs. actual join metrics
A join step carries the join-order optimizer's estimates — the same ones `EXPLAIN PLAN` shows (see the [EXPLAIN PLAN](#explain-plan) section) — and `EXPLAIN ANALYZE` prints each of them next to the measured value:
```txt theme={null}
Cost: estimated · actual
Selectivity: estimated (NDV) · actual (cartesian)
Output rows: estimated · actual · q-error
```
* `Cost` — both values count matched output rows. The estimate is the optimizer's cost of the join subtree: ` * * ` plus the cost of its inputs. The actual value is measured the same way — the matched output rows of this join plus the actual cost of every join below it that belongs to the same reorder cluster.
* `Selectivity` — the estimate is derived from the number of distinct values of the join keys; the actual value is the measured fraction of the Cartesian product that ended up in the output: ` / ( * )`, since this is what optimizer tries to estimate.
* `Output rows` — the estimated and the actual number of rows the join produced. When both are non-zero, `q-error` reports `max(estimated / actual, actual / estimated)`, the standard measure of cardinality-estimation quality: `1.00` means a perfect estimate, and a large value means the optimizer picked the join order using a badly wrong cardinality.
An estimate that was never made is reported as `no stats` — for example, when the join-order optimization did not run because [`query_plan_optimize_join_order_limit`](/reference/settings/session-settings/query-plan#query_plan_optimize_join_order_limit) is `0`. This is distinct from `not collected`, which marks an actual value the execution could not measure. Joins with a pre-filled right side (the [`Join`](/reference/engines/table-engines/special/join) table engine, `direct` joins) do not go through the join-order optimizer and print only the participation lines.
The join step also prints `Input (left):` and `Input (right):` lines with the columns each side feeds into the join.
For the tables of the [EXPLAIN PLAN](#explain-plan) join example, the join step of `EXPLAIN ANALYZE SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id` looks like this:
```text theme={null}
Join (JOIN FillRightFirst)
│ t1[100] ⋈ t2[100]
│ Type: inner | Strictness: all | Algorithm: SpillingHashJoin(HashJoin)
│ Join conditions: id = id
│ Cost: estimated 100.00 · actual 100.00
│ Selectivity: estimated (NDV) 0.01 · actual (cartesian) 0.01
│ Output rows: estimated 100.00 · actual 100.00 · q-error 1.00
│ Left: rows estimated 100.00 · rows 100.00 · matched 100.00 · match rate 100.00% · fanout 1.00
│ Right: rows estimated 100.00 · rows 100.00 · matched not collected · match rate not collected · fanout not collected
│ Hash table: unique keys 100.00 · memory 6.27 KB
│ Input (left): id, value
│ Input (right): id, value
│ I/O: rows 200 → 100 (50.00%) · 3.58 KB → 3.58 KB
│ Stage (build): time 127.45 us (1.2%) · parallelism 0.99/1
│ Stage (probe): time 124.99 us (1.2%) · parallelism 0.99/1
```
Fanout
`fanout` measures row multiplication:
```txt theme={null}
matched output rows = -
fanout = /
```
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:
| Join | `matched` left | `matched` right |
| - | - | - |
| `ALL INNER`, `ALL LEFT`, `ALL RIGHT`, `ALL FULL` | yes | yes |
| `SEMI LEFT`, `ANTI LEFT` | yes | no |
| `ANY RIGHT`, `ANTI RIGHT` | no | yes |
| `ASOF` (inner) | yes | no |
| `SEMI RIGHT` | no | no |
| `ANY INNER`, `ANY LEFT`, `ASOF LEFT` | no | no |
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`](/reference/settings/session-settings/other#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`](/reference/engines/table-engines/special/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](#explain-analyze-join-algorithm-lines).
**Block nested loop join.** The operator that takes an `ON` section determining no join key, unless one of the paths before it claims the condition (see [`allow_block_nested_loop_join`](/reference/settings/session-settings/allow)). It accepts every kind and strictness except `ASOF`, `PASTE` and `ANY FULL`:
| Join | `matched` left | `matched` right |
| - | - | - |
| `ALL FULL` | yes | yes |
| `ALL LEFT`, `ANY LEFT`, `SEMI LEFT`, `ANTI LEFT`, `ANY INNER` | yes | no |
| `ALL RIGHT`, `ANY RIGHT`, `SEMI RIGHT`, `ANTI RIGHT` | with `matches = 1` | yes |
| `ALL INNER` | with `matches = 1` | no |
The left side is reported without the option wherever the operator already records which left rows matched: every kind that keeps the unmatched left rows needs that record, and so does every strictness that takes at most one pair per left row. The right side is reported wherever the operator keeps its match flags, which is what `RIGHT`, `FULL` and a right-driven `SEMI`/`ANTI` need. `ANY INNER` keeps them too, but only in order to give a right row to a single left row: it stops claiming for a left row that already has its pair, so the flags mark the pairs of the result rather than every match, and are not reported. `ALL INNER` reaches the operator only when `hash` is disabled, since otherwise the condition becomes a `CROSS JOIN` with a filter.
Enabling [`any_join_distinct_right_table_keys`](/reference/settings/session-settings/other#any_join_distinct_right_table_keys) switches `ANY` to the `RightAny` semantics here as well, which reports the left side for every kind and the right side for `RIGHT` and `FULL`; `ANY INNER` is rewritten to `SEMI LEFT`.
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:
```txt theme={null}
Hash table: unique keys · memory
```
* `unique keys ` — the number of unique keys stored in the hash table during the build phase.
* `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:
```txt theme={null}
Hash table: unique keys · memory · buckets · rehashes
Spill: yes · left spilled · right spilled
```
* `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 ` — 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 ` and `right spilled ` 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:
```txt theme={null}
Right: rows estimated · rows · matched · size · blocks · storage · match rate % · fanout
Stage (build): time (%) · parallelism / · sort time · sort share %
Stage (probe): time (%) · parallelism / · sort time · sort share %
```
* `size ` — the memory size of the blocks of right table.
* `blocks ` — the number of blocks the right table was buffered into.
* `storage ` — whether the right table fit into memory (`in-memory`) or had to be spilled to disk (`external`). When it is `external`, an extra `spilled ` reports the compressed bytes written to disk.
* `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 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 an `ALL INNER JOIN` whose `ON` section has no key equality — which the planner turns into a `CROSS JOIN` with that condition as a filter — a `Buffer:` line describes how the right table was held in memory and a `Spill:` line reports whether it went to disk:
```txt theme={null}
Buffer: memory · compressed
Spill: yes · right spilled
```
* `memory ` — the peak memory the buffered right table occupied.
* `compressed ` — 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 ` 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.
The block nested loop join, which takes every other `ON` section without a key equality, prints the same two lines about the right table it materializes:
```txt theme={null}
Buffer: memory · compressed
Spill: yes · right spilled
```
* `memory ` — the peak memory the stored right table occupied. It is `0` when every block was written to disk as it arrived, since none of them was ever held in memory.
* `compressed ` — whether at least one stored block was compressed; readers then decompress every stored block.
* `Spill:` — the same `yes`/`no` flag as for `grace_hash`, with `right spilled ` reporting the compressed bytes written to disk.
Unlike the `CROSS` case, both sides can report `matched` here, since the operator evaluates a real condition on each pair it examines; which of them does is in the table [above](#explain-analyze-matches).
For a join against the [`Join`](/reference/engines/table-engines/special/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:
```txt theme={null}
Time per processor (): min · median · max · sum
```
`` is the number of processors in the stage. A large gap between `median` and `max` points to load skew between parallel processors.
Step and branch wall-clock time and concurrency levels
Unless `time = 0`, two more lines are printed for every query plan step:
```txt theme={null}
Time: step (%) · branch (%)
Concurrency: step / · branch /
```
Here `step` refers to the step itself. `branch` refers to the subtree rooted at the step: the step together with all steps below it in the plan.
* `Time` — the wall-clock time during which at least one thread did work for the step (`step`), or for any step of the subtree (`branch`). `` is that time as a percentage of the query execution time.
* `Concurrency` — the average number of threads that were busy with *any* step of the query — not only this one — while the step (`step`) or its subtree (`branch`) was active. `` is the maximum number of threads the query could use. Steps that own no processors, for example `Union` when its inputs already have the same header, print `Unknown` for `step`: there is no work to measure. `branch` is still reported.
Both metrics are derived from work intervals. Each time a thread finishes a piece of work, the executor records an interval: the start time, the end time, and the step the work belongs to.
`Time` of a step is the length of the union of that step's intervals. Gaps, where no thread worked on the step, do not count. `Time` of a branch unites the intervals of all steps of the subtree first.
The `branch` of the root step is the whole plan, but its share is normally below 100%. The `Execution` line of the query summary shows where the rest of the execution time went (see the query summary in the example above):
* `in steps` — the union of the intervals of every step of the plan. This is the `branch` time of the root step, and the share is the same number.
* `outside steps` — the time when at least one thread ran a processor, but none of them belonged to a step of this plan: the output sink, `Resize`, converting transforms, and the steps of plans that are not reachable from this one.
* `idle` — the time when no thread ran any processor: the start-up and shutdown of the executor, and the moments when every thread waited.
The `Time` shares, the per-stage `time` shares, and the `Execution` shares use the same denominator, so they can be compared with each other.
`Concurrency` is the total busy time of all query threads inside that union, divided by the length of the union. It counts threads busy with *any* step of the query, unlike the per-stage `parallelism` metric, which counts only the threads of the stage itself. It answers: "while this step (branch) was active, how busy was the query as a whole?" A value near `` means the query stayed fully parallel during that step's lifetime. A value near `1` means the query serialized during that time: for `step`, the single busy thread was working on this step itself, which makes the step a serialization point; for `branch`, the thread was working somewhere in the subtree, not necessarily on this step. A serialized step is a real bottleneck only when its `Time` share is also significant.
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](/reference/engines/table-engines/mergetree-family/mergetree) family.
**Example**
Creating a table:
```sql title="Query" theme={null}
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;
```
```sql title="Query" theme={null}
EXPLAIN ESTIMATE SELECT * FROM ttt;
```
```text title="Response" theme={null}
┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ default │ ttt │ 1 │ 128 │ 8 │
└──────────┴───────┴───────┴──────┴───────┘
```
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`](/reference/statements/hypothetical-index#create-hypothetical-index), then run `EXPLAIN WHATIF SELECT ...` to see, for each candidate: applicability, estimated marks read, estimated bytes, and skip ratio.
Hypothetical projections defined with [`CREATE HYPOTHETICAL PROJECTION`](/reference/statements/hypothetical-projection#create-hypothetical-projection) are candidates too. For a normal projection, the estimate builds its primary index in memory over the parts the query would read and prunes it as a materialized projection would be pruned.
The report gives the marks and rows it would read, a `read_ratio` against the base-table read, and a `verdict` on whether the optimizer would choose it. With `force_optimize_projection = 1` or `prefer_optimize_projection = 1` the optimizer uses any usable projection, so one it would not pick by cost is reported as `chosen (forced)`.
When the result depends on how the projection part would be laid out, `marks_span` gives the range, and a decision that could go either way is reported as `too close to call`. On large tables the estimate reads a sample of granules (see `projection_scan_budget_rows`).
Aggregate projections, projections with a `WHERE` clause, their own skip indexes, a commit-order or virtual-column key or a stored `_block_number`, and projections that do not cover every column the query reads are reported as `not_applicable`. `force_optimize_projection_name` and `preferred_optimize_projection_name` are ignored, and `force_optimize_projection` does not fail the statement.
**Syntax**
```sql theme={null}
EXPLAIN WHATIF [empirical = 0] SELECT ...
```
**Settings**
* `projection_scan_budget_rows` — how many rows a projection estimate may read before it switches to a sample of granules. Default: `10000000`. `0` means no limit; `max_rows_to_read` can lower it. A sample reads at least about 30 granules and one per part, and when `max_rows_to_read` does not allow that the estimate is `unsupported`.
* `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](/reference/engines/table-engines/mergetree-family/mergetree#column-statistics), and finally to an applicability-only summary if neither is available.
**Output**
```text theme={null}
Baseline (after PK + partition + existing indexes):
table: db.t
parts: 1
marks: 100
rows: 10000
est_bytes: 1.50 MiB (only when the query reads rows)
With idx_b (minmax, hypothetical):
status: applicable
marks: 1
est_bytes: 15.00 KiB (only when baseline bytes are known)
skip_ratio: 99.0%
Estimation:
source: empirical | statistical | applicability_only
empirical_status: ok | unsupported | disabled
empirical_reason: (only when empirical_status = unsupported)
sampled_parts: 50 / 100 (only when source = empirical)
sampled_marks: 50 / 100 (only when source = empirical)
elapsed_us: 631 (only when source = empirical)
```
* `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`](/reference/statements/hypothetical-index#limitations).
* `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.
* `empirical_reason` — why the empirical estimate could not run. Shown only with `empirical_status: unsupported`. For example, a non-zero `merge_tree_min_rows_for_seek` or `merge_tree_min_bytes_for_seek` makes a real read coalesce mark ranges, which the per-granule count does not model, so the estimate falls back to `statistical` or `applicability_only`.
* `sampled_parts` / `sampled_marks` — ` / `. Shows what fraction of the table survived PK, partition, and existing-index pruning, i.e. the input to the hypothetical index. For a projection, `sampled_marks` is ` / `, and a first number below the second means the estimate used a sample.
* `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 neither hypothetical indexes nor hypothetical projections 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**
```sql theme={null}
CREATE TABLE t (a UInt64, b UInt64) ENGINE = MergeTree ORDER BY a
SETTINGS index_granularity = 100;
INSERT INTO t SELECT number, number FROM numbers(10000);
CREATE HYPOTHETICAL INDEX idx_b ON t (b) TYPE minmax GRANULARITY 1;
EXPLAIN WHATIF SELECT * FROM t WHERE b = 42;
```
```text theme={null}
Baseline (after PK + partition + existing indexes):
table: default.t
parts: 1
marks: 100
rows: 10000
est_bytes: 85.52 KiB
With idx_b (minmax, hypothetical):
status: applicable
marks: 1
est_bytes: 875.00 B
skip_ratio: 99.0%
Estimation:
source: empirical
empirical_status: ok
sampled_parts: 1 / 1
sampled_marks: 100 / 100
```
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](/reference/engines/table-engines/mergetree-family/mergetree#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:
```sql theme={null}
ALTER TABLE t ADD STATISTICS b TYPE tdigest;
ALTER TABLE t MATERIALIZE STATISTICS b SETTINGS mutations_sync = 1;
```
Then disable the empirical path so the estimator falls back to column statistics:
```sql theme={null}
EXPLAIN WHATIF empirical = 0 SELECT * FROM t WHERE b < 10;
```
```text theme={null}
With idx_b (minmax, hypothetical):
status: applicable
marks: 1
est_bytes: 1.66 KiB
skip_ratio: 99.9%
Estimation:
source: statistical
empirical_status: disabled
```
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%`.
**Hypothetical projections**
A normal projection is estimated the same way, against the parts the query would read after primary-key and partition pruning:
```sql theme={null}
CREATE TABLE t (a UInt64, b UInt64, v UInt64) ENGINE = MergeTree ORDER BY a SETTINGS index_granularity = 100;
INSERT INTO t SELECT number, number % 100, number FROM numbers(10000);
CREATE HYPOTHETICAL PROJECTION p_b ON t (SELECT a, b, v ORDER BY b);
EXPLAIN WHATIF SELECT count() FROM t WHERE b = 42;
```
```text theme={null}
Baseline (after PK + partition + existing indexes):
table: default.t
parts: 1
marks: 100
rows: 10000
est_bytes: 80.79 KiB
With p_b (normal projection, hypothetical):
status: applicable
marks: 2
rows: 200
read_ratio: 0.02x
verdict: chosen
reason: 2 marks would be read instead of 100 from the base table
Estimation:
source: empirical
empirical_status: ok
sampled_parts: 1 / 1
sampled_marks: 100 / 100
elapsed_us: 1126
```
`marks` and `rows` are what the projection read itself would touch, not a share of the base-table read, because a projection granule holds different rows than a base granule. `read_ratio` is those marks over the base-table marks, so `0.02x` is fifty times less work and `15x` is fifteen times more. `verdict` applies the optimizer's own rule — fewer marks than the base table, or the same number when the projection serves the query's `ORDER BY` — so a candidate can be `applicable` and still not be chosen.
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:
```sql title="Query" theme={null}
CREATE TABLE db.tbl (
id INT PRIMARY KEY,
created DATETIME DEFAULT now()
)
```
```sql title="Query" theme={null}
EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'tbl', 'root', 'clickhouse')
PARTITION BY toYYYYMM(assumeNotNull(created))
```
```text title="Response" theme={null}
┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: `created` Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘
```
The validation is not complete, so a successful query does not guarantee that the override would not cause issues.