7. A query, traced end to end

SELECT country, count() AS views, avg(duration) AS avg_ms
FROM page_views
WHERE tenant_id = 7 AND ts >= '2026-08-01' AND url LIKE '%checkout%'
GROUP BY country
ORDER BY views DESC
LIMIT 10;

Forty billion rows in the table. Here is every step between pressing enter and getting an answer.

Step 1 — parse and analyse

The SQL is parsed into an abstract syntax tree, then into a logical query plan, then optimised: constant folding, predicate pushdown, removing unused columns, rewriting some subqueries into joins, and applying whatever the current analyser knows.

The most important early decision is which columns are needed at all. Here: tenant_id, ts, url, country, duration. If the table has forty columns, the other thirty-five are never touched. That is chapter 1’s first effect, and it happens right here.

Step 2 — partition pruning

PARTITION BY toYYYYMM(ts) and WHERE ts >= '2026-08-01', so only partitions 202608 and later are considered. Everything else is eliminated before a single byte is read.

Step 3 — index analysis

For each surviving part, ClickHouse binary-searches the sparse primary index (chapter 3). The ordering key is (tenant_id, country, ts), so tenant_id = 7 gives a contiguous range, and ts >= '2026-08-01' narrows within it.

EXPLAIN indexes = 1
SELECT country, count() FROM page_views
WHERE tenant_id = 7 AND ts >= '2026-08-01';

   ReadFromMergeTree (default.page_views)
   Indexes:
     MinMax
       Keys: ts
       Parts: 4/61                    ← partition pruning
       Granules: 812340/4882312
     PrimaryKey
       Keys: tenant_id, country, ts
       Condition: and((tenant_id in [7,7]), (ts in [1754006400, +Inf)))
       Parts: 4/4
       Granules: 1284/812340          ← 1,284 granules ≈ 10.5 million rows

Forty billion rows down to 10.5 million before any data is decompressed. The Granules line is the number that matters, and it is decided entirely by chapter 5’s ordering key.

Note that url LIKE '%checkout%' contributes nothing here — an unanchored substring match cannot use a sorted index. It will be applied later, to the rows we read.

Step 4 — PREWHERE

Now a genuinely clever optimisation, and one of the reasons ClickHouse feels fast.

We must read tenant_id, ts, url, country and duration for 10.5 million rows. But url is a large string column and duration is only needed for rows that survive the filter. Reading them all is wasteful.

PREWHERE reads the filter columns first, evaluates the condition, and only then reads the remaining columns for the rows that passed.

   WITHOUT PREWHERE                  WITH PREWHERE
   read tenant_id  10.5M             read tenant_id  10.5M
   read ts         10.5M             read ts         10.5M
   read url        10.5M  ← big      evaluate filter → 400K rows survive
   read country    10.5M                     │
   read duration   10.5M             read url       (granules with survivors)
   filter → 400K                     read country   only surviving granules
                                     read duration  only surviving granules

ClickHouse does this automatically (optimize_move_to_prewhere = 1), moving parts of your WHERE into PREWHERE based on column sizes and estimated selectivity. You can also write it explicitly when you know better than the heuristic:

SELECT country, count()
FROM page_views
PREWHERE tenant_id = 7 AND ts >= '2026-08-01'    -- cheap, selective columns
WHERE url LIKE '%checkout%'                       -- expensive column, read later
GROUP BY country;

The rule for writing it by hand: put the small, highly selective columns in PREWHERE, and leave the large ones in WHERE. On tables with a few large string or array columns and many small ones, doing this deliberately can be a several-fold speedup.

Step 5 — parallel reading

The granules to read are divided among threads — max_threads, defaulting to the number of physical cores.

   1,284 granules across 4 parts, max_threads = 16

   thread 0   granules   0–80      ┐
   thread 1   granules  81–160     │  each thread:
   thread 2   granules 161–240     │    decompress blocks
   …                               │    apply filters (SIMD)
   thread 15  granules …           ┘    build a partial aggregate

