Skip to content

Latest commit

 

History

History
129 lines (97 loc) · 10.2 KB

README.md

File metadata and controls

129 lines (97 loc) · 10.2 KB

Three slow query patterns, before and after the fix

This experiment measures three common causes of slow queries and the fix for each, with the query plan before and after.

  1. The composite index and its column order (Postgres).
  2. Functions on indexed columns, the non-sargable filter (Postgres and ClickHouse).
  3. An unpartitioned, unsorted warehouse table (ClickHouse).

Both engines run locally in Docker, so nothing is billed.

The data

orders(order_id, customer_id, created_at, status, amount) uses the same integer formulas as ../postgres-vs-bigquery/, so the two experiments share their data shape.

  • Postgres holds 10 million orders. ClickHouse holds 100 million, because a warehouse table at 10 million rows is too small for its layout to show.
  • 1 million customers. customers(customer_id, email) in Postgres holds one row each, with mixed-case emails such as [email protected].
  • created_at spreads the orders evenly over 2024 and 2025, in order_id order, so the rows arrive in time order, as an event table does.
  • status is 90% delivered, 6% shipped, 3% cancelled and 1% refunded. amount runs from 1.00 to 499.99.

ClickHouse holds the same 100 million rows in four tables that differ only in their layout:

Table PARTITION BY ORDER BY
orders_plain none none (tuple())
orders_by_month toYYYYMM(created_at) none
orders_by_month_customer toYYYYMM(created_at) (customer_id, created_at)
orders_by_time none created_at

After each build, the tool compares a checksum (row count, sums of customer_id, amount and created_at seconds, and the refunded count) across the ClickHouse tables and stops if they differ. When both engines are built at the same size, it compares Postgres too; a run at 2 million rows on both matched. Each ClickHouse table is merged with OPTIMIZE TABLE ... FINAL after the load, so every layout is measured fully merged.

The variants

Pattern Variant Query Engine
Composite index no index, (created_at, customer_id), (customer_id, created_at) one customer's orders in the second half of 2025 (sql/postgres/customer_recent.sql) Postgres
Non-sargable date created_at::date = day against a half-open range, with an index on created_at orders and revenue on one day Postgres
Non-sargable text lower(email) = ... with an index on email, then with an index on lower(email) one customer by email Postgres
Non-sargable date formatDateTime(created_at, '%Y-%m-%d') = day, toDate(created_at) = day and a half-open range, on orders_by_time orders and revenue on one day ClickHouse
Warehouse layout orders_plain, orders_by_month, orders_by_month_customer, and orders_by_time for the month query revenue by status for October 2025, and one customer's history ClickHouse

For each Postgres variant the tool drops every secondary index, creates only that variant's index and runs VACUUM ANALYZE, so no variant benefits from another's index.

The setup

Postgres 18.6 and ClickHouse 26.8 run in Docker from compose.yaml, with both images pinned by digest. Postgres settings: shared_buffers=4GB, work_mem=64MB, max_parallel_workers_per_gather=4, effective_cache_size=8GB, default random_page_cost=4. ClickHouse runs with its defaults, except that every query sets use_query_cache=0 and use_query_condition_cache=0. The query condition cache, on by default since ClickHouse 25.4, remembers which granules failed a filter and skips them on the next run with the same filter, which would make the unsorted table look fast from the second run on. Checked by hand on 26 October 2026 with the cache on: the month query on orders_plain read 100M rows in 51 ms, then 4,259,840 rows in 10 ms on each of the next two runs (from system.query_log).

Each variant runs once as a warm-up and then 7 measured times. Queries with a parameter (a customer id, a day or an email) use a different value on each run, from a fixed seed, and every variant of the same query gets the same values. The reported value is the median of the 7 measured runs, with the minimum and maximum next to it.

Postgres time is measured by the Python client on the same laptop, so it includes the driver but no network. Postgres JIT stays at its default (on). It compiles the two full scans of orders, which took about 10 ms of each in the saved plans. ClickHouse time is the server's elapsed time from the query summary it returns, and the summary also gives the rows and bytes read.

After the timed runs, so with the same warm cache, the tool saves one plan per variant in results/plans/, using the first measured run's parameter:

  • Postgres: EXPLAIN (ANALYZE, BUFFERS). results/plans.csv takes the scan nodes, the shared buffers the top node touched (hits plus reads, 8 KB each) and the execution time.
  • ClickHouse: EXPLAIN indexes = 1, which shows the parts and granules (8,192 rows each) left after each index. results/plans.csv takes the granules read and the granules in the table.

Run it

