How a Query Becomes an Execution Plan

The mental model

Parse, Analyze, Plan, and Execute are still the right four questions. What this lesson adds is that each of the first three produces one specific, inspectable data structure — and "Execute" isn't a stage at all, it's a different kind of thing: not a step in a sequence, but a graph that runs.

Parse produces an AST. Exactly as Module 2 described: grammar only, nothing checked against real tables or columns. EXPLAIN AST dumps it directly.

Analyze produces a query tree, not just a validated AST. This is the first real addition. ClickHouse's Analyzer takes the AST and builds a query tree — buildQueryTree(ASTPtr, ContextPtr), a semantically resolved structure where every identifier is tied to a real column, * is expanded, aliases are substituted, and every expression has a concrete type. EXPLAIN QUERY TREE dumps this structure directly — a QUERY node with resolved COLUMN nodes carrying their result_type and a source_id pointing at exactly which table they came from. This matters because it's a genuinely different architecture from ClickHouse's older analysis path: ClickHouse's own docs describe the new Analyzer's defining change as that "query validation takes place before the optimization step," a shift from the previous system, which applied optimizations before full validation. EXPLAIN SYNTAX sits adjacent to this: it shows the AST after running the query tree through its resolution and optimization passes and converting it back to AST form — which is why a three-way self-join's aliases come out rewritten as __table1, __table2, __table3 once run_query_tree_passes = 1 is set, even though nothing about the query's meaning changed.

Plan produces a query plan — logical, still untouched by data. The Planner class takes the resolved query tree and builds a QueryPlan: a tree of IQueryPlanStep nodes (ReadFromMergeTree, FilterStep, AggregatingStep, SortingStep, LimitStep, and others) — the same tree EXPLAIN shows you by default, exactly as Module 2 described.

"Execute" is really two things: building a pipeline, then running it. QueryPlan::buildQueryPipeline(...) walks the plan tree and calls each step's updatePipeline(), and each step turns itself into one or more concrete processors — IProcessor objects with input and output ports that pull blocks in, do work, and push blocks out. The result is a query pipeline — not a sequence of stages that finish one after another, but a graph, and EXPLAIN PIPELINE shows you that graph directly. A PipelineExecutor then runs it across worker threads: each processor's prepare() method reports its own status — NeedData, PortFull, Ready, Async, or Finished — and the executor calls work() on whichever processors are Ready, over and over, until the whole graph reports Finished.

This is the single most important correction this lesson makes to the Module 2 model: a plan is a tree with one order; a pipeline is a graph with no single order at all. During execution, a ReadFromMergeTree source, several ExpressionTransforms, and an AggregatingTransform aren't "stages" that run one after another — they're independent processors, several of them alive and doing work at the same instant, only blocking on each other when a port genuinely has no data to pull or nowhere to push it. "First it reads, then it filters, then it aggregates" is the plan's story. The pipeline's story is closer to: dozens of small workers, each watching its own ports, all running whenever they have something to do.

Why the pipeline has multiple copies of the same processor. ClickHouse doesn't run one ReadFromMergeTree and hand its output to one AggregatingTransform — it runs N parallel copies of each, one per processing lane, where N is bounded by max_threads (which defaults to the number of CPU cores available). Resize processors appear in the pipeline specifically to redistribute blocks evenly across lanes when the number of lanes changes between steps, or when some lanes are naturally getting more data than others — "faster lanes effectively help out slower ones, optimizing overall query runtime." And critically: max_threads is an upper bound, not a promise. ClickHouse only allocates as many concurrent read streams as the selected data actually justifies, governed by merge_tree_min_rows_for_concurrent_read (163,840 rows by default) and merge_tree_min_bytes_for_concurrent_read (2,097,152 bytes by default) — a filtered query that only touches a small slice of a table can run with far fewer active lanes than max_threads allows, and nothing about that is visible in EXPLAIN (plan); it only shows up once you look at EXPLAIN PIPELINE's actual × N counts.

Codebase tour: where this lives in ClickHouse's source

Reading a real EXPLAIN PIPELINE end to end

Here's a captured, real EXPLAIN PIPELINE run against a MergeTree table (uk_price_paid_simple, on a 59-core test server), reading from the bottom up — the same direction EXPLAIN (plan) reads in:

    ┌─explain───────────────────────────────────────────────────────────────────────────┐
 1. │ (Expression)                                                                      │
 2. │ ExpressionTransform × 59                                                          │
 3. │   (Aggregating)                                                                   │
 4. │   Resize 59 → 59                                                                  │
 5. │     AggregatingTransform × 59                                                     │
 6. │       StrictResize 59 → 59                                                        │
 7. │         (Expression)                                                              │
 8. │         ExpressionTransform × 59                                                  │
 9. │           (ReadFromMergeTree)                                                     │