Each thread works independently and produces a partial aggregation state — a hash table of country → (count, sum, n). No locks, no coordination. At the end, the 16 partial states are merged.

This is why ClickHouse scales almost linearly with cores on a scan-heavy query, and why max_threads is the first setting people touch (usually to lower it, so one query does not consume the whole machine — chapter 13).

Step 6 — vectorised execution

Inside each thread, work happens on blocks of up to 65,536 values (max_block_size), one column at a time.

   for each block:
       decompress country column block        → contiguous array
       decompress duration column block       → contiguous array
       apply filter mask (SIMD compare)
       hash country values → group index
       aggregate: count += 1, sum += duration (SIMD where possible)

Chapter 1’s third effect. The inner loop is a tight scan over an array with no branches and no pointer chasing, which is where the CPU’s SIMD units and prefetcher earn their keep.

Step 7 — aggregation

GROUP BY country builds a hash table per thread. ClickHouse picks the hash table implementation based on the key type — a LowCardinality(String) key is hashed as an integer, which is much faster than string hashing, and is one of the reasons chapter 2 recommends it.

If the hash tables get too large, aggregation spills to disk (max_bytes_before_external_group_by), which keeps the query alive at a large cost. If that setting is zero — which it is by default in many configurations — the query fails with a memory limit exception instead. Chapter 9 covers the choice.

Step 8 — merge, sort, limit

Partial aggregation states merge into a final result: a few hundred rows, one per country. Then ORDER BY views DESC LIMIT 10 sorts that tiny result.

Notice where the sort happens — after aggregation, on hundreds of rows, not on 10.5 million. That ordering is why ORDER BY on an aggregate is cheap, while ORDER BY on a raw column across a large scan is not.

The whole path

   SELECT … FROM page_views WHERE tenant_id=7 AND ts>=… AND url LIKE …
        │
        ├─ 1. parse, plan, determine required columns    (5 of 40 columns)
        ├─ 2. partition pruning                          61 → 4 parts
        ├─ 3. sparse index analysis                      4.9M → 1,284 granules
        ├─ 4. PREWHERE                                   read cheap cols first
        ├─ 5. distribute granules across max_threads
        │        │
        │        ├─ thread: decompress → SIMD filter → partial hash aggregate
        │        ├─ thread: …
        │        └─ thread: …
        ├─ 6/7. merge partial aggregation states
        ├─ 8. ORDER BY + LIMIT on the small result
        └─ return

Reading EXPLAIN

Four modes, each answering a different question:

EXPLAIN SELECT …                    -- the logical plan
EXPLAIN indexes = 1 SELECT …        -- ← the one you want most of the time
EXPLAIN PIPELINE SELECT …           -- the physical execution pipeline and threads
EXPLAIN ESTIMATE SELECT …           -- estimated parts, rows and marks
EXPLAIN AST SELECT …                -- the parsed syntax tree

EXPLAIN indexes = 1 answers the only question that usually matters: how many granules will this read? If that number is close to the table total, nothing else you do will help. Go back to chapter 5.

EXPLAIN ESTIMATE is a fast way to get the same answer as a single row of numbers.

Reading the query log

system.query_log records every query, and it is the best tool in this guide.

SELECT
    event_time,
    query_duration_ms,
    formatReadableQuantity(read_rows)      AS rows_read,
    formatReadableSize(read_bytes)         AS bytes_read,
    formatReadableSize(memory_usage)       AS memory,
    result_rows,
    substring(query, 1, 80)                AS q
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

The diagnostic ratio:

   read_rows ≈ result_rows        ideal for a point query
   read_rows ≫ result_rows        a scan — fine for analytics,
                                  bad if it should have been indexed
   read_rows ≈ table total        ❌ the index is doing nothing

Group by normalised query to find where the time actually goes:

