Compare query measurements ​

A useful comparison changes one part of a query while keeping its input data, parameters and expected result constant. Check result equivalence before interpreting a shorter duration as an improvement. A query that returns fewer rows may simply be doing different work.

Use terminal events to compare completed requests:

SELECT query_id, query_duration_ms, read_rows, read_bytes,
       result_rows, peak_memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 10
FORMAT csv;

Record the query IDs for the two executions. Terminal memory_usage and peak_memory_usage both describe peak accounted SQL memory. read_rows can include rows rejected by a filter, while result_rows counts returned rows across the request statements. They measure different things.

Run several comparable observations when durations fluctuate, and inspect EXPLAIN QUERY PLAN alongside them. EXPLAIN ANALYZE executes the statement once and replaces its usual result with measurements; analyzed writes still change data. Do not use it on a write unless that effect is intended or enclosed in a transaction you roll back.

See query measurements.