When a query is slow and system.query_log has told you it is CPU-bound rather than IO-bound, the next question is which stage of execution burns the time. ClickHouse EXPLAIN PIPELINE answers that question. It prints the physical execution graph — the processors ClickHouse will actually run, with their parallelism — and it is the fastest way to spot a single-threaded sort, an oversized join build, or a resize boundary throttling an otherwise parallel plan.
This guide covers the seven-step workflow ChistaDATA engineers use to read ClickHouse EXPLAIN PIPELINE output on production escalations, plus how to pair it with system.processors_profile_log so you reason from measured elapsed time per processor instead of guesswork.
The ClickHouse EXPLAIN PIPELINE Workflow at a Glance
The diagram above compresses the seven steps into four phases, and the sequence is deliberate. Phase one establishes a baseline, because a pipeline that faithfully implements a bad logical plan is still a bad query. Phase two isolates the choke point: read the processor names, hunt the stages annotated x 1, and expand the graph when the flat text output stops being legible. Phase three replaces suspicion with evidence, then matches that evidence against the handful of shapes every ClickHouse fleet eventually produces. Phase four remediates and verifies.
Read the top row left to right and the bottom row right to left; the arrows follow the order an engineer actually works in, not the order in which processors execute. Each card also carries the single command or system table that closes out its step, so the diagram doubles as a runbook you can keep open in a second window during an incident.
Teams that skip a phase almost always skip the third one, and that is where most wasted effort begins: a plausible-looking serial stage gets optimised for an afternoon before anyone checks whether it accounted for two percent of runtime. A ClickHouse EXPLAIN PIPELINE graph tells you what could be slow; only the profile log tells you what was slow. Keep both artefacts side by side and the investigation stays honest.
What ClickHouse EXPLAIN PIPELINE Actually Shows
ClickHouse executes queries as a pipeline of processors: small units like MergeTreeSelect, ExpressionTransform, AggregatingTransform, and Resize, connected by ports and scheduled across threads. EXPLAIN PLAN shows the logical steps; ClickHouse EXPLAIN PIPELINE shows the physical processor graph, including how many parallel instances of each processor will run — the x N suffix in the output.
That distinction matters because most real bottlenecks are physical: a plan can look perfectly reasonable while one stage silently collapses to a single thread. The parallelism annotations are the payload.
EXPLAIN PIPELINE
SELECT
user_id,
count() AS events
FROM analytics.events
WHERE event_date >= today() - 7
GROUP BY user_id
ORDER BY events DESC
LIMIT 100;Step 1: Baseline the Logical Plan First
Run EXPLAIN PLAN (optionally with indexes = 1) before ClickHouse EXPLAIN PIPELINE. If the logical plan is already wrong — no granule pruning, an unexpected full sort, a subquery that should have been rewritten — fix that first. Pipeline analysis on a broken plan optimizes the wrong thing.
EXPLAIN PLAN indexes = 1 SELECT user_id, count() FROM analytics.events WHERE event_date >= today() - 7 GROUP BY user_id;
Step 2: Read the Processor Names
Each line of ClickHouse EXPLAIN PIPELINE output is a processor class. A working vocabulary covers most graphs you will ever read:
| Processor | Role | Bottleneck signal |
|---|---|---|
| MergeTreeSelect / MergeTreeThread | Reads granules from parts | Low parallelism here means few parts/marks selected — usually good |
| ExpressionTransform | Computes columns, functions, filters | Heavy UDF or regex work shows up here |
| AggregatingTransform | Builds hash tables for GROUP BY | Memory growth, spill triggers |
| MergingAggregatedTransform | Combines partial aggregates | Often x1 — the classic serial choke point |
| MergeSortingTransform / MergingSortedTransform | Sorts and merges sorted streams | Large ORDER BY without LIMIT lands here |
| Resize / StrictResize | Rebalances streams between stages | A narrowing Resize (32 → 1) serializes everything after it |
| JoiningTransform / FillingRightJoinSide | Probes and builds join sides | Build-side size dictates memory and stalls |
Step 3: Hunt the x1 Stages
Scan the graph for processors annotated x 1 that sit between highly parallel stages. A pipeline reading with MergeTreeSelect x 32 that funnels into MergingAggregatedTransform x 1 will spend most of its wall time in that final single-threaded merge when key cardinality is high.
Not every x 1 is a problem — a LIMIT-terminated stream is naturally serial at the end. The question ClickHouse EXPLAIN PIPELINE lets you ask precisely is: how much data crosses the serial boundary? Millions of grouped states crossing into one thread is the smoking gun.
Step 4: Use graph and compact Modifiers
For complex queries, the flat text output gets hard to follow. Two modifiers help: graph = 1 emits DOT format you can render with Graphviz, and compact = 0 expands every parallel instance so you can see exact port wiring:
EXPLAIN PIPELINE graph = 1, compact = 0 SELECT ...; -- render locally: -- clickhouse-client -q "EXPLAIN PIPELINE graph=1 SELECT ..." | dot -Tsvg > pipeline.svg
Step 5: Confirm with system.processors_profile_log
ClickHouse EXPLAIN PIPELINE predicts the shape of execution; system.processors_profile_log (available since ClickHouse 22.x, enabled via log_processors_profiles = 1) records what actually happened — elapsed, input/output rows, and crucially need_data vs work time per processor. This is the difference between suspecting a bottleneck and measuring one:
SELECT
name,
count() AS instances,
sum(work_elapsed_us) / 1000 AS work_ms,
sum(need_data_elapsed_us) / 1000 AS waiting_for_input_ms,
sum(output_rows) AS rows_out
FROM system.processors_profile_log
WHERE query_id = ''
GROUP BY name
ORDER BY work_ms DESC;A processor with huge work_ms is your bottleneck. A processor with huge waiting_for_input_ms is starved by something upstream — walk one stage back and repeat.
Step 6: Recognize the Classic Patterns
The serial aggregation merge
High-cardinality GROUP BY funneling into a lone merging stage. Fixes: pre-aggregate with a materialized view, reduce key cardinality, or verify two-level aggregation is engaging (it usually does automatically past a threshold).
The full sort that should be a heap
ORDER BY without LIMIT materializes a full sort in MergeSortingTransform. If the query sorts by the table key, confirm optimize_read_in_order = 1 took effect — in ClickHouse EXPLAIN PIPELINE output you will see the sort replaced by cheap merging of already-sorted streams.
The oversized join build side
A hash join builds its right side first; if the pipeline stalls in FillingRightJoinSide, the right table is too large. Swap sides, pre-filter, or move to join_algorithm = 'grace_hash' (since 22.x) for out-of-core behavior.
The narrowing Resize
A Resize 32 → 1 mid-graph usually means a stage ahead demanded a single stream — a window function without partitioning, or a FINAL modifier. Restructure the query so parallelism survives to the end.
Step 7: Fix, Re-Explain, Re-Measure
Every remediation ends the same way: rerun ClickHouse EXPLAIN PIPELINE to confirm the graph changed as intended, then rerun the query and compare system.processors_profile_log and system.query_log entries before and after. Keep both artifacts in the incident ticket; the paired graphs are the clearest evidence a fix worked that you can show a stakeholder.
As always: validate schema or settings changes in staging before production, stage them reversibly, and keep your backup and DR posture current before any DDL.
A Worked ClickHouse EXPLAIN PIPELINE Example
An illustrative composite from support engagements — figures are illustrative, not benchmarks. A funnel query aggregating 600 million events by session took 90 seconds despite healthy granule pruning. The ClickHouse EXPLAIN PIPELINE graph showed AggregatingTransform x 48 flowing into MergingAggregatedTransform x 1.
system.processors_profile_log confirmed it: the merging stage accounted for the large majority of total work time, with upstream stages mostly waiting to hand off output. Session-level cardinality was tens of millions of keys, so the single-threaded merge dominated.
The fix was structural, not a setting: a SummingMergeTree materialized view maintaining per-session partial states at insert time, so the interactive query aggregated a few million pre-reduced rows instead of six hundred million raw ones. The new ClickHouse EXPLAIN PIPELINE graph showed the merge stage handling two orders of magnitude fewer states, and p95 dropped from tens of seconds into sub-second territory on the same hardware.
Settings That Change the ClickHouse EXPLAIN PIPELINE Graph
The same statement can produce two very different graphs in two different sessions, and the difference is almost never the data. Parallelism annotations are derived from the settings active at explain time, so an ad-hoc session with defaults will happily show you a pipeline that production never runs. Before you trust a graph, know which of these settings your workload profile changes.
| Setting | Effect on the pipeline | Why it matters |
|---|---|---|
max_threads | Caps how many parallel instances of each processor are created | The x N you read is a function of this setting, not of the data volume |
max_insert_threads | Controls fan-out on the writing side of INSERT … SELECT | Serial insert stages are easy to overlook in an otherwise parallel graph |
optimize_read_in_order | Replaces a full sort with a merge of already-sorted streams | Removes MergeSortingTransform entirely when the ORDER BY matches the table key |
distributed_aggregation_memory_efficient | Streams partial aggregates from shards instead of buffering them | Moves where the merge happens and how much memory it needs |
join_algorithm | Selects hash, partial_merge, full_sorting_merge or grace_hash | Each algorithm produces different processors and a different memory profile |
parallel_replicas settings | Spread reads for one query across replicas | Extra fan-out appears at the source stage and changes the whole shape |
log_processors_profiles | Populates system.processors_profile_log for executed queries | Without it, step five of the workflow has no data to work with |
The practical rule is short: reproduce the production settings profile in your session, or run ClickHouse EXPLAIN PIPELINE as the same user and profile the application uses. Comparing a 32-thread ad-hoc graph with an 8-thread production reality is how teams end up removing a bottleneck that only ever existed on their own laptop.
A 10-Minute ClickHouse EXPLAIN PIPELINE Triage Checklist
On a live escalation there is rarely time for a leisurely read. This is the compressed loop ChistaDATA engineers run first, and it usually surfaces the dominant cost inside ten minutes.
- Capture the
query_idfromsystem.query_logand confirm the query is CPU-bound rather than blocked on IO, merges or locks. - Run
EXPLAIN PLAN indexes = 1and check that partition and primary key pruning are actually happening. - Run ClickHouse EXPLAIN PIPELINE using the production settings profile, not your comfortable default session.
- Note the maximum parallelism reached anywhere in the graph, then note every stage that falls below it.
- Flag the widest narrowing first — a 32-to-1 transition matters far more than an 8-to-4 one.
- Enable
log_processors_profiles = 1, re-run the query, and rank processors by work time. - Compare that ranking against your flagged stages; where they disagree, trust the measurement.
- Check whether the top processor is busy or merely starved, and walk one stage upstream if it is starved.
- Write down the hypothesis and the graph change you expect before you touch anything.
- Apply the fix in staging, re-explain, re-measure, and only then promote it to production.
Ten items sounds like a lot until you have run the loop a few times; most of it is mechanical. Writing the hypothesis down at step nine is the part that matters most, because it is what stops an incident from dissolving into an afternoon of speculative setting changes that nobody can later explain.
When ClickHouse EXPLAIN PIPELINE Is Not the Right Tool
Pipeline analysis answers one question well — how execution is shaped — and several other questions badly. If a query is waiting on merges, competing for the background pool, or queued behind a mutation, the graph will look perfectly healthy while wall time stays terrible. Those problems live in system.merges, system.mutations and system.metrics, not in the processor graph.
The same caution applies to IO-bound work. A pipeline stalled on a cold page cache or a saturated disk shows up as processors waiting for data, which looks identical to a stage starved by upstream serialisation. Telling the two apart needs system.asynchronous_metrics and host-level IO statistics read alongside the ClickHouse EXPLAIN PIPELINE output.
Distributed queries deserve particular care. On a Distributed table the graph describes the initiator’s local pipeline plus the remote read stages; it does not unfold what each shard does internally. When a distributed query is slow, explain the underlying local table on a representative shard as well, or the most expensive part of the execution stays invisible.
Finally, client-side and network effects — oversized result sets, an expensive output format, a client that reads slowly — never appear in the graph at all. If the pipeline looks balanced and the profile log reports modest work time, stop tuning the query and start measuring what happens after the last processor hands off its rows.
Common Mistakes When Reading the Output
Three recurring misreads waste investigation time. First, treating processor count as goodness — more parallel instances only help if data volume justifies them; tiny queries run slower with maximum fan-out. Second, ignoring the direction of the graph: output is bottom-up in text mode, and misreading order leads to fixing the wrong stage.
Third, analyzing on an idle node and deploying to a saturated one. Parallelism in ClickHouse EXPLAIN PIPELINE reflects max_threads at explain time; a profile that caps threads in production will produce a different graph than your ad-hoc session. Always explain with the same settings profile the workload uses.
Key Takeaways
- ClickHouse EXPLAIN PIPELINE shows the physical processor graph with per-stage parallelism — the
x Nannotations are the payload. - Baseline with
EXPLAIN PLAN indexes = 1first; pipeline tuning on a broken logical plan is wasted effort. - Serial stages (
x 1) between parallel ones are the prime suspects; quantify them withsystem.processors_profile_log(22.x+). - Most fixes are structural — pre-aggregation, join-side ordering, read-in-order — not settings.
- Close the loop: re-explain and re-measure with the same settings profile production uses.
Frequently Asked Questions
What is the difference between EXPLAIN PLAN and ClickHouse EXPLAIN PIPELINE?
EXPLAIN PLAN shows logical steps (reads, filters, aggregations); ClickHouse EXPLAIN PIPELINE shows the physical processor graph with parallelism per stage. Use PLAN to verify what will run, PIPELINE to see how it will run.
How do I see actual per-stage timing, not just the graph?
Enable log_processors_profiles = 1 and query system.processors_profile_log for the query_id — it records work time, wait time, and rows per processor since ClickHouse 22.x.
Why does my pipeline show x 1 on the final stage?
Final merging, LIMIT handling, and result serialization are naturally serial. It is only a problem when large data volumes cross that serial boundary — measure with processors_profile_log before restructuring.
Does ClickHouse EXPLAIN PIPELINE execute the query?
No — it only builds and prints the pipeline. That makes it safe to run against production tables at any time.
Can I run ClickHouse EXPLAIN PIPELINE on a Distributed table?
Yes, but the output describes the initiator’s pipeline and the remote read stages rather than the full execution on every shard. For distributed queries, also explain the underlying local table on a representative shard so the per-shard work becomes visible.
Why does the same query produce a different pipeline on two servers?
Parallelism is derived from settings such as max_threads at explain time and from the number of parts and marks the query selects. A different settings profile, a different replica state, or a recent merge will all change the graph for an identical SQL statement.
What does need_data time tell me that work time does not?
Work time is what a processor spent doing its own job; need_data time is what it spent idle waiting for input. A processor with a large wait figure is a symptom rather than a cause, so walk one stage upstream and repeat the comparison there.
Reading pipelines is a skill; reading them at 3 a.m. during an incident is a service. ChistaDATA provides ClickHouse performance consulting, 24x7x365 support with a 15-minute S1 SLA, remote DBA, and managed services on 100% open-source ClickHouse. If a query graph is costing you sleep, talk to a ClickHouse engineer — or continue with our guide to slow query troubleshooting with system.query_log and the official EXPLAIN reference. Deeper internals live in the ClickHouse architecture documentation.
