In ClickHouse, what does EXPLAIN PIPELINE show that EXPLAIN PLAN does not?
answer
- one is logical, one is physical
- the difference is what actually runs on threads
- look for the multiplier next to each operator
- neither of them runs the query
- no EXPLAIN ANALYZE here — the log has the actuals
basics
~20 sEXPLAIN PLAN prints the logical steps of a ClickHouse query — read, filter, aggregate, sort. EXPLAIN PIPELINE prints the physical processors those steps become, each with a multiplier showing how many parallel streams run it, so you can see where parallelism collapses.
solid answer
~50 s`EXPLAIN PLAN` gives you the logical query plan: a tree of steps such as `ReadFromMergeTree`, `Expression`, `Aggregating`, `Sorting`, `Limit`. It answers *what work will be done and in what order*. `EXPLAIN PIPELINE` lowers that plan into the execution pipeline of processors that actually run, and prints each with a `× N` suffix — the number of parallel streams. That is what makes it a tuning tool: you can see that eight `MergeTreeSelect` streams feed eight `AggregatingTransform` instances, and exactly where a `Resize` funnels them down to a single stream for the final merge or sort. Neither form executes the query — ClickHouse has no `EXPLAIN ANALYZE`, so actual rows and timings come from `system.query_log` afterwards, or from running the client with trace-level logging. Processor names differ between versions; read the shape and the multipliers, not the exact spelling.
code
sql · 11 linesEXPLAIN PLAN indexes = 1
SELECT domain, count()
FROM hits
WHERE event_date = today()
GROUP BY domain;
EXPLAIN PIPELINE
SELECT domain, count()
FROM hits
WHERE event_date = today()
GROUP BY domain;go deeper
Know that ClickHouse has EXPLAIN, that PLAN shows logical steps and PIPELINE shows what executes, and that neither runs the query. Be able to prefix a SELECT with each and describe the output.
Explain the lowering from plan steps to processors, what the × N multiplier means, and why the final merge or sort narrows to one stream. Know that actual numbers come from system.query_log, not EXPLAIN.
Show a diagnosis routine: check pruning with indexes = 1, then look for the point where the pipeline collapses to a single stream, then confirm against read_rows and ProfileEvents from the log before changing anything.
Own the standard: teams should be able to produce a plan, a pipeline and the log row for any query they escalate. Decide how much profiling telemetry is worth its overhead across the fleet and what the escalation evidence bar is.
## The EXPLAIN family in ClickHouse ClickHouse's `EXPLAIN` has several modes and they answer different questions: - `EXPLAIN AST` — the raw parse tree; useful almost only when debugging the parser. - `EXPLAIN SYNTAX` — the query after rewriting, which is how you see what the optimizer did to your text. - `EXPLAIN QUERY TREE` — the analyzed query tree in versions that use the newer analyzer. - `EXPLAIN PLAN` — the logical plan of steps. - `EXPLAIN PIPELINE` — the physical pipeline of processors. - `EXPLAIN ESTIMATE` — a compact estimate of the parts, rows and marks a MergeTree read will touch. The two you use daily are `PLAN` and `PIPELINE`, and knowing which one answers your question is the point of the interview. ## What PLAN gives you `EXPLAIN PLAN` prints a tree of logical steps, output at the top and sources at the bottom: ```sql EXPLAIN PLAN SELECT domain, count() FROM hits WHERE event_date = today() GROUP BY domain; ``` yields something in the shape of `Expression → Aggregating → Expression → ReadFromMergeTree`. Options make it much more informative: `EXPLAIN PLAN indexes = 1` adds, under the read step, which key conditions were applied and how many parts and granules survived them — the fastest way to confirm that a filter actually eliminated data rather than being evaluated row by row. `actions = 1` expands the expressions each step computes, and `header = 1` prints the column names and types flowing between steps. What PLAN cannot tell you is *how many threads* will run any of that. ## What PIPELINE gives you ClickHouse executes queries as a graph of **processors** — small units that pull blocks of columns in, transform them, and push blocks out. `EXPLAIN PIPELINE` prints that graph. A typical aggregation looks roughly like: ``` (Expression) ExpressionTransform × 8 (Aggregating) Resize 8 → 8 AggregatingTransform × 8 StrictResize 8 → 8 (Expression) ExpressionTransform × 8 (ReadFromMergeTree) MergeTreeSelect × 8 0 → 1 ``` Read it bottom-up: eight reading streams pull granules from the MergeTree parts, eight expression transforms evaluate projections and filters, eight aggregating transforms each build their **own** hash table, and the arrows show where streams are resized. The parenthesised names are the plan steps the processors came from, so you can line the pipeline up against the plan. ## Reading the × N The multiplier is the answer to "is this query actually parallel?". Three patterns are worth recognising: 1. **× 1 at the read step** on a big table means the engine found only one range of marks to split — often because the query touches a single small part, or because a `LIMIT` with reading in primary-key order deliberately serialised the read. 2. **A `Resize N → 1`** marks the point where parallelism ends. Final sorting, a global `LIMIT`, and the last merge of aggregation states are natural single-stream points; if that funnel sits low in the pipeline, most of the query is running on one thread. 3. **N equal to `max_threads`** confirms the setting took effect. Change `max_threads` in a `SETTINGS` clause and re-run `EXPLAIN PIPELINE` to see the multipliers move. `EXPLAIN PIPELINE graph = 1` emits the graph in DOT format for rendering, and `compact = 0` expands processors that are otherwise collapsed. ## What EXPLAIN never gives you No mode executes the query. There is no `EXPLAIN ANALYZE` in ClickHouse, so EXPLAIN output contains no actual row counts, no timings and no memory figures. Estimates come from `EXPLAIN ESTIMATE`; actuals come from `system.query_log` (`read_rows`, `query_duration_ms`, `ProfileEvents`), from `system.query_thread_log` when per-thread logging is enabled, or from running the query in the client with trace-level logging, which prints the parts, marks and ranges selected as the query runs. ## Distributed queries EXPLAIN on a query over a `Distributed` table describes the plan on the node you asked, where the remote work appears as a single read-from-remote step. It does not expand into the shards' own pipelines; to see those you run EXPLAIN against the local table on a shard. ## Practical routine Start with `EXPLAIN PLAN indexes = 1` to check that filters prune. If the query reads a sane amount of data and is still slow, switch to `EXPLAIN PIPELINE` and look for the multiplier collapsing. Then confirm the theory with the numbers in `system.query_log` — the plan tells you what should happen, the log tells you what did.
- You see Resize 8 → 1 near the bottom of the pipeline. Why does that matter?It means parallelism ends early: everything above that point runs on a single stream. Low in the pipeline it usually indicates the query is serialised almost immediately — a single-stream read, or an operator that cannot be parallelised. Funnels near the top are normal, because the final sort, the global LIMIT and the last merge of aggregation states are inherently single-stream.
- If EXPLAIN gives no timings, how do you get the actual numbers for a query?Run the query, then read its row in `system.query_log`: `query_duration_ms`, `read_rows`, `read_bytes`, `memory_usage` and the `ProfileEvents` map with counters such as `SelectedParts` and `SelectedMarks`. Enabling per-thread logging adds `system.query_thread_log`, and the sampling profiler writes stacks to `system.trace_log`. Running the client with trace-level logging prints the selected parts and marks live.
- Which EXPLAIN mode do you reach for first when a query reads far more data than it should?`EXPLAIN PLAN indexes = 1`. It shows the key conditions applied at the read step and how many parts and granules survived them, which immediately separates "the filter did not prune" from "the filter pruned fine and the work is downstream". `EXPLAIN ESTIMATE` is a quicker, coarser version of the same signal.
The plan is the recipe — chop, sear, reduce, plate. The pipeline is the kitchen line: eight cooks chopping in parallel, then everything funnelling to one plating station at the pass.
saying these in an interview costs you the question
- Thinks EXPLAIN executes the query and reports real timings
- Looks for EXPLAIN ANALYZE, which ClickHouse does not have
- Reads the × N as a cost or a row estimate
- Believes EXPLAIN PLAN shows how many threads will run
- Expects EXPLAIN over a Distributed table to expand each shard's pipeline