Minimize and optimize JOINs

​

Prefer avoiding JOINs

• Tables change infrequently or when batch refreshes are acceptable.
• Relationships are not many-to-many and do not have excessively high cardinality.
• Only a limited subset of the columns will be queried, i.e. certain columns can be excluded from denormalization.
• You have the capability to shift processing out of ClickHouse into upstream systems like Flink, where real-time enrichment or flattening can be managed.

​

When JOINs are required

• Use ANY JOINs when only one match is needed. A regular JOIN returns every matching row and can produce a Cartesian product for duplicate keys. Use LEFT ANY JOIN or ANY INNER JOIN when one match is sufficient to reduce result size and memory use.
• Reduce both sides before the JOIN. Apply selective conditions in WHERE and JOIN ON clauses. ClickHouse pushes filters down when possible, but explicit subqueries can ensure that each input is filtered before the JOIN.
• Consider IN instead of INNER JOIN. When a query only needs to test whether a key exists in the other table, an IN subquery expresses that requirement directly and can use less memory than producing an INNER JOIN result.
• Prefer the Join table engine for reusable lookups. The Join table engine stores a precomputed in-memory structure for a declared JOIN type, strictness, and key. In ClickHouse Cloud, it is implemented as SharedJoin with a MergeTree backing table, and Join(ANY, LEFT, key) provides an updatable per-key lookup. See Fast, updatable lookups with the Join table engine.
• Join on table sorting keys when possible. When both tables are physically ordered by the JOIN key, full_sorting_merge can skip sorting and reduce memory use. Confirm the execution plan and benchmark this algorithm against the default on representative data.
• Avoid disk-spilling JOINs. Spilling intermediate JOIN state to disk can keep a query within its memory limit, but disk I/O usually increases latency. Hash joins can switch to grace hash at configured spill thresholds, while merge joins can use external sorting. Reduce input rows and columns or change the data model before relying on spilling. See Choosing the optimal JOIN algorithm for details.
• Use default values as no-match markers when appropriate. The default join_use_nulls = 0 fills unmatched outer JOIN columns with their data type’s default value and avoids Nullable wrappers. Enable join_use_nulls only when the query must distinguish an unmatched row from a matched row containing a default value.

​

Choosing the optimal JOIN algorithm

• (1) If the data from the right-hand side table can be pre-loaded into an in-memory low-latency key-value data structure, e.g. a dictionary, and if the join key matches the key attribute of the underlying key-value storage, and if LEFT ANY JOIN semantics are adequate - then the direct join is applicable and is often the fastest approach.
• (2) If your table’s physical row order matches the join key sort order, then it depends. In this case, the full sorting merge join skips the sorting phase resulting in significantly reduced memory usage plus, depending on data size and join key value distribution, faster execution times than some of the hash join algorithms.
• (3) If the right table fits into memory, even with the additional memory usage overhead of the parallel hash join, then this algorithm or the hash join can be faster. This depends on data size, data types, and value distribution of the join key columns.
• (4) If the right table does not fit into memory, hash and parallel hash joins can spill by switching to grace hash when max_bytes_before_external_join or max_bytes_ratio_before_external_join is reached. Full sorting merge and partial merge joins can also use external storage. Which approach is fastest depends on the data volume, data types, and value distribution of the join key columns.

​

Optimizing for memory

• (1) If your table’s physical row order matches the join key sort order, then the memory usage of the full sorting merge join is as low as it gets with the additional benefit of good join speed because the sorting phase is disabled.
• (2) The grace hash join can be tuned for very low memory usage by increasing grace_hash_join_initial_buckets at the expense of join speed. The partial merge join intentionally uses a low amount of main memory. The full sorting merge join with external sorting enabled generally uses more memory than the partial merge join when the row order does not match the key sort order, with the benefit of significantly better join execution time. Hash and parallel hash joins can also reduce memory pressure by switching to grace hash at the configured spill threshold.

​

JOIN optimization examples

​

Replace ANY INNER JOIN with IN

SELECT count()
FROM stackoverflow.posts AS p
ANY INNER JOIN stackoverflow.comments AS c ON p.Id = c.PostId
WHERE
    p.Title != ''
    AND p.Title NOT ILIKE '%clickhouse%'
    AND p.Body NOT ILIKE '%clickhouse%'
    AND c.Text ILIKE '%clickhouse%'

SELECT count()
FROM stackoverflow.posts
WHERE
    Title != ''
    AND Title NOT ILIKE '%clickhouse%'
    AND Body NOT ILIKE '%clickhouse%'
    AND Id IN
    (
        SELECT PostId
        FROM stackoverflow.comments
        WHERE Text ILIKE '%clickhouse%'
    )

​

Filter both sides before replacing the JOIN

SELECT countIf(v.VoteTypeId = 2) AS upvotes
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.votes AS v ON p.Id = v.PostId
WHERE
    p.CreationDate >= '2020-01-01'
    AND v.CreationDate >= '2020-01-01'
    AND has(arrayFilter(t -> t != '', splitByChar('|', p.Tags)), 'java')

SELECT count() AS upvotes
FROM stackoverflow.votes
WHERE
    VoteTypeId = 2
    AND CreationDate >= '2020-01-01'
    AND PostId IN
    (
        SELECT Id
        FROM stackoverflow.posts
        WHERE
            CreationDate >= '2020-01-01'
            AND has(arrayFilter(t -> t != '', splitByChar('|', Tags)), 'java')
    )

​

Further reading

• Joining tables
• JOIN clause reference
• Dictionary best practices
• Full SQL JOIN support, Part 1
• Full SQL JOIN support, Part 2
• Full SQL JOIN support, Part 3
• Full SQL JOIN support, Part 4