How to Speed Up Slow ClickHouse Queries on Large Tables
Most slow ClickHouse queries are slow for exactly one of four reasons: the query reads too many granules, the join spills, the columns are fatter than they need to be, or the aggregation is done against raw rows when a pre-aggregation would do.
Diagnosing which one takes five minutes. Fixing it ranges from changing a data type to adding a projection — no re-ingestion required for the first two.
What is the fastest way to find why a query is slow?
Read the system.query_log entry, not the wall-clock time.
SELECT query_duration_ms, read_rows, read_bytes,
memory_usage, ProfileEvents['SelectedMarks'] AS marks
FROM system.query_log
WHERE type = 'QueryFinish' AND query LIKE '%your_table%'
ORDER BY event_time_millis DESC LIMIT 10;
read_rows versus the table’s total row count tells you immediately whether the problem is filtering or compute. Reading 2 billion rows to return 400 means the sort key doesn’t match your predicate. Reading 5 million of 2 billion and still taking 30 seconds means the compute is the issue.
read_bytes / read_rows is the average bytes per row read. Below 20 is healthy for narrow analytical tables. If you see 300, you’re dragging wide String columns through every query.
Then EXPLAIN indexes = 1 shows granules selected versus total per part. It’s the only reliable way to confirm an index is being used at all.
How do I fix a query that reads every granule?
The ORDER BY defines the sparse index. If your filter isn’t a prefix of that key, ClickHouse scans. Three options, cheapest first.
Reorder the existing query to use the prefix. If the table is ORDER BY (site, ts) and the query filters only on ts, add a site predicate. Users hate this answer. It’s free.
Change the primary key with ORDER BY reordering at table creation, or migrate with INSERT ... SELECT into a new table. On 500 million rows with a LowCardinality column that’s roughly a 15-minute rebuild.
Add a projection. This is the underused one. A projection stores a second physical sort order of the same table, and ClickHouse picks it automatically when the predicate matches better.
ALTER TABLE events ADD PROJECTION by_country (
SELECT * ORDER BY (country, ts)
);
ALTER TABLE events MATERIALIZE PROJECTION by_country;
Materialising rewrites the parts, so it’s a one-time heavy operation — the same order of cost as a rebuild, but future inserts maintain it automatically and you keep the original sort order for the queries that already work.
Skip indexes (ADD INDEX ... TYPE minmax / set / bloom_filter) help for path LIKE and IN (...) on columns that are not in the key. They’re cheap to add, but only pay off when the predicate is highly selective. On LowCardinality columns they’re usually pointless.
Which column types change the most?
This is where the easy 2-4x wins live.
| Replace | With | Typical gain |
|---|---|---|
String for a 3-value column |
LowCardinality(String) |
3-10x less I/O |
String for timestamps |
DateTime / DateTime64 |
Avoids parsing 10M strings |
String for counts |
UInt32 / UInt16 |
4-8x smaller |
Nullable(String) |
String with default '' |
Removes the null mask column |
Float64 prices |
Decimal(18,4) |
Correctness, not speed |
Nullable deserves a note. ClickHouse stores it as a separate mask array plus the values, and comparisons get slower. It’s a correctness feature, not a convenience one — use it only where a null is semantically different from empty or zero.
When should I pre-aggregate?
When the same question is asked repeatedly over the same slice. There are two mechanisms with different trade-offs.
Materialized views transform rows as they’re inserted. A 5-minute rollup table computed by a MV means the dashboard queries 200k rows instead of 2 billion.
CREATE MATERIALIZED VIEW events_5m
ENGINE = AggregatingMergeTree
ORDER BY (site, bucket)
AS SELECT site, toStartOfFiveMinute(ts) AS bucket,
countState() AS hits, avgState(duration) AS avg_dur
FROM events GROUP BY site, bucket;
Note the State suffix — you query it with countMerge(hits), not count(). This trips up everyone the first time, and the error message (“aggregate function countState is used with wrong type”) doesn’t hint at the fix.
A MV only covers inserts made after it exists. Backfilling means inserting into the target table with INSERT ... SELECT from the source, using the same Merge wrappers.
Query-level caching is the cheap alternative: SET use_query_cache = 1 with a short TTL. On 24.8 this is available but disabled by default. It helps dashboards that refresh on a schedule and shows invisible staleness — check the cache age in the result metadata before you trust a number.
Is it bad to hit 30 seconds?
Not necessarily. A nightly analyst query on 2 billion rows that runs for 40 seconds costs nothing and nobody sees it. Optimising it is a week of work for a personal vanity metric.
What’s genuinely worth fixing: anything a dashboard calls on page load (needs sub-second), anything a product feature calls per user request, and anything that currently times out. Rank by user-visible latency, not by how slow it feels in a notebook.
If the real cost driver is storage rather than time, compressing with CODEC(ZSTD(3)) on text columns typically cuts disk 2-3x with a small CPU penalty — worth it for cold historical data, not for hot dashboards.
Quick Summary
- Read
system.query_logforread_rowsandread_bytesbefore changing anything. - A predicate that isn’t a prefix of
ORDER BYforces a full scan — reorder the query, migrate, or add a projection. - Projections are the cheapest way to add a second sort order without a full migration.
LowCardinalityand explicit integer types are the most reliable 2-4x I/O reduction.- Materialized views pre-aggregate for inserts made after creation only; backfill separately.
- Leave slow offline queries alone — optimise what users wait on.</}
