Shows the execution plan of a statement.
Syntax:
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
[
SELECT ... |
tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
]
[FORMAT ...]
Example:
EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;
Union
Expression (Projection)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
Expression (Projection)
MergingSorted (Merge sorted streams for ORDER BY)
MergeSorting (Merge sorted blocks for ORDER BY)
PartialSorting (Sort each block for ORDER BY)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
EXPLAIN Types
AST — Abstract syntax tree.SYNTAX — Query text after AST-level optimizations.QUERY TREE — Query tree after Query Tree level optimizations.PLAN — Query execution plan.PIPELINE — Query execution pipeline.
EXPLAIN AST
Dump query AST. Supports all types of queries, not only SELECT.
Examples:
SelectWithUnionQuery (children 1)
ExpressionList (children 1)
SelectQuery (children 1)
ExpressionList (children 1)
Literal UInt64_1
EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();
explain
AlterQuery t1 (children 1)
ExpressionList (children 1)
AlterCommand 27 (children 1)
Function equals (children 1)
ExpressionList (children 2)
Identifier date
Function today (children 1)
ExpressionList
EXPLAIN SYNTAX
Shows the Abstract Syntax Tree (AST) of a query after syntax analysis.
It's done by parsing the query, constructing query AST and query tree, optionally running query analyzer and optimization passes, and then converting the query tree back to the query AST.
Settings:
oneline – Print the query in one line. Default: 0.run_query_tree_passes – Run query tree passes before dumping the query tree. Default: 0.query_tree_passes – If run_query_tree_passes is set, specifies how many passes to run. Without specifying query_tree_passes it runs all the passes.
Examples:
EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c WHERE a.number = b.number AND b.number = c.number;
Output:
SELECT *
FROM system.numbers AS a, system.numbers AS b, system.numbers AS c
WHERE (a.number = b.number) AND (b.number = c.number)
With run_query_tree_passes:
EXPLAIN SYNTAX run_query_tree_passes = 1 SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c WHERE a.number = b.number AND b.number = c.number;
Output:
SELECT
__table1.number AS `a.number`,
__table2.number AS `b.number`,
__table3.number AS `c.number`
FROM system.numbers AS __table1
ALL INNER JOIN system.numbers AS __table2 ON __table1.number = __table2.number
ALL INNER JOIN system.numbers AS __table3 ON __table2.number = __table3.number
EXPLAIN QUERY TREE
Settings:
run_passes — Run all query tree passes before dumping the query tree. Default: 1.dump_passes — Dump information about used passes before dumping the query tree. Default: 0.passes — Specifies how many passes to run. If set to -1, runs all the passes. Default: -1.dump_tree — Display the query tree. Default: 1.dump_ast — Display the query AST generated from the query tree. Default: 0.
Example:
EXPLAIN QUERY TREE SELECT id, value FROM test_table;
QUERY id: 0
PROJECTION COLUMNS
id UInt64
value String
PROJECTION
LIST id: 1, nodes: 2
COLUMN id: 2, column_name: id, result_type: UInt64, source_id: 3
COLUMN id: 4, column_name: value, result_type: String, source_id: 3
JOIN TREE
TABLE id: 3, table_name: default.test_table
EXPLAIN PLAN
Dump query plan steps.
Settings:
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.projections — Shows all analyzed projections and their effect on part-level filtering based on projection primary key conditions. For each projection, this section includes statistics such as the number of parts, rows, marks, and ranges that were evaluated using the projection's primary key. It also shows how many data parts were skipped due to this filtering, without reading from the projection itself. Whether a projection was actually used for reading or only analyzed for filtering can be determined by the description field. Default: 0. Supported for MergeTree tables.actions — Prints detailed information about step actions. Default: 0.json — Prints query plan steps as a row in JSON format. Default: 0. It is recommended to use TSVRaw format to avoid unnecessary escaping.
When json=1 step names will contain an additional suffix with unique step identifier.
Example:
EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;
Union
Expression (Projection)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
Note
Step and query cost estimation is not supported.
When json = 1, the query plan is represented in JSON format. Every node is a dictionary that always has the keys Node Type and Plans. Node Type is a string with a step name. Plans is an array with child step descriptions. Other optional keys may be added depending on node type and settings.
Example:
EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;
[
{
"Plan": {
"Node Type": "Union",
"Node Id": "Union_10",
"Plans": [
{
"Node Type": "Expression",
"Node Id": "Expression_13",
"Plans": [
{
"Node Type": "ReadFromStorage",
"Node Id": "ReadFromStorage_0"
}
]
},
{
"Node Type": "Expression",
"Node Id": "Expression_16",
"Plans": [
{
"Node Type": "ReadFromStorage",
"Node Id": "ReadFromStorage_4"
}
]
}
]
}
}
]
With description = 1, the Description key is added to the step:
{
"Node Type": "ReadFromStorage",
"Description": "SystemOne"
}
With header = 1, the Header key is added to the step as an array of columns.
Example:
EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;
[
{
"Plan": {
"Node Type": "Expression",
"Node Id": "Expression_5",
"Header": [
{
"Name": "1",
"Type": "UInt8"
},
{
"Name": "plus(2, dummy)",
"Type": "UInt16"
}
],
"Plans": [
{
"Node Type": "ReadFromStorage",
"Node Id": "ReadFromStorage_0",
"Header": [
{
"Name": "dummy",
"Type": "UInt8"
}
]
}
]
}
}
]
With indexes = 1, the Indexes key is added. It contains an array of used indexes. Each index is described as JSON with Type key (a string MinMax, Partition, 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 before/after the index is applied.Granules — The number of granules before/after the index is applied.Ranges — The number of granules ranges after the index is applied.
Example:
"Node Type": "ReadFromMergeTree",
"Indexes": [
{
"Type": "MinMax",
"Keys": ["y"],
"Condition": "(y in [1, +inf))",
"Parts": 5/4,
"Granules": 12/11
},
{
"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": 4/3,
"Granules": 11/10
},
{
"Type": "PrimaryKey",
"Keys": ["x", "y"],
"Condition": "and((x in [11, +inf)), (y in [1, +inf)))",
"Parts": 3/2,
"Granules": 10/6,
"Search Algorithm": "generic exclusion search"
},
{
"Type": "Skip",
"Name": "t_minmax",
"Description": "minmax GRANULARITY 2",
"Parts": 2/1,
"Granules": 6/2
},
{
"Type": "Skip",
"Name": "t_set",
"Description": "set GRANULARITY 2",
"": 1/1,
"Granules": 2/1
}
]
With projections = 1, the Projections key is added. It contains an array of analyzed projections. Each projection is described as JSON with following keys:
Name — The projection name.Condition — The used projection primary key condition.Description — The description of how the projection is used (e.g. part-level filtering).Selected Parts — Number of parts selected by the projection.Selected Marks — Number of marks selected.Selected Ranges — Number of ranges selected.Selected Rows — Number of rows selected.Filtered Parts — Number of parts skipped due to part-level filtering.
Example:
"Node Type": "ReadFromMergeTree",
"Projections": [
{
"Name": "region_proj",
"Description": "Projection has been analyzed and is used for part-level filtering",
"Condition": "(region in ['us_west', 'us_west'])",
"Search Algorithm": "binary search",
"Selected Parts": 3,
"Selected Marks": 3,
"Selected Ranges": 3,
"Selected Rows": 3,
"Filtered Parts": 2
},
{
"Name": "user_id_proj",
"Description": "Projection has been analyzed and is used for part-level filtering",
"Condition": "(user_id in [107, 107])",
"Search Algorithm": "binary search",
"Selected Parts": 1,
"Selected Marks": 1,
"Selected Ranges": 1,
"Selected Rows": 1,
"Filtered Parts": 2
}
]
With actions = 1, added keys depend on step type.
Example:
EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;
[
{
"Plan": {
"Node Type": "Expression",
"Node Id": "Expression_5",
"Expression": {
"Inputs": [
{
"Name": "dummy",
"Type": "UInt8"
}
],
"Actions": [
{
"Node Type": "INPUT",
"Result Type": "UInt8",
"Result Name": "dummy",
"Arguments": [0],
"Removed Arguments": [0],
"Result": 0
},
{
"Node Type": "COLUMN",
"Result Type": "UInt8",
"Result Name": "1",
"Column": "Const(UInt8)",
"Arguments": [],
"Removed Arguments": [],
"Result": 1
}
],
"Outputs": [
{
"Name": "1",
"Type": "UInt8"
}
],
"Positions": [1]
},
"Plans": [
{
"Node Type": "ReadFromStorage",
"Node Id": "ReadFromStorage_0"
}
]
}
}
]
EXPLAIN PIPELINE
Settings:
header — Prints header for each output port. Default: 0.graph — Prints a graph described in the DOT graph description language. Default: 0.compact — Prints graph in compact mode if graph setting is enabled. Default: 1.
When compact=0 and graph=1 processor names will contain an additional suffix with unique processor identifier.
Example:
EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;
(Union)
(Expression)
ExpressionTransform
(Expression)
ExpressionTransform
(Aggregating)
Resize 2 → 1
AggregatingTransform × 2
(Expression)
ExpressionTransform × 2
(SettingQuotaAndLimits)
(ReadFromStorage)
NumbersRange × 2 0 → 1
EXPLAIN ESTIMATE
Shows the estimated number of rows, marks and parts to be read from the tables while processing the query. Works with tables in the MergeTree family.
Example
Creating a table:
CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
INSERT INTO ttt SELECT number FROM numbers(128);
OPTIMIZE TABLE ttt;
Query:
EXPLAIN ESTIMATE SELECT * FROM ttt;
Result:
┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ default │ ttt │ 1 │ 128 │ 8 │
└──────────┴───────┴───────┴──────┴───────┘
EXPLAIN TABLE OVERRIDE
Shows the result of a table override on a table schema accessed through a table function.
Also does some validation, throwing an exception if the override would have caused some kind of failure.
Example
Assume you have a remote MySQL table like this:
CREATE TABLE db.tbl (
id INT PRIMARY KEY,
created DATETIME DEFAULT now()
)
EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'tbl', 'root', 'clickhouse')
PARTITION BY toYYYYMM(assumeNotNull(created))
Result:
┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: `created` Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘
Note
The validation is not complete, so a successful query does not guarantee that the override would not cause issues.
