clickhouse-performance-tuning

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 <[e-mail]>
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
 type  
   event_time  now()    
   query_duration_ms     
  query_duration_ms 
LIMIT ;