10. │           MergeTreeSelect(pool: PrefetchedReadPool, algorithm: Thread) × 59 0 → 1 │
    └───────────────────────────────────────────────────────────────────────────────────┘

Line 10 is the bottom of the graph: 59 independent MergeTreeSelect processors, one per processing lane, each reading its own slice of granules. Line 9's (ReadFromMergeTree) in parentheses isn't a processor — it's the plan step that produced everything below it, kept in the output as a label so you can tie the physical processors back to the logical plan they came from. Resize and StrictResize (lines 4 and 6) both redistribute blocks between groups of processing lanes so that later steps stay evenly fed even if the read lanes didn't all pull the same amount of data — the exact difference between the two isn't important for this lesson's purposes; both exist to rebalance data across lanes, not to change what work happens. And the × 59 on nearly every line is max_threads made visible: this query genuinely ran with 59 concurrent copies of ExpressionTransform and AggregatingTransform, each with its own independent partial-aggregation state, all alive at once.

That parallelism isn't guaranteed, though. On the same table, adding a selective WHERE town = 'LONDON' filter — one that only matches 282 of 3,609 granules — drops the read-side count from 59 to 30, with max_threads unchanged at 59: MergeTreeSelect(pool: PrefetchedReadPool, algorithm: Thread) × 30. Nothing in EXPLAIN (plan) shows you that difference — the logical plan for both queries looks the same shape, just with or without a Filter step. Only EXPLAIN PIPELINE's actual × N counts reveal it.

Debugging walkthrough: "the plan looks fine — so which processor is actually slow?"

Symptom. A GROUP BY event_type aggregation over the course's events table (user_id, event_type, event_time) has gotten noticeably slower after a schema change added a dictGet() call — a lookup against a small "campaign metadata" dictionary — to the SELECT list, enriching each row. EXPLAIN (plan) still shows the expected shape: read, filter, an expression step (where the dictGet() call lives), aggregate, sort, limit. Nothing about the plan itself looks wrong.

Step 1 — confirm the plan's shape, then check whether the pipeline is actually parallel. EXPLAIN (plan) confirms the logical steps are what you'd expect. EXPLAIN PIPELINE is the next question, and it's a different one: are there really N copies of each transform running, matching max_threads, or did this query quietly fall into the low-concurrency case the previous section just walked through? If the × N counts are already lower than expected, that's a real, separate finding worth chasing on its own — but assume here they match max_threads, so the parallelism itself isn't the problem.

Step 2 — the plan and the pipeline shape both look right, so look at what each processor actually did. This is exactly what neither EXPLAIN variant can tell you: they show the graph's shape, not how any processor in it behaved on a specific run. Enable log_processors_profiles and re-run the query:

SELECT event_type, count(), dictGet('campaign_metadata', 'name', event_type) AS campaign
FROM events
WHERE event_time >= '2024-01-15 00:00:00'
GROUP BY event_type, campaign
SETTINGS log_processors_profiles = 1;

Step 3 — query system.processors_profile_log for that query_id, and read elapsed_us against the wait columns together, not elapsed_us alone. The table's own documented example demonstrates the exact interpretive pattern: for a query containing sleep(1), the ExpressionTransform doing the sleeping shows elapsed_us over one million, while every processor downstream of it shows input_wait_elapsed_us over one million instead — they weren't slow, they were blocked waiting on the one processor that was. The same logic applies here: a processor with high elapsed_us did real, slow work; a processor with low elapsed_us but high input_wait_elapsed_us was idle, waiting on whoever feeds it; high output_wait_elapsed_us means its own output port was full — its consumer couldn't keep up with it.

┌─name─────────────────┬─elapsed_us─┬─input_wait_elapsed_us─┬─output_wait_elapsed_us─┐
│ ExpressionTransform   │     842910 │                  3120 │                  210400 │
│ AggregatingTransform  │      41200 │                798300 │                    1800 │
│ MergeTreeSelect       │       9800 │                  4400 │                  831200 │
└───────────────────────┴────────────┴───────────────────────┴──────────────────────────┘

