When IN beats a JOIN in ClickHouse

A selective query can still read most of a ClickHouse table. I ran into this while tuning a scheduled aggregation: the result contained only a small set of assets, but the query read roughly 70% of the table's granules before the join discarded almost everything.

Rewriting the join as IN produced the same result and cut the read dramatically. The small subquery was not cheaper; it ran once in either query. The difference was when its result became available to the storage layer.

Setup

The production schema was more involved. This synthetic table keeps the part that matters:

CREATE TABLE prices
(
    asset_id String,
    ts DateTime,
    price Float64,
    volume Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (asset_id, ts);

The important detail is that asset_id participates in the ordering key. That gives the sparse primary index a chance to rule out ranges belonging to assets the query does not need.

WITH target_assets AS
(
    SELECT asset_id
    FROM asset_labels
    WHERE label = 'reference'
)
SELECT
    p.asset_id,
    avgWeighted(p.price, p.volume)
FROM prices AS p
INNER JOIN target_assets AS t USING (asset_id)
WHERE p.ts >= now() - INTERVAL 30 DAY
GROUP BY p.asset_id;

Why the JOIN read too much

The target set was small, so this looked reasonable. For the hash join, ClickHouse built the right side and then read the rows fromprices selected by the time predicate. Each row was probed against the hash table and most were discarded. A selective join result did not imply a selective table read.

Rewriting with IN

WITH target_assets AS
(
    SELECT asset_id
    FROM asset_labels
    WHERE label = 'reference'
)
SELECT
    p.asset_id,
    avgWeighted(p.price, p.volume)
FROM prices AS p
WHERE p.ts >= now() - INTERVAL 30 DAY
  AND p.asset_id IN (SELECT asset_id FROM target_assets)
GROUP BY p.asset_id;

IN expresses the operation as a semi-join in theWHERE clause. With a small subquery result, ClickHouse can materialize the set while analysing the read and include it in the primary-key condition before selecting granules.

The primary index is sparse: it does not identify every matching row. It rules out granules whose key ranges cannot contain any target asset. Surviving granules still receive an exact row-level filter, which may also be moved to PREWHERE so wider columns are read later.

Fun fact: for this form, the subquery is materialized during index analysis, before the main table scan. That gives the query planner the concrete asset set it needs to calculate granule ranges. It also means EXPLAIN indexes = 1 may execute the subquery while producing its granule counts.

Results and semantics

On the original workload, rounded to avoid exposing production scale, granules read fell from about 70% of the table to well below 1%. Runtime moved from seconds to sub-second, with a similar reduction in peak memory.

Those numbers are less important than the shape of the improvement. A small SQL rewrite changed which data ClickHouse had to read; it did not merely make the same scan slightly faster.

The question was “keep price rows whose asset appears in this set.” That is semi-join semantics. IN cannot multiply rows if the label source contains duplicate asset IDs; an INNER JOINcan. The faster form also stated the intended result more directly.

When to use each

Use IN when:

• The right side is only a membership test.
• The set is small and selective.
• The filtered column participates in the ordering key.

Keep the JOIN when:

• The query needs columns from the right-hand table.
• The membership set is too large to materialize comfortably.
• The filter matches most of the table, leaving little to prune.

How to verify the difference

• Run EXPLAIN indexes = 1 for both forms and compare selected parts and granules.
• Compare read_rows, read_bytes, and peak_memory_usage in system.query_log.
• Check whether the small set participates in the primary-key condition.

Rule of thumb

Join selectivity and storage pruning are different things. A join can produce very few rows while the left side still reads an enormous range. When the operation is really membership in a small set, write it that way and confirm the change at the granule level.