ClickHouse Query Performance: The Complete Guide

ClickHouse is fast by default — but only if you use it correctly. A single missing primary key filter can turn a millisecond query into a 30-second table scan.

This hub collects everything on diagnosing and fixing slow queries: from reading system.query_log to profiling with EXPLAIN, to fixing schema problems that cause performance issues.

Where to Start

If a query is slow, the first thing to check is what it's scanning. system.query_log gives you read_rows, read_bytes, and memory_usage per query. A query reading 100M rows for a result of 10 rows almost always has a schema or query design problem.

SELECT
    round(query_duration_ms / 1000, 2) AS duration_sec,
    formatReadableSize(read_bytes) AS read_bytes,
    read_rows,
    query
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 20;

Articles in This Series

Finding Slow Queries

ClickHouse Query Performance: Finding and Fixing Slow Queries — How to use system.query_log, understand read amplification, and identify the root cause of slow queries.

Profiling Queries

How to Profile ClickHouse Queries with system.query_log and EXPLAIN — Step-by-step query profiling with EXPLAIN PLAN, EXPLAIN PIPELINE, and trace-level logging.

Common Performance Patterns

Primary Key Misuse

ClickHouse's primary key controls which granules are skipped. If your WHERE clause doesn't use the first column(s) of the ORDER BY, ClickHouse scans everything. The fix is either restructuring the ORDER BY or using a skip index.

High-Cardinality GROUP BY

Aggregating on a UUID or user_id column without pre-aggregation creates a huge in-memory hash map. Materialized views with a SummingMergeTree or AggregatingMergeTree pre-compute these aggregations at insert time.

Reading Too Many Columns

ClickHouse is columnar — only read the columns you need. SELECT * on a wide table reads all columns from disk even if your WHERE clause only touches two.