Isolate query bottlenecks

​

Before you begin

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

​

How it works

1. Run the original query to establish the baseline measurements.
2. Keep GROUP BY, replace the query’s aggregate calculations with count, and remove later operations such as sorting and output formatting.
3. Remove the grouping and run an ungrouped count to approximate the work retained by scanning, filtering, and any joins.

​

Establish a repeatable baseline

• Keep the FROM, JOIN, PREWHERE, and WHERE clauses unchanged so every comparison uses the same data and time range.
• Run each version of the query several times under similar system load.
• Keep cache conditions consistent. Either run each version of the query before recording measurements or disable the caches listed below. Do not compare cached and uncached runs.
• Record a representative duration, such as the median across repeated runs after any warm-up runs, rather than relying on the fastest or slowest result.
• Change one variable at a time so that you can associate a performance difference with a specific change.

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

1. Assign a unique query ID to every run, or record the ID generated by your query interface. For example, identify repeated runs as bottleneck-a-1, bottleneck-a-2, and bottleneck-a-3. With clickhouse-client, pass --query_id your-query-id when you execute a query.
2. Execute each comparison query several times under the same conditions. Keep warm-up runs separate from the measured runs.
3. Flush the query log before looking up recently completed queries:
   SYSTEM FLUSH LOGS;
   
   If you cannot run SYSTEM FLUSH LOGS, wait for the query log to flush automatically, then retry the lookup. If the record never appears, verify that query logging is enabled, that you can read system.query_log, and that you are querying the node that ran the query.
4. Look up the completed record for each query ID. system.query_log records both QueryStart and QueryFinish events for a completed query. Filter for QueryFinish, which contains the final duration, rows and bytes read, and peak memory:
   SELECT
       query_id,
       query_duration_ms,
       read_rows,
       read_bytes,
       memory_usage
   FROM system.query_log
   WHERE type = 'QueryFinish'
     AND query_id = 'your-query-id'
   ORDER BY event_time_microseconds DESC
   LIMIT 1;
   
5. For each version of the query, use the median duration from the measured runs. Record read_rows, read_bytes, and peak memory from the run closest to that median so that the measurements remain tied to an actual run.

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

​

Run progressively simpler queries

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

​

Interpret the differences

​

Compare rows read with the count result

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

​

Validate the suspected bottleneck

• For a scan or filtering bottleneck, use EXPLAIN indexes = 1 with the settings described above to see which indexes ClickHouse uses and how many parts and granules each index eliminates. Check whether the plan uses an implicit projection instead of the expected scan.
• For a grouping or aggregation bottleneck, inspect relevant query profile events and peak memory usage.
• If run C remains slow and contains joins, compare it with a diagnostic query that removes one join at a time. A large decrease in duration suggests that the removed join contributes significant work. Because removing a join changes the query’s meaning, use this comparison only to isolate timing and interpret changes in row count separately.
• For a bottleneck in another operation retained by run C, inspect the execution plan and relevant query profile events.

​

Next steps