Reading EXPLAIN in ClickHouse

EXPLAIN shows what ClickHouse is going to do with a query without running it. There are several kinds, one per stage of query processing. You rarely need all of them; knowing which stage answers your question is the skill.

The examples use this table with a million rows:

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT toDateTime('2026-01-01 00:00:00') + number, number % 1000,
       ['view', 'click', 'buy'][number % 3 + 1], concat('/p/', toString(number % 50)),
       number % 5000, ['US', 'DE', 'TR', 'JP'][number % 4 + 1]
FROM numbers(1000000);

The stages

EXPLAIN kind	What it shows	Use it to
AST	the parsed syntax tree	debug parsing oddities
SYNTAX	the query text after syntax-level rewrites	see what the server really runs
QUERY TREE	the analyzer’s tree, with resolved types	debug name resolution and types
PLAN (default)	the logical query plan	understand the steps and index use
PIPELINE	the physical, parallel execution pipeline	check parallelism
ESTIMATE	rows, parts and marks that will be read	gauge the cost cheaply

PLAN: the default

EXPLAIN
SELECT country, count() FROM events WHERE event_type = 'buy' GROUP BY country;

Expression ((Project names + Projection))
  Aggregating
    Expression (Before GROUP BY)
      Expression ((WHERE + Change column names to column identifiers))
        ReadFromMergeTree (default.events)

Read it from the bottom up: data flows from ReadFromMergeTree through the filter and the pre-aggregation expression into Aggregating. Each indentation level is one processing step.

Is the index used? indexes = 1

This is the most useful flag. It shows how many parts and granules survive each index:

EXPLAIN indexes = 1
SELECT count() FROM events
WHERE event_type = 'buy' AND event_time > '2026-01-05 00:00:00';

        ReadFromMergeTree (default.events)
        Indexes:
          PrimaryKey
            Keys:
              event_type
              event_time
            Condition: and((event_time in [1767560401, +Inf)), (event_type in ['buy', 'buy']))
            Parts: 1/1
            Granules: 27/123
            Search Algorithm: binary search

Granules: 27/123 says the primary key reduced the read from 123 granules to 27. If you see 123/123 for a query that should be selective, your WHERE does not match the primary key prefix. Skip indexes appear in the same block under Skip, with their own part and granule counts.

The same plan also shows ReadFromPreparedSource (_exact_count_projection): for a plain count() ClickHouse answers from part metadata instead of reading columns, and the plan tells you so.

Other PLAN flags

• header = 1: the columns and types each step outputs.
• actions = 1: the actual expression nodes. Useful to see a function being evaluated more than you thought.
• description = 0 and json = 1: compact or machine-readable output.

EXPLAIN actions = 1 SELECT country, count() FROM events GROUP BY country;

Expression ((Project names + Projection))
Actions: INPUT :: 0 -> count() UInt64 : 0
         INPUT : 1 -> __table1.country LowCardinality(String) : 1
         ALIAS __table1.country :: 1 -> country LowCardinality(String) : 2
Positions: 2 0
  Aggregating
  Keys: __table1.country
  Aggregates:
      count()
        Function: count() → UInt64
        Arguments: none
  ...
      ReadFromMergeTree (default.events)
      ReadType: Default
      Parts: 1
      Granules: 123

The __table1.country names are the analyzer’s internal column identifiers.

SYNTAX and QUERY TREE

EXPLAIN SYNTAX prints the query after syntax-level optimisations, as text you can paste back into a client:

EXPLAIN SYNTAX SELECT count() FROM events WHERE country = 'US' OR country = 'DE';

SELECT count()
FROM events
WHERE (country = 'US') OR (country = 'DE')

EXPLAIN QUERY TREE shows the analyzer’s resolved tree. Every column has a resolved type and a source:

QUERY id: 0
  PROJECTION COLUMNS
    country LowCardinality(String)
    count() UInt64
  PROJECTION
    LIST id: 1, nodes: 2
      COLUMN id: 2, column_name: country, result_type: LowCardinality(String), source_id: 3
      FUNCTION id: 4, function_name: count, function_type: aggregate, result_type: UInt64
  JOIN TREE
    TABLE id: 3, alias: __table1, table_name: default.events
  GROUP BY
    LIST id: 5, nodes: 1
      COLUMN id: 6, column_name: country, result_type: LowCardinality(String), source_id: 3

Reach for this one when an alias, a join column or an implicit cast is not behaving: it tells you what each name resolved to. See Moving to the new analyzer.

AST

EXPLAIN AST SELECT 1 + 2;

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Function plus (children 1)
     ExpressionList (children 2)
      Literal UInt64_1
      Literal UInt64_2

Mostly of interest to people writing parser extensions or chasing a syntax edge case.

PIPELINE: parallelism

EXPLAIN PIPELINE SELECT country, count() FROM events GROUP BY country;

(Expression)
ExpressionTransform × 6
  (Aggregating)
  Resize 6 → 6
    AggregatingTransform × 6
      (Expression)
      ExpressionTransform × 6
        (ReadFromMergeTree)
        MergeTreeSelect(pool: ReadPool, algorithm: Thread) × 6 0 → 1

The × 6 is the number of parallel streams (it depends on your cores and max_threads). A pipeline stuck at × 1 where you expected many is a reason a query is slower than your hardware could make it. Options: compact (default 1), graph = 1 (Graphviz DOT output you can render), header = 1.

ESTIMATE: cost before running

EXPLAIN ESTIMATE SELECT * FROM events WHERE event_type = 'buy';

database  table   parts  rows    marks
default   events  1      335872  41

It gives the parts, rows and marks that the primary key says must be read, without reading the data. Run it on a huge production table before you commit to a query shape. The row count is an upper bound taken from granules, not an exact count of matches.

A short workflow

1. Query is slow: EXPLAIN indexes = 1. Did the primary key prune? If not, fix the filter or the key.
2. Query returns the wrong thing: EXPLAIN SYNTAX, then EXPLAIN QUERY TREE.
3. Query is selective but CPU-bound on one core: EXPLAIN PIPELINE.
4. Not sure the query is safe to run: EXPLAIN ESTIMATE.