SELECT
    normalized_query_hash,
    any(substring(query, 1, 100))       AS sample,
    count()                             AS runs,
    sum(query_duration_ms)              AS total_ms,
    avg(query_duration_ms)              AS avg_ms,
    formatReadableQuantity(sum(read_rows)) AS total_rows
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash
ORDER BY total_ms DESC
LIMIT 20;

Sort by total time, not by worst case. A 3-second query run once a day matters less than a 200 ms query run a million times.

For a single query, send_logs_level shows the engine’s own commentary as it runs:

SET send_logs_level = 'trace';
SELECT …;
-- "Key condition: (column 0 in [7, 7])"
-- "Selected 4/61 parts by partition key, 1284/812340 marks by primary key"

That second line is the same information as EXPLAIN indexes = 1, produced by the real execution.

Settings that change query behaviour

max_threads                        cores    parallelism for one query
max_block_size                     65505    values per block
max_memory_usage                   10 GiB   per query, per server
max_bytes_before_external_group_by 0        0 = fail rather than spill
max_execution_time                 0        no limit — set one
optimize_move_to_prewhere          1        automatic PREWHERE
use_uncompressed_cache             0        see below
max_rows_to_read / max_bytes_to_read        hard guardrails

The uncompressed cache is worth a note. ClickHouse caches decompressed blocks in memory when enabled. It helps a workload with many repeated small queries hitting the same data, and it hurts a scan-heavy workload by evicting useful page cache. It is off by default for good reason; turn it on only for a measured point-lookup-heavy pattern.

The mark cache (on by default, mark_cache_size, 5 GB default) caches the marks files. It matters a lot — marks are read for every query — and it rarely needs tuning.

Making a query faster, in order

1. Check EXPLAIN indexes = 1. If granules read ≈ total, the ordering key is wrong for this query. That is chapter 5, and no other step matters until it is fixed.
2. Select fewer columns. SELECT * on a wide table is the easiest large win available. Never use it in an application.
3. Move selective, small columns into PREWHERE if the automatic choice is poor.
4. Add a data skipping index if the filter column is clustered but not in the sort key (chapter 3).
5. Add a projection if a second access pattern needs a different sort order (chapter 11).
6. Pre-aggregate with a materialised view if the same aggregation runs repeatedly (chapter 11).
7. Only then consider max_threads, memory settings, and hardware.

People habitually start at step 7. The first two steps deliver more than everything else combined.

What goes wrong

SELECT *. Reads every column, defeating the entire architecture. The most common and most easily fixed mistake.

A filter that cannot use the index. WHERE toDate(ts) = '2026-08-20' wraps the key column in a function, so the sorted order no longer applies. Write WHERE ts >= '2026-08-20' AND ts < '2026-08-21' instead. ClickHouse handles some of these rewrites, not all — never rely on it.

ORDER BY on a large raw result. Sorting 400 million rows needs memory or spills. Aggregate first, or add a LIMIT.

Memory limit exceeded on a GROUP BY with very high cardinality. Chapter 9.

No max_execution_time. One runaway analyst query consumes the cluster for an hour. Set limits per user (chapter 13).

A query with high read_rows but few result_rows, run constantly by a dashboard. That is a materialised view waiting to be written.

What to take from this chapter

• The read path is prune partitions → analyse the sparse index → PREWHERE → parallel granule reading → vectorised filter and aggregate → merge partial states.
• EXPLAIN indexes = 1 and the Granules: read/total ratio is the one diagnostic to internalise.
• PREWHERE reads cheap filter columns first and only then reads the expensive ones, for surviving rows. It is automatic, and worth writing by hand when large columns are involved.
• Each thread builds a partial aggregation state independently; there is no locking, which is why cores scale nearly linearly.
• system.query_log grouped by normalized_query_hash, sorted by total duration, tells you where your cluster’s time actually goes.
• To make a query faster: fix the ordering key, then stop using SELECT *, then PREWHERE, then skipping indexes, then projections, then materialised views. Settings last.
• Wrapping a key column in a function (toDate(ts)) disables index use. Write range conditions on the raw column.

Next: the type system, and the choices in it that cost or save you real money.