The ExpressionTransform — the one evaluating dictGet() per row — has by far the highest elapsed_us: it's the one doing genuinely slow work, not merely reporting a symptom of something upstream. MergeTreeSelect's huge output_wait_elapsed_us confirms the direction of the bottleneck: the reader has data ready and is stuck waiting for the ExpressionTransform ahead of it to accept it, exactly the PortFull pattern IProcessor's own documentation describes. AggregatingTransform's high input_wait_elapsed_us says the same thing from the other side — it's starved, waiting on the same slow expression step to hand it anything to aggregate. Three processors, three different symptoms, one actual root cause, and the wait-column pattern is what tells you where the root cause sits without guessing.

Step 4 — confirm the hypothesis by tying the processor back to its plan step. system.processors_profile_log carries plan_step_name and plan_step_description columns specifically so a physical processor can be matched back to the logical step that created it. Filtering to the slow ExpressionTransform's plan_step_name confirms it's the expression step containing the dictGet() call, not some other expression step in the same query — closing the loop between "this processor is slow" and "this is the specific part of the query causing it," which is a per-row external dictionary lookup, not the aggregation or the read.

Failure mode gallery

The plan looks parallel; the pipeline isn't. EXPLAIN (plan) never shows stream counts — it's a logical tree, and "how many copies of this step will actually run" is a physical-pipeline question. A query that reads only a handful of granules can silently run with far fewer streams than max_threads, governed by merge_tree_min_rows_for_concurrent_read and merge_tree_min_bytes_for_concurrent_read. The signature: someone tunes max_threads up expecting more parallelism, sees no change, and assumes the setting is broken — when the actual constraint is how much data the query selected, visible only in EXPLAIN PIPELINE's real × N counts, never in the plan.

Blaming the busy-looking neighbor instead of the actual bottleneck. Sorting system.processors_profile_log by elapsed_us alone finds who did the most work — not necessarily who's making the query slow. A processor waiting on a slow neighbor shows near-zero elapsed_us and a large input_wait_elapsed_us or output_wait_elapsed_us instead; skipping those columns and fixating on whichever processor "did the most" is exactly the misread the debugging walkthrough above was built to avoid, and it's the documented teaching point of processors_profile_log's own official example.

A hash join spilling to disk, visible in the plan if you know the notation. EXPLAIN (plan) with actions = 1 shows a join step's chosen algorithm directly — including Algorithm: SpillingHashJoin(HashJoin) when the build side didn't fit comfortably in memory and ClickHouse fell back to a disk-spilling variant of the hash join hash-join (Module 4) described as the default. The failure signature: a join that used to be fast gets progressively slower as one side's table grows, with no error and no obvious plan change — just this one line in EXPLAIN (plan) quietly switching from HashJoin to SpillingHashJoin(HashJoin), trading join speed for a bounded memory footprint exactly as this course's glossary describes.

Mistaking the Query Tree's rewritten form for a bug in the query. EXPLAIN SYNTAX run_query_tree_passes = 1 (or EXPLAIN QUERY TREE) shows identifiers rewritten into an internal form — __table1.number instead of a.number, in the case of a self-joined system.numbers query — because the Analyzer's resolution passes have already run. Someone unfamiliar with this stage can read that rewritten form as evidence the query itself is malformed or that ClickHouse "changed" their query; it's the expected, correct output of a stage that runs on every query, visible specifically because EXPLAIN QUERY TREE and EXPLAIN SYNTAX are two of the only tools that show post-resolution form at all — plain EXPLAIN (plan) never surfaces it.

Hands-on forensics: a diagnostic checklist for a slow query with a clean-looking plan

1. Run EXPLAIN (plan) first. Confirm the logical shape — read, filter, aggregate, sort, limit — is what you'd expect. If it isn't, that's a planning-level problem (a missing index, an unexpected full scan) and you're done before reaching for anything below.
2. Run EXPLAIN PIPELINE next. Check the actual × N counts against max_threads. A lower-than-expected count is a real, separate finding — chase it via the concurrent-read thresholds before assuming the bottleneck is inside any single processor.
3. If the plan and the pipeline's parallelism both look right, enable SETTINGS log_processors_profiles = 1 and re-run the query.
4. Query system.processors_profile_log for that query_id, and read elapsed_us next to input_wait_elapsed_us / output_wait_elapsed_us together — never elapsed_us alone. High elapsed_us means genuine work; high wait times mean "blocked on a neighbor," and which wait column is high tells you which direction the block runs.
5. Match the slow processor's plan_step_name back to the logical plan step that created it, closing the loop between "this specific processor is slow" and "this is the part of the query responsible" — the same join between the physical pipeline and the logical plan EXPLAIN (plan) and EXPLAIN PIPELINE show separately.