Optimize query performance

Measure first with EXPLAIN#

EXPLAIN indexes = 1 reports how many parts and granules survive index pruning:

EXPLAIN indexes = 1
SELECT max(price) FROM uk.uk_price_paid_simple
WHERE town = 'LONDON' AND street = 'OXFORD STREET';

             Condition: and((street in ['OXFORD STREET', 'OXFORD STREET']), (town in ['LONDON', 'LONDON']))
             Parts: 3/3
             Granules: 3/3609

Granules: 3/3609 means the primary index did its job. If the two numbers are equal, nothing was pruned and the query is scanning the table.

Other variants: EXPLAIN PIPELINE shows execution stages and parallelism, EXPLAIN ESTIMATE gives estimated parts, rows, and marks, and EXPLAIN SYNTAX shows the query after rewriting.

Every query also reports what it actually read:

1 row in set. Elapsed: 0.010 sec. Processed 24.58 thousand rows, 159.04 KB

Compare "rows processed" against the table's total. That ratio is your optimization target.

PREWHERE reads filter columns first#

PREWHERE reads the filter columns, evaluates the predicate, and only then reads remaining columns for surviving rows. On wide tables this avoids reading columns for rows that are about to be discarded.

SELECT column_a, column_b
FROM table
PREWHERE column_c = 'value'
WHERE column_d > 100;

ClickHouse moves WHERE conditions into PREWHERE automatically — optimize_move_to_prewhere defaults to 1. Write it explicitly when you know a specific cheap and highly selective column should be evaluated first.

Data-skipping indexes for non-key columns#

A data-skipping index stores metadata per block of granules, letting ClickHouse skip granules based on a non-primary-key column. GRANULARITY N sets how many index granules each entry covers.

ALTER TABLE table ADD INDEX idx_minmax  column TYPE minmax GRANULARITY 4;
ALTER TABLE table ADD INDEX idx_set     column TYPE set(100) GRANULARITY 4;
ALTER TABLE table ADD INDEX idx_bloom   column TYPE bloom_filter(0.01) GRANULARITY 4;
ALTER TABLE table ADD INDEX idx_token   column TYPE tokenbf_v1(32768, 3, 0) GRANULARITY 4;

Build the index over existing parts, otherwise it applies only to new data:

ALTER TABLE table MATERIALIZE INDEX idx_minmax;

These indexes only help when the indexed column correlates with physical row order. A randomly distributed column appears in every block, so nothing is skipped and you have paid for metadata that never prunes.

Projections for a second access pattern#

A projection is an alternate physical ordering, or a pre-aggregation, of the same table, stored inside the parts:

ALTER TABLE table ADD PROJECTION proj_by_date (SELECT * ORDER BY event_date);
ALTER TABLE table MATERIALIZE PROJECTION proj_by_date;

The optimizer routes to it transparently — queries keep naming the original table. Compared to a materialized view, a projection stays consistent with the source rather than firing only on insert, at the same cost in duplicated data.

Find your slow queries#

system.query_log records every executed query:

SELECT
    query_duration_ms,
    query,
    read_rows,
    formatReadableSize(read_bytes) AS read_data,
    formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;

Sort by read_rows rather than duration to find queries that are wasteful rather than merely large.

Mistakes that cause full scans#

Next steps#

• Choose a primary key — the fix for most slow queries
• The sparse primary index — what pruning actually does
• Monitor a running server — catch regressions before users report them