A simple guide for query optimization

-- Insert the data
INSERT INTO trips_small_pk SELECT * FROM trips_small_inferred
```

We then rerun our queries. We compile the results from the three experiments to see the improvements in elapsed time, rows processed, and memory consumption.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Query 1|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Run 1 Run 2 Run 3|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Elapsed 1.699 sec 1.353 sec 0.765 sec|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Rows processed 329.04 million 329.04 million 329.04 million|  |  |  |  | | --- | --- | --- | --- | | Peak memory 440.24 MiB 337.12 MiB 444.19 MiB | | | | | | | | | | | | | | | | | | | |

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Query 2|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Run 1 Run 2 Run 3|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Elapsed 1.419 sec 1.171 sec 0.248 sec|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Rows processed 329.04 million 329.04 million 41.46 million|  |  |  |  | | --- | --- | --- | --- | | Peak memory 546.75 MiB 531.09 MiB 173.50 MiB | | | | | | | | | | | | | | | | | | | |

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Query 3|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Run 1 Run 2 Run 3|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Elapsed 1.414 sec 1.188 sec 0.431 sec|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Rows processed 329.04 million 329.04 million 276.99 million|  |  |  |  | | --- | --- | --- | --- | | Peak memory 451.53 MiB 265.05 MiB 197.38 MiB | | | | | | | | | | | | | | | | | | | |

We can see significant improvement across the board in execution time and memory used.

Query 2 benefits most from the primary key. Let's have a look at how the query plan generated is different from before.

```
EXPLAIN indexes = 1
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_pk
WHERE (pickup_datetime >= '2009-01-01') AND (pickup_datetime < '2009-04-01')
GROUP BY payment_type
ORDER BY trip_count DESC

Query id: 30116a77-ba86-4e9f-a9a2-a01670ad2e15

    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Projection + Before ORDER BY [lifted up part]))                                                     │
 2. │   Sorting (Sorting for ORDER BY)                                                                                 │
 3. │     Expression (Before ORDER BY)                                                                                 │
 4. │       Aggregating                                                                                                │
 5. │         Expression (Before GROUP BY)                                                                             │
 6. │           Expression                                                                                             │
 7. │             ReadFromMergeTree (nyc_taxi.trips_small_pk)                                                          │
 8. │             Indexes:                                                                                             │
 9. │               PrimaryKey                                                                                         │
10. │                 Keys:                                                                                            │
11. │                   pickup_datetime                                                                                │
12. │                 Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf))) │
13. │                 Parts: 9/9                                                                                       │
14. │                 Granules: 5061/40167                                                                             │
    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```

Thanks to the primary key, only a subset of the table granules has been selected. This alone greatly improves the query performance since ClickHouse has to process significantly less data.

## Next steps[​](#next-steps "Direct link to Next steps")

Hopefully this guide gets a good understanding on how to investigate slow queries with ClickHouse and how to make them faster. To explore more on this topic, you can read more about [query analyzer](/docs/operations/analyzer) and [profiling](/docs/operations/optimizing-performance/sampling-query-profiler) to understand better how exactly ClickHouse is executing your query.

As you get more familiar with ClickHouse specificities, I would recommend to read about [partitioning keys](/docs/optimize/partitioning-key) and [data skipping indexes](/docs/optimize/skipping-indexes) to learn about more advanced techniques you can use to accelerate your queries.

- [Understand query performance](#understand-query-performance)- [General considerations](#general-considerations)- [Dataset](#dataset)- [Spot the slow queries](#spot-the-slow-queries)
        - [Query logs](#query-logs)- [Explain statement](#explain-statement)- [Methodology](#methodology)- [Basic optimization](#basic-optimization)
            - [Nullable](#nullable)- [Low cardinality](#low-cardinality)- [Optimize data type](#optimize-data-type)- [Apply the optimizations](#apply-the-optimizations)- [The importance of primary keys](#the-importance-of-primary-keys)
              - [Choose primary keys](#choose-primary-keys)- [Next steps](#next-steps)

Was this page helpful?