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.

1. Diagnose slow queries — rank the last 24h of system.query_log by

querydurationms, then inspect a suspect query with EXPLAIN PLAN /

EXPLAIN PIPELINE.

1. ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix

skips whole granules; a mismatched key forces a full scan.

1. Data skipping indexes — bloom_filter for high-cardinality lookups, set for

low-cardinality columns, minmax for range filters on non-key columns.

1. Projections — automatic pre-aggregation ClickHouse picks transparently when a

query matches the projection's shape.

1. Server settings — maxthreads, external sort/group-by spill, asyncinsert,

and friends, set per-query or per-session.

1. Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so

dashboard reads hit milliseconds, not seconds.

1. 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 readrows / readbytes 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

readrows, readbytes, querydurationms, or memory_usage.

Error Handling

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.