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: "clickhouse performance", "optimize clickhouse query", "clickhouse slow query",
"clickhouse indexing", "clickhouse tuning", "clickhouse projections".
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: "clickhouse performance", "optimize clickhouse query", "clickhouse slow query",
"clickhouse indexing", "clickhouse tuning", "clickhouse projections".
Diagnose and fix ClickHouse performance issues using query analysis, proper indexing,
projections, materialized views, and server settings tuning.
Prerequisites
ClickHouse tables with data (see clickhouse-core-workflow-a)
Access to system.query_log and system.parts
Instructions
Step 1: Diagnose Slow Queries
-- Find the slowest queries in the last 24 hoursSELECT
event_time,
query_duration_ms,
read_rows,
read_bytes,
result_rows,
memory_usage,
substring(query, 1, 300) AS query_preview
FROM system.query_log
WHERE type ='QueryFinish'AND event_time >= now() -INTERVAL24HOURAND query_duration_ms >1000-- > 1 secondORDERBY query_duration_ms DESC
LIMIT 20;
-- Analyze a specific query with EXPLAIN
EXPLAIN PLAN
SELECT event_type, count() FROM events WHERE created_at >= today() -7GROUPBY event_type;
-- Full pipeline analysis
EXPLAIN PIPELINE
SELECT event_type, count() FROM events WHERE created_at >= today() event_type;
-
7
GROUP
BY
Step 2: ORDER BY Key Optimization
The ORDER BY key is ClickHouse's primary performance lever. Queries that filter
on the ORDER BY prefix skip entire granules (8192-row chunks).
-- Check what your current ORDER BY key isSELECT
database, table, sorting_key, primary_key,
formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.tables
JOIN system.parts ON tables.name = parts.table AND tables.database = parts.database
WHERE tables.database ='analytics'AND tables.name ='events'AND parts.active
GROUPBY database, table, sorting_key, primary_key;
-- If your queries filter on (tenant_id, event_type, created_at)-- but ORDER BY is (created_at), you're scanning too much data.-- Fix: recreate table with correct ORDER BYCREATE TABLE analytics.events_v2 AS analytics.events
ENGINE = MergeTree()
ORDERBY (tenant_id, event_type, toDate(created_at));
INSERT INTO analytics.events_v2 SELECT*FROM analytics.events;
RENAME TABLE analytics.events TO analytics.events_old,
analytics.events_v2 TO analytics.events;
Step 3: Data Skipping Indexes
-- Add a bloom filter index for high-cardinality lookupsALTER TABLE analytics.events
ADD INDEX idx_session_id session_id TYPE bloom_filter(0.01) GRANULARITY 4;
-- Add a set index for low-cardinality columnsALTER TABLE analytics.events
ADD INDEX idx_country country TYPE set(100) GRANULARITY 4;
-- Add a minmax index for range queries on non-ORDER-BY columnsALTER TABLE analytics.events
ADD INDEX idx_amount amount TYPE minmax GRANULARITY 4;
-- Materialize indexes for existing dataALTER TABLE analytics.events MATERIALIZE INDEX idx_session_id;
-- Verify index usage
EXPLAIN indexes =1SELECT*FROM analytics.events WHERE session_id ='abc-123';
Step 4: Projections (Automatic Pre-Aggregation)
-- Add a projection for a common aggregation patternALTER TABLE analytics.events
ADD PROJECTION events_by_hour (
SELECT
toStartOfHour(created_at) AShour,
tenant_id,
event_type,
count() AS cnt,
uniq(user_id) AS unique_users
GROUPBYhour, tenant_id, event_type
);
-- Materialize for existing dataALTER TABLE analytics.events MATERIALIZE PROJECTION events_by_hour;
-- ClickHouse automatically uses the projection when the query matchesSELECT toStartOfHour(created_at) AShour, count()
FROM analytics.events
WHERE tenant_id =1GROUPBYhour;
-- ^ This query reads from the projection (much smaller) instead of full table
Step 5: Key Server Settings
-- Per-query performance settingsSET max_threads =8; -- Threads per query (default: CPU cores)SET max_memory_usage =10000000000; -- 10GB per querySET max_bytes_before_external_sort =10000000000; -- Spill sorts to diskSET max_bytes_before_external_group_by =10000000000; -- Spill GROUP BY to diskSET optimize_read_in_order =1; -- Skip sorting if ORDER BY matchesSET compile_expressions =1; -- JIT compile expressionsSET max_execution_time =60; -- 60s timeout-- Insert performance settingsSET async_insert =1; -- Server-side batching for small insertsSET async_insert_max_data_size =10000000; -- 10MB flush thresholdSET async_insert_busy_timeout_ms =5000; -- 5s flush intervalSET min_insert_block_size_rows =100000; -- Min rows per insert block
Step 6: Materialized Views for Dashboards
-- Pre-aggregate for dashboard queries (runs on INSERT, not on query)CREATE TABLE analytics.dashboard_daily (
dateDate,
tenant_id UInt32,
total_events UInt64,
unique_users AggregateFunction(uniq, UInt64),
p95_latency AggregateFunction(quantile(0.95), Float64)
)
ENGINE = AggregatingMergeTree()
ORDERBY (tenant_id, date);
CREATE MATERIALIZED VIEW analytics.dashboard_daily_mv
TO analytics.dashboard_daily
ASSELECT
toDate(created_at) ASdate,
tenant_id,
count() AS total_events,
uniqState(user_id) AS unique_users,
quantileState(0.95)(latency_ms) AS p95_latency
FROM analytics.events
GROUPBYdate, tenant_id;
-- Query pre-aggregated data (milliseconds instead of seconds)SELECTdate,
sum(total_events) AS events,
uniqMerge(unique_users) AS users,
quantileMerge(0.95)(p95_latency) AS p95
FROM analytics.dashboard_daily
WHERE tenant_id =1ANDdate>= today() -30GROUPBYdateORDERBYdate;
Step 7: Query Optimization Patterns
-- Use PREWHERE for large tables (reads less data than WHERE)SELECT*FROM analytics.events
PREWHERE event_type ='purchase'-- Evaluated first, skips non-matching granulesWHERE user_id >1000; -- Evaluated second, only on matching granules-- Use LIMIT BY for top-N per group (more efficient than window functions)SELECT tenant_id, event_type, count() AS cnt
FROM analytics.events
GROUPBY tenant_id, event_type
ORDERBY cnt DESC
LIMIT 5BY tenant_id; -- Top 5 event types per tenant-- Use FINAL sparingly with ReplacingMergeTree-- Instead of: SELECT * FROM users FINAL (slow, full scan)-- Prefer: SELECT argMax(email, updated_at) AS email FROM users GROUP BY user_id
Performance Benchmarks
-- Measure bytes read and time for a specific querySELECT
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read_size,
result_rows,
formatReadableSize(memory_usage) AS memory
FROM system.query_log
WHERE query_id = currentQueryId()
AND type ='QueryFinish';
