A slow query in ClickHouse® is rarely a mystery once you know where to look. The database records an enormous amount of detail about every statement it runs, and the skill is reading that detail in the right order. This is the routine I use to take a query from "it feels slow" to "here is the exact reason," and the fix that follows from it.
Profiling is about finding the real constraint
Every query is limited by something: it is reading too much data, sorting too much, holding too much in memory, or waiting on the network between nodes. Profiling is the process of identifying which of those is the actual constraint, so you spend your effort on the part that matters instead of guessing. Optimizing the wrong stage is the most common way people waste an afternoon.
So the goal is not to look at one number. It is to build a small picture of where the time and resources went, then act on the widest bar.
Start with the query log
system.query_log is the first place to look. It holds a row for every finished query with its timing and resource cost. Pull the recent slow ones:
SELECT
query_duration_ms,
read_rows,
formatReadableSize(read_bytes) AS read,
formatReadableSize(memory_usage) AS memory,
substring(query, 1, 60) AS preview
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 10;The type = 'QueryFinish' filter matters, because the log writes a separate row when a query starts and finishes, and only the finish rows carry the final numbers. A query that reads billions of rows quickly is usually fine. The ones worth your attention read far more data than the size of their result.
Read the ratio, not just the duration
The single most useful signal is how many rows a query read compared to how many it returned. A query that reads 500 million rows to return 50 is scanning data it did not need, which almost always points back to a primary key that is not being used for the filter. Group by the normalized query to see the repeat offenders rather than thousands of one-off statements:
SELECT
normalized_query_hash,
any(substring(query, 1, 80)) AS sample,
count() AS runs,
round(avg(read_rows)) AS avg_read,
round(avg(query_duration_ms)) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_date = today()
GROUP BY normalized_query_hash
ORDER BY avg_ms DESC
LIMIT 20;Ask the planner before changing anything
Once you have a suspect query, ask ClickHouse® how it intends to run it. EXPLAIN with index analysis tells you whether the primary key is doing any work:
EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE event_date = today() AND user_id = 4242;If the output shows that every granule is read, the key is not helping this filter, and no amount of hardware will save you. The fix is a better key order, a projection, or a data-skipping index, not a bigger box.
Look at what is running right now
For a cluster that is slow at this exact moment, system.processes shows everything in flight, with the elapsed time, memory, and rows read so far. If one runaway query is hurting everyone, you can stop it by its id with KILL QUERY. Watching the live picture alongside the historical log is how you tell a one-off spike from a recurring pattern.
When the log is not enough
System tables tell you what happened and how much it cost, but they are less good at showing where the time went inside a single query. That is where a visual profiler earns its place. In CHOps the query profiler builds a flame graph from ClickHouse®'s own trace data, so the most expensive operation is literally the widest bar on the screen, and the resource timelines show CPU, memory, and IO second by second across the query's life. For the queries that spike or crash rather than run steadily slow, that per-second view is what reveals the exact moment things went wrong.
The routine, summarized
Use query_log to find the slow patterns and the read ratios. Use EXPLAIN to confirm whether the primary key is being used. Use system.processes for the live picture. Then, for the genuinely puzzling ones, profile the single query to see where the time is spent. Done in that order, slow queries stop being a source of dread and become a short, repeatable investigation.
Working through a struggling production cluster is stressful. Take it one step at a time rather than firing off changes under pressure, and let the numbers point you to the fix.
