Effective ClickHouse query performance tuning is less about magic settings and more about a repeatable diagnostic loop: measure, read the plan, isolate the bottleneck, change one variable, and re-measure. This playbook is written for senior engineers who already run ClickHouse in production and want a disciplined way to turn a slow query into a fast one. Every recommendation for ClickHouse query performance tuning here is version-pinned, because ClickHouse’s optimizer and observability surface change quickly, and a tuning trick that held in one release can behave differently in the next.
We will walk through reading EXPLAIN PIPELINE and EXPLAIN PLAN indexes = 1, diagnosing slow queries from system.query_log, the real mechanics of PREWHERE versus WHERE, why granule pruning fails when your predicate is not aligned with the sort key, and the trade-offs behind max_threads and the memory-limit settings. Illustrative figures are labeled as such; you should always reproduce numbers on your own hardware and data.
Start With Evidence, Not Intuition: system.query_log
The first rule of ClickHouse query performance tuning is to stop guessing. The system.query_log table records one or more rows per executed query and is the authoritative source for what actually happened. It has existed for many major versions, and the core columns discussed here (read_rows, read_bytes, memory_usage, and the ProfileEvents map) have been stable since at least ClickHouse 22.x. Confirm that log_queries is enabled and that the flush interval matches how quickly you need feedback.
A query emits an initial row with type = 'QueryStart' and a final row with type = 'QueryFinish' (or an exception type on failure). For diagnostics you almost always want the finish rows, because that is where the resource accounting lands.
SELECT
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read_bytes_h,
formatReadableSize(memory_usage) AS peak_memory_h,
result_rows,
query
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time >= now() - INTERVAL 1 HOUR
AND query NOT ILIKE '%system.query_log%'
ORDER BY query_duration_ms DESC
LIMIT 20;Interpreting the resource columns during ClickHouse query performance tuning
Read the numbers as a ratio, not in isolation. If read_rows is close to the full table cardinality, the query scanned everything and your primary index did no pruning. If read_bytes is large relative to read_rows, you are pulling wide columns you may not need, and column pruning or a narrower projection is the lever.
The memory_usage column reports peak memory for the query. A value approaching your per-query limit is a warning that the next spike will throw MEMORY_LIMIT_EXCEEDED, and it is one of the first signals to watch during any ClickHouse query performance tuning session.
The ProfileEvents map is where the deeper story lives. It is a Map(String, UInt64), so you index into it by event name. A handful of events explain most slow queries.
SELECT
query_duration_ms,
ProfileEvents['SelectedMarks'] AS selected_marks,
ProfileEvents['SelectedParts'] AS selected_parts,
ProfileEvents['OSReadBytes'] AS os_read_bytes,
ProfileEvents['OSCPUVirtualTimeMicroseconds'] AS cpu_us,
ProfileEvents['RealTimeMicroseconds'] AS real_us
FROM system.query_log
WHERE type = 'QueryFinish'
AND query_id = '00000000-0000-0000-0000-000000000000'
ORDER BY event_time DESC
LIMIT 1;The relationship between SelectedMarks and the total marks in the table is the single most useful signal for index effectiveness. Each mark covers one granule (index_granularity rows, 8192 by default). If your query reads nearly all marks, index analysis pruned nothing, and you should move to EXPLAIN indexes = 1 to find out why.
A high OSReadBytes paired with low CPU time points to an I/O-bound query, while high OSCPUVirtualTimeMicroseconds relative to wall-clock time points to a CPU-bound aggregation or expression evaluation. Knowing which of the two you face keeps your ClickHouse query performance tuning focused on the right resource.
ClickHouse Query Performance Tuning With EXPLAIN indexes = 1
Once query_log tells you a query scanned too much data, EXPLAIN PLAN indexes = 1 tells you why. This form annotates each ReadFromMergeTree step with the indexes that were considered and, crucially, how many parts and granules survived each stage of pruning. It is supported for the MergeTree family.
EXPLAIN indexes = 1
SELECT
count()
FROM events_local
WHERE event_date = '2025-01-15'
AND user_id = 4242;The output lists an index per stage — partition min-max, the partition expression, the PrimaryKey index, and any skip indexes — each with a Parts and Granules figure shown as survivors over the input (for example Granules: 6/10). You want those fractions to shrink aggressively at the PrimaryKey stage. If they do not, your predicate cannot be answered by the sort key.
Two version caveats matter here. Since ClickHouse 25.9, this statement only produces meaningful index output when you disable two caches, because otherwise the plan reflects cached decisions rather than a fresh analysis: run it with SETTINGS use_query_condition_cache = 0, use_skip_indexes_on_data_read = 0.
Separately, the default rendering of EXPLAIN PLAN changed: prior to ClickHouse 26.7 the actions, compact, and pretty options defaulted to 0, whereas from 26.7 onward the pretty, action-annotated form is the default. If you script plan parsing, pin the output shape with explain_query_plan_default = 'legacy' or set the options explicitly.
EXPLAIN indexes = 1
SELECT
count()
FROM events_local
WHERE event_date = '2025-01-15'
AND user_id = 4242
SETTINGS use_query_condition_cache = 0,
use_skip_indexes_on_data_read = 0;EXPLAIN ESTIMATE for quick query performance tuning checks
When you just want the projected read footprint before running anything, EXPLAIN ESTIMATE returns the estimated parts, rows, and marks for MergeTree tables. It is a fast way to confirm whether a WHERE predicate is expected to prune before you pay for a full execution, and a cheap first step in ClickHouse query performance tuning.
EXPLAIN ESTIMATE
SELECT
count()
FROM events_local
WHERE event_date = '2025-01-15';Reading EXPLAIN PIPELINE and EXPLAIN ANALYZE
Where the index plan explains data volume, EXPLAIN PIPELINE explains parallelism. It shows the physical processors that will execute the query and, importantly, their multiplicity — the × N suffix that tells you how many parallel lanes a stage runs with.
EXPLAIN PIPELINE
SELECT
user_id,
count()
FROM events_local
GROUP BY user_id;In the output, transforms such as AggregatingTransform × 8 indicate eight parallel aggregation lanes, and Resize N → 1 nodes mark the points where parallel streams merge back into one. If you expected wide parallelism but see a low multiplier, the query is not fanning out — often because there are too few parts or marks to distribute across threads, or because max_threads is capped lower than you think. Passing header = 1 annotates each port with its columns, which helps when a pipeline branches unexpectedly during ClickHouse query performance tuning.
EXPLAIN ANALYZE: the profiler for query performance tuning
Since ClickHouse 25.x, EXPLAIN ANALYZE actually runs the query, discards the result rows, and annotates the plan tree with measured runtime metrics per step. This is the closest thing to a per-operator profiler that ships in the box. Each step reports rows in and out (with selectivity), bytes flowing through, wall-clock time with its share of execution, and a parallelism figure shown as average over maximum threads.
EXPLAIN ANALYZE
SELECT
user_id,
count()
FROM events_local
GROUP BY user_id;Read the parallelism line carefully. A value near the maximum means the step was well parallelized; a value near 1 means it ran mostly serially, which is your cue to investigate why the workload did not distribute.
The selectivity percentage on each step tells you where filtering actually happens. If a Filter step near the top of the tree is doing most of the reduction, that filtering happened late and read more data than necessary. Because EXPLAIN ANALYZE executes the wrapped query, it is charged against the same quotas and limits as the real query and is not supported in distributed mode, so run it against a local table or a single shard.
PREWHERE vs WHERE: What Actually Gets Read
The distinction between PREWHERE and WHERE is one of the most misunderstood areas of query performance tuning. Semantically the two clauses are identical — they filter the same rows. The difference is entirely in the read strategy.
With prewhere, ClickHouse first reads only the columns needed to evaluate the prewhere expression, applies it, and then reads the remaining columns only for the blocks where the prewhere predicate matched at least one row. When the predicate is selective and touches few columns, this avoids reading large columns for rows that will be discarded anyway.
This optimization is on by default and does not require you to write PREWHERE explicitly. The MergeTreeWhereOptimizer automatically moves eligible parts of your WHERE clause into the prewhere stage; the explicit clause exists only for when you know your data better than the heuristic does. It is controlled by optimize_move_to_prewhere (default on), and it applies only to tables in the MergeTree family.
SELECT
count()
FROM events_local
PREWHERE status_code = 500
WHERE response_body ILIKE '%timeout%';To see what the optimizer moved automatically, enable debug logging in your session and look for the optimizer’s decision line.
SET send_logs_level = 'debug';
SELECT
count()
FROM events_local
WHERE status_code = 500;
-- server log shows: MergeTreeWhereOptimizer: condition "status_code = 500" moved to PREWHEREWhen to override the heuristic in query performance tuning
Manual PREWHERE pays off when a strongly filtering predicate uses a small, cheap-to-read column while the rest of the query needs wide columns. Force that predicate into prewhere and the wide columns are read only for surviving blocks.
Two correctness caveats are important. With the FINAL modifier, prewhere is applied only when both optimize_move_to_prewhere and optimize_move_to_prewhere_if_final are enabled, and because prewhere executes before FINAL, filtering on columns that are not in the sort key can skew FINAL results. Validate the read reduction by comparing the “Processed” bytes in the query footer, or the read_bytes column in query_log, before and after.
Sort-Key Alignment and Why Granule Pruning Fails
Primary index pruning in ClickHouse works on the sparse primary index built from the table’s sorting key. The index stores one entry per granule — the value of the sort key at each mark. When your predicate is a prefix of the sort key, ClickHouse performs a range search over these marks and reads only the granules that can contain matching rows. When it is not, every granule is a potential match and pruning fails, which is exactly the situation where you see SelectedMarks approach total marks in query_log.
Consider a table sorted by (tenant_id, event_date, user_id). A query filtering on tenant_id alone, or on tenant_id and event_date, prunes well because those columns form a left-anchored prefix of the sort key. A query filtering only on user_id cannot prune, because user_id is the third column and its values are not globally ordered within the parts — they are only ordered within each fixed (tenant_id, event_date) group.
CREATE TABLE events_local
(
tenant_id UInt32,
event_date Date,
user_id UInt64,
status_code UInt16,
response_ms UInt32,
response_body String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, user_id)
SETTINGS index_granularity = 8192;The mechanics of prefix ordering have been stable across the MergeTree family for many years, so this reasoning is not version-sensitive. What changes between releases is the surrounding tooling — the analysis caches noted earlier, and skip-index behavior — not the fundamental rule that pruning follows the sort-key prefix.
Fixing pruning failures during query performance tuning
There are three durable fixes. First, reorder the sorting key so the most selective, most frequently filtered column sits in the prefix — but weigh this against compression, since ClickHouse compresses best when low-cardinality columns lead.
Second, add a data-skipping index (for example minmax or bloom_filter) on the out-of-prefix column so that granules can be skipped by a secondary structure even though the primary index cannot help. This is one of the highest-leverage moves in ClickHouse query performance tuning when a reorder is too disruptive.
ALTER TABLE events_local
ADD INDEX idx_user_id user_id TYPE minmax GRANULARITY 4;
ALTER TABLE events_local
MATERIALIZE INDEX idx_user_id;Third, when a single physical order cannot serve two very different access patterns, use a projection with its own ORDER BY so that queries filtering on the alternate key get their own pruning-friendly ordering. Since ClickHouse 24.x, EXPLAIN indexes = 1 and the projections option surface whether a projection was analyzed and used for part-level filtering, which lets you confirm the projection is actually doing work rather than sitting idle.
ALTER TABLE events_local
ADD PROJECTION proj_by_user
(
SELECT *
ORDER BY user_id
);
ALTER TABLE events_local
MATERIALIZE PROJECTION proj_by_user;max_threads and Memory Settings Trade-offs
Parallelism and memory are the two levers most often reached for in ClickHouse query performance tuning, and the two most often misused. The max_threads setting controls how many threads a single query uses for its main processing stages; it defaults to the number of physical CPU cores.
Raising it can shorten wall-clock time for a CPU-bound scan or aggregation, but only up to the point where there is enough work — recall that the effective parallelism of a step is bounded by the lesser of max_threads and the number of tasks (roughly, granule ranges) available to distribute. Beyond that point, extra threads add scheduling overhead without benefit.
SELECT
user_id,
count()
FROM events_local
WHERE tenant_id = 7
GROUP BY user_id
SETTINGS max_threads = 16;The cost of high max_threads is memory and contention. Each thread maintains its own state — hash tables for aggregation, buffers for sorting — so peak memory tends to scale with thread count. On a busy cluster serving many concurrent queries, a high per-query max_threads can starve other queries of cores and inflate aggregate memory pressure.
The right value is workload-dependent: raise it for a few heavy analytical queries running in isolation, lower it for high-concurrency serving where predictability matters more than single-query latency. This is a classic ClickHouse query performance tuning trade-off between latency and throughput.
Memory limits and spilling in query performance tuning
The per-query ceiling is max_memory_usage (0 means unlimited within server constraints), and it is enforced against the same peak that memory_usage reports in query_log. When an aggregation or sort risks exceeding memory, ClickHouse can spill to disk if you enable the external variants: max_bytes_before_external_group_by for GROUP BY and max_bytes_before_external_sort for ORDER BY. Setting these to roughly half of max_memory_usage is a common starting point, so that the query has headroom to merge spilled data without hitting the hard limit.
SELECT
tenant_id,
user_id,
count() AS event_count
FROM events_local
GROUP BY
tenant_id,
user_id
ORDER BY event_count DESC
SETTINGS max_memory_usage = 20000000000,
max_bytes_before_external_group_by = 10000000000;Spilling trades latency for the ability to complete: a query that spills is slower than one that fits in memory but faster than one that fails outright. The trade-off, therefore, is not “more memory is always better” but rather choosing between failing fast, spilling, and provisioning more RAM.
Decide based on whether the query is interactive or batch, and whether occasional failure is acceptable. As an illustrative example only, a large GROUP BY that peaks at roughly 25 GB in memory might complete in the low tens of seconds when it fits, and take noticeably longer when forced to spill — reproduce these figures on your own data before drawing conclusions.
Putting the Query Performance Tuning Loop Together
A complete diagnostic pass ties these tools into a sequence. Pull the slow query from system.query_log and read its read_rows, read_bytes, memory_usage, and ProfileEvents['SelectedMarks']. If marks read are close to the total, run EXPLAIN indexes = 1 (with the analysis caches disabled on 25.9+) to see which stage failed to prune, and check whether your predicate matches the sort-key prefix.
If data volume is fine but wall-clock time is high, run EXPLAIN ANALYZE and inspect the per-step time and parallelism to find the serial or CPU-bound stage. Use PREWHERE to cut column reads, adjust the sort key, skip indexes, or projections to restore pruning, and tune max_threads and the memory settings last, once the data-read problem is solved. Changing thread and memory settings before fixing a pruning problem only masks the symptom, and disciplined ClickHouse query performance tuning always fixes the read path first.
For teams that want a second set of experienced eyes on this loop, ChistaDATA offers senior-led ClickHouse Consulting engagements focused on production performance, and 24×7 ClickHouse Support with enterprise SLAs for teams running mission-critical clusters. The official ClickHouse EXPLAIN documentation and the PREWHERE reference are worth keeping open while you work, since both track behavior changes release by release.
Before You Change Anything in Production
Every change described here — reordering a sort key, adding a skip index or projection, or raising max_threads and memory limits — has cluster-wide consequences for compression, background merges, and concurrency. Test each change in a staging environment that mirrors your production schema, data distribution, and hardware profile, and validate it against real query patterns before rolling it out. A change that improves one query can regress ten others, and only a staging comparison will catch that.
If you would rather not carry that risk alone, ChistaDATA’s ClickHouse Consulting services can review your schema, reproduce your slow queries in a controlled environment, and hand you a validated tuning plan — so your ClickHouse query performance tuning work reaches production with confidence rather than guesswork.
