ClickHouse Performance Tuning Skill

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   -- > 1 second
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

references/implementation.md — full 7-step SQL walkthrough
references/examples.md — worked before/after tuning examples
Projections
Data Skipping Indexes
MergeTree Settings

Next Steps

For cost optimization, see clickhouse-cost-tuning.

Agent Skills: ClickHouse Performance Tuning

Install this agent skill to your local

Skill Files