Optimize memory usage for joins
ClickHouse provides different algorithms for joins operations. By default, the
hash algorithm is used, which loads the right hand side of the join into memory, which can result in very high memory usage.
It is possible to choose which join algorithm to use, so you can pick the best algorithm for your use case.
This is done using the
join_algorithm setting; you can find the possible algorithms here.
) USING (number)
SETTINGS join_algorithm = 'partial_merge'
You can also use
join_algorithm='auto' and let ClickHouse automatically decide which algorithm to use.