You need Docker and uv. A full run takes about 2 minutes on the M5 laptop. Right after it, the two Docker volumes take about 20 GB: ClickHouse keeps the parts that OPTIMIZE ... FINAL merged away for 8 minutes by default, after which the volumes hold about 8 GB.

uv run query-fixes all                                             # build, run and chart
uv run query-fixes all --postgres-rows 2000000 --clickhouse-rows 2000000   # a quick trial that also compares Postgres with ClickHouse
uv run query-fixes teardown                                        # remove both containers and their volumes

summary and chart rebuild results/summary.csv and the charts from the saved measurements.

Results

  • results/measurements.csv: every run, warm-ups included (run 0).
  • results/plans.csv and results/plans/: the plan behind each variant.
  • results/summary.csv: medians per variant, with the plan figures next to them.
  • results/checksums.csv, results/environment.json: the data check and the settings in effect.
  • results/composite_index.png, results/non_sargable.png, results/warehouse_layout.png: one chart per pattern.

Results on 26 October 2026

Medians of 7 warm runs, from results/summary.csv. Postgres pages are the shared buffers the plan's top node touched; ClickHouse rows are the rows the server read.

Variant Time Read
Postgres, one customer, no index 70.0 ms 758.5 MB of pages, parallel sequential scan
Postgres, index on (created_at, customer_id) 17.9 ms 78.8 MB of pages
Postgres, index on (customer_id, created_at) 0.24 ms 65.5 KB of pages
Postgres, created_at::date = day 131.6 ms 758.5 MB of pages, parallel sequential scan
Postgres, half-open range on created_at 1.30 ms 1.4 MB of pages
Postgres, lower(email) with an index on email 67.0 ms 68.3 MB of pages, parallel sequential scan
Postgres, lower(email) with an index on lower(email) 0.22 ms 32.8 KB of pages
ClickHouse, formatDateTime(created_at, ...) = day 131.2 ms 100M rows
ClickHouse, toDate(created_at) = day 1.51 ms 147,456 rows
ClickHouse, half-open range on created_at 1.38 ms 147,456 rows
ClickHouse, one month, orders_plain 39.7 ms 100M rows
ClickHouse, one month, orders_by_month 8.11 ms 4,246,575 rows
ClickHouse, one month, orders_by_month_customer 7.41 ms 4,246,575 rows
ClickHouse, one month, orders_by_time 8.47 ms 4,251,648 rows
ClickHouse, one customer, orders_plain 56.9 ms 100M rows
ClickHouse, one customer, orders_by_month 65.6 ms 100M rows
ClickHouse, one customer, orders_by_month_customer 6.03 ms 196,608 rows

Three other full runs the same morning read the same pages and rows for every variant: two earlier ones without the orders_by_time month variant, and one later run from a clean start (teardown, then all). Across the four runs, medians above 10 ms varied by up to 16% (one customer on orders_by_month: 65.6 to 76.3 ms) and medians under 10 ms by up to 3.8 ms (one customer on orders_by_month_customer: 6.03 to 9.85 ms). The published files are from the run on 26 October 2026 at the measured_at time in environment.json.

Plan notes:

  • With (created_at, customer_id), Postgres puts both conditions in the index condition, but the range on the leading column means it walks every index entry for the six months and checks customer_id on each (Index Searches: 1). Postgres 18's skip scan can use an index whose leading column has only a range condition, but it pays off when that column has few distinct values; created_at is close to unique here.
  • created_at::date on a timestamptz column cannot get an expression index at all: the cast depends on the session time zone, so Postgres refuses it with "functions in index expression must be marked IMMUTABLE". (created_at AT TIME ZONE 'UTC')::date can be indexed, but the half-open range needs no extra index.
  • ClickHouse still uses the sort key for toDate(created_at), because toDate is monotonic and the index analysis knows it. formatDateTime returns a string, the analysis cannot map it back to a range of created_at, and the query reads every granule.
  • For the month query, sorting by created_at without partitions reads the same 519 granules as partitioning by month, through the primary key.
  • Partitioning by month leaves the per-customer query reading the whole table, slightly slower than the plain table. Sorting each partition by customer_id leaves one granule per partition: 24 granules of 12,219.

What this setup does not show

  • Warm caches only. Every table fits in memory after the warm-up, so a slow plan here costs CPU and memory reads; on a table larger than RAM, the full scans would also wait on disk.
  • One client at a time. A full scan that takes tens of milliseconds alone holds CPU and buffer cache that concurrent queries need.
  • Writes. Every index here makes inserts and updates slower, and that cost is not measured.
  • One laptop. Absolute times change with hardware; the ratios between variants and the pages or rows read are the portable part.