ClickHouse Query Profiling: How to Diagnose Expensive Queries

ClickHouse provides three EXPLAIN variants that reveal different aspects of query execution. Combined with system.query_log sampling, you can pinpoint exactly what makes a query expensive without guessing.

EXPLAIN PLAN — Logical Structure

EXPLAIN PLAN
SELECT user_id, count(), sum(amount)
FROM orders
WHERE created_at >= today() - 30
GROUP BY user_id;

Look for: ReadFromMergeTree with ReadFromMergeTree (Scanning 1000000 granules) — high granule counts mean a lot of data is being scanned.

EXPLAIN PIPELINE — Execution Detail

EXPLAIN PIPELINE
SELECT user_id, count()
FROM orders
WHERE created_at >= today() - 30
GROUP BY user_id;

Shows parallelism and the full execution pipeline. Look for single-threaded stages as bottlenecks.

EXPLAIN ESTIMATE — I/O Preview

EXPLAIN ESTIMATE
SELECT * FROM orders WHERE user_id = 12345;

Shows estimated rows, marks, and bytes before running the query. Use this to catch expensive queries before they run in production.

Sampling Query Logs

-- Find the most expensive query patterns (normalized)
SELECT
    normalizeQuery(query) AS pattern,
    count() AS executions,
    avg(query_duration_ms) AS avg_ms,
    max(query_duration_ms) AS max_ms,
    avg(read_rows) AS avg_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
GROUP BY pattern
ORDER BY avg_ms * executions DESC
LIMIT 20;

Clustersight provides a Query Inspector panel that surfaces this analysis automatically without requiring direct SQL access to system tables.

Read more: ClickHouse Slow Queries Guide | ClickHouse Monitoring Guide