ClickHouse Query Performance: Finding and Fixing Slow Queries

Finding slow queries in ClickHouse starts with system.query_log. Every query ClickHouse executes is logged here with duration, rows read, bytes read, and memory usage. Queries over 5 seconds are worth investigating. Queries over 30 seconds usually indicate a schema or query design problem.

Finding Slow Queries

-- Top 20 slowest queries in the last hour
SELECT
    query_id,
    user,
    round(query_duration_ms / 1000, 2) AS duration_sec,
    formatReadableSize(read_bytes) AS read_bytes,
    formatReadableSize(memory_usage) AS memory,
    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;

Understanding Query Cost

Read rows and bytes

High read_rows or read_bytes means the query is scanning a lot of data. Check if the primary key is being used for filtering.

Memory usage

High memory_usage indicates large sorts, GROUP BY on high-cardinality columns, or large IN() lists.

Profile a specific query

-- See exactly what a query does
EXPLAIN PLAN SELECT ...;
 
-- See I/O and timing breakdown
EXPLAIN PIPELINE SELECT ...;

Common Fixes

1. Use primary key for range filtering

-- Slow: full scan
SELECT count() FROM events WHERE user_id = 12345;
 
-- Fast: if (date, user_id) is the primary key
SELECT count() FROM events
WHERE date >= today() - 7 AND user_id = 12345;

2. Select only needed columns ClickHouse is columnar — selecting fewer columns reduces I/O dramatically.

3. Avoid high-cardinality GROUP BY on raw data Use materialized views to pre-aggregate frequent queries.

Clustersight surfaces slow queries automatically in the Query Inspector panel with duration, memory, and read bytes — no manual query_log querying needed.

Read more: ClickHouse Monitoring Guide | Query Profiling