---

name: clickhouse-performance-tuning description: | Optimize ClickHouse query performance with indexing, projections, settings tuning, and query analysis using system tables. Use when queries are slow, investigating performance bottlenecks, or tuning ClickHouse server settings. Trigger with "clickhouse performance", "optimize clickhouse query", "clickhouse slow query", "clickhouse indexing", "clickhouse tuning", "clickhouse projections". allowed-tools: Read, Write, Edit version: 1.7.0 license: MIT author: Jeremy Longshore jeremy@intentsolutions.io tags:

• saas
• database
• analytics
• clickhouse
• olap compatibility: Designed for Claude Code

---

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 query_duration_ms, then inspect a suspect query with EXPLAIN PLAN / EXPLAIN PIPELINE.
2. ORDER BY key optimization — the primary lever. Filtering on the ORDER BY prefix skips whole granules; a mismatched key forces a full scan.
3. Data skipping indexes — bloom_filter for high-cardinality lookups, set for low-cardinality columns, minmax for range filters on non-key columns.
4. Projections — automatic pre-aggregation ClickHouse picks transparently when a query matches the projection's shape.
5. Server settings — max_threads, external sort/group-by spill, async_insert, and friends, set per-query or per-session.
6. Materialized views — pre-aggregate on INSERT into an AggregatingMergeTree so dashboard reads hit milliseconds, not seconds.
7. 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.