Find expensive completed queries ​

Start with completed requests so a single slow query does not appear twice as both a start and finish event. Query text and resource counters help distinguish a broad scan from a request that returns a large result.

SELECT query_id, query_duration_ms, read_rows, read_bytes,
       result_rows, query
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 20
FORMAT json_each_row;

Investigate a representative example ​

Read the SQL and selected database before changing it. Check whether the request contains several statements: request duration and returned rows can cover their combined work. Use EXPLAIN QUERY PLAN on the expensive SELECT to inspect access choices, then measure one deliberate change at a time.

normalized_query_hash can help recognize repeated query shapes, but inspect actual predicates as well. Two requests with the same shape can read very different amounts of data.

If expected events are missing, inspect log_queries and log_queries_min_query_duration_ms. A nonzero duration threshold records only qualifying terminal events. Use system.processes for work still running, and keep query text out of public reports when it contains private values.

See query history, active requests and logging settings.