EXPLAIN Statement

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

Example with parallel replicas:

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

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

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

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

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

- **Query output columns** are printed at the top of the plan.
- **Source steps** (such as `ReadFromMergeTree`) display their output columns.
- **Join steps** display the join relation using mathematical notation, estimated result row count,
  and which output columns come from the left vs. right side. The following symbols are used to
  represent different join types:

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

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

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

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

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

A more detailed example with joins:

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

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

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

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

### EXPLAIN PIPELINE[​](#explain-pipeline "Direct link to 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.

When `compact=0` and `graph=1` processor names will contain an additional suffix with unique processor identifier.

Example:

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

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

### EXPLAIN ESTIMATE[​](#explain-estimate "Direct link to 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](/docs/engines/table-engines/mergetree-family/mergetree) family.

**Example**

Creating a table:

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

Query:

```
EXPLAIN ESTIMATE SELECT * FROM ttt;
```

Result:

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

### EXPLAIN TABLE OVERRIDE[​](#explain-table-override "Direct link to EXPLAIN TABLE OVERRIDE")

Shows the result of a table override on a table schema accessed through a table function.
Also does some validation, throwing an exception if the override would have caused some kind of failure.

**Example**

Assume you have a remote MySQL table like this:

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

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

Result:

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

Note

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

- [EXPLAIN Types](#explain-types)
  - [EXPLAIN AST](#explain-ast)- [EXPLAIN SYNTAX](#explain-syntax)- [EXPLAIN QUERY TREE](#explain-query-tree)- [EXPLAIN PLAN](#explain-plan)- [EXPLAIN PIPELINE](#explain-pipeline)- [EXPLAIN ESTIMATE](#explain-estimate)- [EXPLAIN TABLE OVERRIDE](#explain-table-override)

Was this page helpful?