ClickHouse Performance Tuning
Overview
Diagnose and fix ClickHouse performance issues using query analysis, proper indexing,
projections, materialized views, and server settings tuning. Work top-down: measure
first with system.query_log, then apply the single highest-leverage fix (usually the
ORDER BY key), then re-measure to confirm.
Prerequisites
- ClickHouse tables with data (see
clickhouse-core-workflow-a)
- Access to
system.query_log and system.parts
Instructions
The tuning workflow is seven independent steps. Diagnose first, then reach for the fix
that matches the bottleneck. Each step's full SQL lives in
references/implementation.md — start there for the
complete, copy-paste commands.
- Diagnose slow queries — rank the last 24h of
system.query_log by
query_duration_ms, then inspect a suspect query with EXPLAIN PLAN /
EXPLAIN PIPELINE.
- ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix
skips whole granules; a mismatched key forces a full scan.
- Data skipping indexes —
bloom_filter for high-cardinality lookups, set for
low-cardinality columns, minmax for range filters on non-key columns.
- Projections — automatic pre-aggregation ClickHouse picks transparently when a
query matches the projection's shape.
- Server settings —
max_threads, external sort/group-by spill, async_insert,
and friends, set per-query or per-session.
- Materialized views — pre-aggregate on INSERT into an
AggregatingMergeTree so
dashboard reads hit milliseconds, not seconds.
- Query patterns —
PREWHERE, LIMIT BY, and avoiding FINAL.
The essential first move — find the slowest queries:
SELECT event_time, query_duration_ms, read_rows, read_bytes,
substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time >= now() - INTERVAL 24 HOUR
AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 20;
Output
Applying this workflow produces:
- A ranked list of the slowest queries with their
read_rows / read_bytes cost.
- One or more concrete schema/query changes: a corrected
ORDER BY key, added data
skipping indexes, a projection, a materialized view, or tuned session settings.
- A before/after measurement from
system.query_log proving the change reduced
read_rows, read_bytes, query_duration_ms, or memory_usage.
Error Handling
| Issue | Indicator | Solution |
|---|
| Full table scan | read_rows = total rows | Fix ORDER BY to match filters |
| Memory exceeded | Error 241 | Add LIMIT, use streaming, increase limit |
| Slow GROUP BY | High read_bytes | Add materialized view or projection |
| Merge backlog | Parts > 300 | Reduce insert frequency, increase merge threads |
Examples
Worked before/after scenarios — full-scan → ORDER BY fix, slow GROUP BY → projection,
confirming a skipping index fires, and the query-cost measurement query — are in
references/examples.md. The core measurement, run right after
any query you are tuning:
SELECT query_duration_ms, read_rows,
formatReadableSize(read_bytes) AS read_size,
formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE query_id = currentQueryId() AND type = 'QueryFinish';
Resources
Next Steps
For cost optimization, see clickhouse-cost-tuning.
