Query Optimization

Every topic in this module — partitions, the sparse index, skip indexes, projections — exists to answer one question before a query even starts executing: how much data can we avoid reading? This page is about what happens to whatever data is left after that pruning.

The pruning pipeline

Vectorized execution: blocks, not rows

Row-at-a-time execution (call a function, get a row, call it again) spends most of its time on function-call and branching overhead, not actual work. ClickHouse's engine instead processes data in blocks — batches of a few thousand to ~65,536 values from one column at a time — so a filter or arithmetic operation runs as a tight loop over contiguous memory, which the CPU can pipeline and auto-vectorize (SIMD) effectively.

PREWHERE: filter before you even fetch every column

SELECT user_id, payload
FROM events
PREWHERE status = 500
WHERE event_type = 'error';

A normal WHERE reads all columns needed for the whole query, then filters. PREWHERE reads a cheap column first (here, status), filters, and only then reads the remaining columns (like payload) for the rows that survived — skipping decompression of expensive columns for rows that were going to be discarded anyway. In recent versions ClickHouse often applies this automatically; it's still worth understanding, and sometimes worth forcing explicitly.

Parallelism, two ways

• Within a server — a single query is split across CPU cores, each processing a different range of granules concurrently.
• Across a cluster — a query against a Distributed table fans out to every shard in parallel and merges partial results.

Reading the plan

EXPLAIN indexes = 1
SELECT count() FROM events WHERE user_id = 204;

This shows exactly which partitions and how many granules were pruned by the primary key and by any skip indexes — the fastest way to confirm a schema decision is actually paying off, instead of guessing from query latency alone.