ClickHouse Evaluates IN with Hash Sets

TL;DR: IN is not a comparison, it is a hash set. ClickHouse picks one of thirteen specialisations based on the shape of the right-hand side, packs integer keys into the hash table’s own key, and — if the set grows past half your memory — spills it to disk behind per-block Bloom filters.

x IN (1, 2, 3) looks like a comparison. x IN (SELECT user_id FROM logins WHERE …) looks like a join. Under the hood, both become the same thing: a hash set, built once, probed once per row of the left-hand side.

The interesting part isn’t “it is a hash table” — it’s which hash table. ClickHouse picks a different data structure for every shape of the right-hand side, and the set can get big enough that it no longer fits in memory and has to spill to disk behind Bloom filters.

Let’s walk it.

The set is a plan artifact, not a function argument

The IN operator is a normal SQL function whose second argument is not a value but a set object:

cpp
// src/Functions/in.cpp:145
        ColumnPtr column_set_ptr = arguments[1].column;
        const ColumnSet * column_set = tryGetColumnSet(column_set_ptr);
        if (!column_set)
            throw Exception(ErrorCodes::ILLEGAL_COLUMN, "Second argument for function '{}' must be Set; found {}",
                getName(), column_set_ptr->getName());

        auto future_set = column_set->getData();
        if (!future_set)
        {
            if (dry_run)
                return ColumnUInt8::create(input_rows_count, static_cast<UInt8>(0));

            throw Exception(ErrorCodes::LOGICAL_ERROR, "No Set is passed as the second argument for function '{}'", getName());
        }

The set arrives as a ColumnSet holding a FutureSet — a promise, because the set may not be built yet when the function’s type is resolved. A separate step in the pipeline builds it, and the function then asks for it:

cpp
// src/Functions/in.cpp:160
        auto set = future_set->get();
        if (!set)
        {
            if (dry_run)
                return ColumnUInt8::create(input_rows_count, static_cast<UInt8>(0));

            throw Exception(ErrorCodes::LOGICAL_ERROR, "Not-ready Set is passed as the second argument for function '{}'", getName());
        }

There are three kinds of FutureSet:

cpp
// src/Interpreters/PreparedSets.h:66
class FutureSet
{
public:
    virtual ~FutureSet() = default;

    /// Returns set if set is ready (created and filled) or nullptr if not.
    virtual SetPtr get() const = 0;
    /// Returns set->getElementsTypes(), even if set is not created yet.
    virtual DataTypes getTypes() const = 0;
    /// If possible, return set with stored elements useful for PK analysis.
    virtual SetPtr buildOrderedSetInplace(const ContextPtr & context) = 0;

FutureSetFromTuple — the right-hand side is a literal list, IN (1, 2, 3). It is built during analysis, because the values are already known.
FutureSetFromSubquery — the right-hand side is a SELECT. It is built during execution, by a CreatingSetStep that runs before the steps that use it.
FutureSetFromStorage — the right-hand side is an ENGINE = Set table. The set already exists; the query only reads it.

The literal case is where the “prepare once, use many” idea shows up, because the same literal list may appear many times in a query:

cpp
// src/Interpreters/ActionsVisitor.cpp:557
    auto set_key = right_arg->getTreeHash(/*ignore_aliases=*/ true);
    if (auto set = prepared_sets.findTuple(set_key, set_element_keys))
        return set; /// Already prepared.

    return prepared_sets.addFromTuple(set_key, right_arg_func, std::move(block), context->getSettingsRef());

The key is the tree hash of the AST of the right-hand side, so x IN (1,2,3) and y IN (1,2,3) share one set. The prepared sets live in a per-query container:

cpp
// src/Interpreters/PreparedSets.h:301
    using SetsFromTuple = std::unordered_map<Hash, std::vector<FutureSetFromTuplePtr>, Hashing>;
    using SetsFromStorage = std::unordered_map<Hash, FutureSetFromStoragePtr, Hashing>;
    using SetsFromSubqueries = std::unordered_map<Hash, FutureSetFromSubqueryPtr, Hashing>;

The hash is 128 bits of CityHash, and the container hashes it down to 64 bits:

cpp
// src/Interpreters/PreparedSets.h:278
struct FutureSetHashing
{
    UInt64 operator()(const FutureSet::Hash & key) const { return key.low64 ^ key.high64; }
};

The shape of the thing

text
   SELECT ... WHERE x IN (SELECT id FROM t)
                    |
                    v
             CreatingSetStep  ---> FutureSetFromSubquery
                                        |  (built during execution, not analysis)
                                        v
   ActionsDAG:  FunctionIn(x, ColumnSet)  ---> Set::execute(key_columns)
                                                |
                    chooseMethod(key_columns) -+--> key8 / key16 / key32 / key64
                                                |     (one integer key: the key IS the slot)
                                                +--> keys32 / keys64 / keys128 / keys256
                                                |     (fixed-width tuple: packed into one word)
                                                +--> key_string / key_fixed_string
                                                |     (one string: chars arena + saved hash)
                                                +--> nullable_keys128 / nullable_keys256
                                                |     (value + null flag packed together)
                                                +--> hashed
                                                      (anything else: 128-bit hash only)

Choosing the data structure

This is the part that makes ClickHouse’s IN fast. The set’s key type is not “some value” — it is a tuple of columns with concrete types, and each shape deserves a different hash table.

SetVariants::chooseMethod inspects the key columns and picks one of thirteen implementations:

cpp
// src/Interpreters/SetVariants.h:417
    #define APPLY_FOR_SET_VARIANTS(M) \
        M(key8)                 \
        M(key16)                \
        M(key32)                \
        M(key64)                \
        M(key_string)           \
        M(key_fixed_string)     \
        M(keys32)               \
        M(keys64)               \
        M(keys128)              \
        M(keys256)              \
        M(nullable_keys128)     \
        M(nullable_keys256)     \
        M(hashed)

The decision tree, in order:

One nullable numeric key. The value and the NULL flag are packed into one 16-byte key, so the hash table needs no separate null handling:

cpp
// src/Interpreters/SetVariants.cpp:149
    if (has_nullable_key)
    {
        /// At least one key is nullable. Therefore we choose a method
        /// that takes into account this fact.
        if ((keys_size == 1) && (nested_key_columns[0]->isNumeric()))
        {
            /// We have exactly one key and it is nullable. We shall add it a tag
            /// which specifies whether its value is null or not.
            size_t size_of_field = nested_key_columns[0]->sizeOfValueIfFixed();
            if ((size_of_field == 1) || (size_of_field == 2) || (size_of_field == 4) || (size_of_field == 8))
                return Type::nullable_keys128;

One numeric key. A single UInt32 column gets a HashSet<UInt32> — the key is the hash-table key, no packing, no hashing beyond the CRC:

cpp
// src/Interpreters/SetVariants.cpp:180
    /// If there is one numeric key that fits into 64 bits
    if (keys_size == 1 && nested_key_columns[0]->isNumeric() && !nested_key_columns[0]->lowCardinality())
    {
        size_t size_of_field = nested_key_columns[0]->sizeOfValueIfFixed();
        if (size_of_field == 1)
            return Type::key8;
        if (size_of_field == 2)
            return Type::key16;
        if (size_of_field == 4)
            return Type::key32;
        if (size_of_field == 8)
            return Type::key64;

Several fixed-width keys. They are packed into 32/64/128/256 bits and the whole packed word is the key:

cpp
// src/Interpreters/SetVariants.cpp:199
    /// If the keys fit in N bits, we will use a hash table for N-bit-packed keys
    if (all_fixed && keys_bytes <= 4)
        return Type::keys32;
    if (all_fixed && keys_bytes <= 8)
        return Type::keys64;
    if (all_fixed && keys_bytes <= 16)
        return Type::keys128;
    if (all_fixed && keys_bytes <= 32)
        return Type::keys256;

One string key. A dedicated method with the string’s own arena:

cpp
// src/Interpreters/SetVariants.cpp:209
    /// If there is single string key, use hash table of it's values.
    if (keys_size == 1
        && (typeid_cast<const ColumnString *>(nested_key_columns[0])
            || (isColumnConst(*nested_key_columns[0]) && typeid_cast<const ColumnString *>(&assert_cast<const ColumnConst *>(nested_key_columns[0])->getDataColumn())))
        return Type::key_string;

Anything else. The fallback hashes the whole tuple to 128 bits and stores only the hash — the original values are not kept:

cpp
// src/Interpreters/SetVariants.cpp:218
    /// Otherwise, will use set of cryptographic hashes of unambiguously serialized values.
    return Type::hashed;

The comment in the header explains the memory trade-off of that last choice:

cpp
// src/Interpreters/SetVariants.h:357
    /** Unlike Aggregator, `concat` method is not used here.
      * This is done because `hashed` method, although slower, but in this case, uses less RAM.
      *  since when you use it, the key values themselves are not stored.
      */

Presence-only vs counting

There are two flavours of the same set family. A plain IN only needs to know whether a key is present, so it uses a HashSet whose cell maps to VoidMapped. INTERSECT ALL / EXCEPT ALL need how many times a key occurs, so they use a HashMap with a UInt64 counter. The mapping is derived from the table rather than passed in, so a method stays “a set” when given a HashSet and becomes a multiset when given a HashMap:

cpp
// src/Interpreters/SetVariants.h:30
/// The method structs below are shared between presence-only sets (a `HashSet`, whose cell reports
/// `VoidMapped`) and counting multisets (a `HashMap`, with a real mapped type, used by INTERSECT
/// ALL / EXCEPT ALL). The mapped type is derived from the table rather than passed explicitly, so a
/// method stays "a set" when given a `HashSet` and becomes a counting multiset when given a `HashMap`.
template <typename TData>
using SetMethodMapped = std::conditional_t<std::is_same_v<typename TData::mapped_type, VoidMapped>, void, typename TData::mapped_type>;

The choice of table type per key shape is visible in the variant declarations:

cpp
// src/Interpreters/SetVariants.h:344
    std::unique_ptr<SetMethodOneNumber<UInt32, HashSet<UInt32, HashCRC32<UInt32>>>>          key32;
    std::unique_ptr<SetMethodOneNumber<UInt64, HashSet<UInt64, HashCRC32<UInt64>>>>          key64;
    std::unique_ptr<SetMethodString<HashSetWithSavedHash<std::string_view>>>                        key_string;
    std::unique_ptr<SetMethodFixedString<HashSetWithSavedHash<std::string_view>>>                   key_fixed_string;
    std::unique_ptr<SetMethodKeysFixed<HashSet<UInt32, HashCRC32<UInt32>>>>                  keys32;
    std::unique_ptr<SetMethodKeysFixed<HashSet<UInt64, HashCRC32<UInt64>>>>                  keys64;
    std::unique_ptr<SetMethodKeysFixed<HashSet<UInt128, UInt128HashCRC32>>>                  keys128;
    std::unique_ptr<SetMethodKeysFixed<HashSet<UInt256, UInt256HashCRC32>>>                  keys256;
    std::unique_ptr<SetMethodHashed<HashSet<UInt128, UInt128TrivialHash>>>                   hashed;

HashSetWithSavedHash is worth a note: for strings it stores the hash next to the string, so a probe that hits the same bucket doesn’t have to re-hash the candidate key.

Building the set

Insertion goes through emplaceKey on the method’s state, one row at a time, with a twist: the same loop can build a deduplication filter, which records which rows were new. That filter is how the set keeps the original values around for primary-key analysis:

cpp
// src/Interpreters/Set.cpp:370
    typename Method::State state(key_columns, key_sizes, nullptr);

    /// For all rows
    for (size_t i = 0; i < rows; ++i)
    {
        if constexpr (has_null_map)
        {
            if ((*null_map)[i])
            {
                if constexpr (build_filter)
                {
                    (*out_filter)[i] = false;
                }
                continue;
            }
        }

        [[maybe_unused]] auto emplace_result = state.emplaceKey(method.data, i, variants.string_pool);

        if constexpr (build_filter)
            (*out_filter)[i] = emplace_result.isInserted();
    }

emplace_result.isInserted() is the deduplication: a row whose key was already in the set is not new, so it gets filtered out of the “explicit set elements” — the copy of the values kept for index analysis.

Keeping the values is optional and bounded. When the set grows past max_elements_to_fill, the copy is dropped and the set keeps only the hashes:

cpp
// src/Interpreters/Set.cpp:495
    bool inserted = insertFromColumnsImpl(columns, holder);
    if (inserted && fill_set_elements)
    {
        if (max_elements_to_fill && max_elements_to_fill < data->getTotalRowCount())
        {
            /// Drop filled elementes
            fill_set_elements = false;
            set_elements.clear();
        }
        else
            appendSetElements(holder);
    }

That’s a graceful degradation: the set still answers IN correctly, it just can no longer be used to prune granules by primary key. The threshold is the setting use_index_for_in_with_subqueries_max_values.

Probing the set

The probe is the hot path: it runs once per row of the left-hand side.

cpp
// src/Interpreters/Set.cpp:1002
    Arena pool;
    typename Method::State state(key_columns, key_sizes, nullptr);

    /// Clustered key columns (e.g. a primary key prefix) arrive in runs of equal consecutive
    /// rows. The consecutive-keys optimization in ColumnsHashing handles them inside `findKey`:
    /// the last-element cache compares the key with the previous row's before probing the hash
    /// table, and `HashMethodHashed` additionally compares the raw key bytes before even
    /// calculating the hash.
    for (size_t i = 0; i < rows; ++i)
    {
        if (has_null_map && (*null_map)[i])
        {
            vec_res[i] = negative;
        }
        else
        {
            auto find_result = state.findKey(method.data, i, pool);
            vec_res[i] = negative ^ find_result.isFound();
        }
    }

Two things worth noticing.

The “last element cache”. When the input is sorted (or clustered by a primary key), consecutive rows often have the same key. The hashing state remembers the last key and its answer, so a repeat costs one comparison instead of a hash computation plus a probe. This optimisation is on for numeric keys:

cpp
// src/Interpreters/SetVariants.h:44
template <typename FieldType, typename TData, bool use_cache = true>    /// UInt8/16/32/64 for any types with corresponding bit width.
struct SetMethodOneNumber
{
    using Data = TData;
    using Key = typename Data::key_type;

    Data data;

    using State = ColumnsHashing::HashMethodOneNumber<typename Data::value_type,
        SetMethodMapped<Data>, FieldType, set_method_use_cache<Data, use_cache>>;

and off for strings, with a measurement in the comment:

cpp
// src/Interpreters/SetVariants.h:62
/// For the case where there is one string key.
/// Unlike aggregation, the consecutive-keys cache stays disabled for string keys: a set probe's
/// per-row work is just hash+lookup, which for strings is cheaper than the extra bytewise
/// comparison the cache check performs on every miss. Measured 2026-08 on 20M-row `IN` probes:
/// enabling the cache made unclustered inputs 40% slower for short `String` keys and 24% slower
/// for `FixedString(36)` keys, while clustered inputs (runs of equal consecutive keys) improved
/// only 8-16%. In aggregation the same check is relatively cheap because the per-row work there
/// (aggregate-state updates) is much heavier.
template <typename TData>
struct SetMethodString
{
    using Data = TData;
    using Key = typename Data::key_type;

    Data data;

    using State = ColumnsHashing::HashMethodString<typename Data::value_type, SetMethodMapped<Data>, true, false>;

That is a rare and welcome thing to find in a codebase: an optimisation that was measured, found harmful for one type family, and turned off — with the numbers kept in the source. Most codebases just leave the “optimisation” in and wonder why strings are slower than integers.

The NULL rule. A row whose key contains a NULL is never in the set, so the answer is negative (0 for IN, 1 for NOT IN) without a lookup. The rationale is in the header:

cpp
// src/Interpreters/Set.h:202
    /** How IN works with Nullable types.
      *
      * For simplicity reasons, all NULL values and any tuples with at least one NULL element are ignored in the Set.
      * And for left hand side values, that are NULLs or contain any NULLs, we return 0 (means that element is not in Set).
      *
      * If we want more standard compliant behaviour, we must return NULL
      *  if lhs is NULL and set is not empty or if lhs is not in set, but set contains at least one NULL.
      *  It is more complicated with tuples.
      * For example,
      *      (1, NULL, 2) IN ((1, NULL, 3)) must return 0,
      *  but (1, NULL, 2) IN ((1, 1111, 2)) must return NULL.
      *
      * We have not implemented such sophisticated behaviour.
      */

So NULL IN (1, 2, 3) is 0, not NULL. A deliberate deviation from the SQL standard, documented in the source. Gotcha if you are porting queries from a stricter engine.

Type coercion on the probe

The left-hand side of the IN may have a different type from the set’s key — the set’s type wins, and the probe column is cast. The cast is cached, so it isn’t rebuilt for every block:

cpp
// src/Interpreters/Set.h:249
    /// A cache for cast functions (if any) to avoid rebuilding cast functions
    /// for every call to `execute`
    mutable std::unique_ptr<InternalCastFunctionCache> cast_cache;

and when the value does not fit the key type, it is not an error — it is simply “not a member”:

cpp
// src/Interpreters/Set.cpp:833
            if (can_convert_leniently)
            {
                /// `transform_null_in` makes NULL an ordinary matchable value, so the accurate-or-null
                /// cast above is not taken. A value the key type cannot represent is still simply not a
                /// member - which is what the literal `IN` list concludes, because it compares in a
                /// common supertype - but converting it strictly raised `CANNOT_CONVERT_TYPE` on rows
                /// the read path happened to deliver, so `v IN set_table` failed where `v IN (1)`
                /// returned 0.

Size limits and overflow

The set is subject to max_rows_in_set and max_bytes_in_set, checked after every inserted chunk:

cpp
// src/Interpreters/Set.cpp:556
    bool within_limits = limits.check(data->getTotalRowCount(), data->getTotalByteCount(), "IN-set", ErrorCodes::SET_SIZE_LIMIT_EXCEEDED);
    if (!within_limits)
        is_truncated = true;

The overflow mode is set_overflow_mode: throw (the default) aborts the query, break truncates the set and returns the partial result — which is wrong for IN, but is the only sane option for a GLOBAL IN over a huge table, where a partial set is still useful.

Spilling to disk

A set built from a subquery can be enormous: x IN (SELECT id FROM big_table) over a billion rows. ClickHouse 26.10 added spilling: the set moves to a temporary file, and the in-memory part shrinks to a directory plus a set of Bloom filters.

The disk representation is not the original values. Each key is converted into a compact disk key: numbers and FixedStrings of at most 32 bytes are packed into integers, and everything else becomes a 128-bit hash:

cpp
// src/Interpreters/Set.h:85
    /// the keys that come first in the order of their disk keys rather than the first ones to arrive. Disk
    /// keys keep the order of the keys that the set packs into integers in memory, numbers and `FixedString`
    /// values of at most 32 bytes in total, with `NULL` after the values, so these are the smallest keys.
    /// Other keys, such as `String`, are stored as 128-bit hashes, whose order is arbitrary.

The keys are then sorted, so a lookup can binary-search the directory and find the block that may contain the key.

The Bloom filters

Each block of the set gets its own Bloom filter, and all the filters share one array of bits:

cpp
// src/Interpreters/DiskSetBlockFilters.h:15
/// Bloom filters of the blocks of a set on disk, which the set keeps in memory. A lookup checks the filter of
/// the only block that can hold its key, and reads the block only when the filter lets the key pass.
///
/// The filters share one array of bits. Each block takes a range of it in proportion to its keys, and each
/// key sets a few bits in the range of its block. A single filter for all keys would take as much memory, but
/// a key would set bits anywhere in it, leading to writes at random places all over the array. For a large
/// set, the array is far larger than the cache, so each such write would miss the cache and the TLB, and
/// these misses would slow down writing the set, because the set fills the filters on the single thread that
/// writes its blocks. The range of a block is about as small as the block, so it stays in the cache while the
/// set writes the block. Lookups check the ranges in the order of the blocks, as they read the blocks.

That’s a lovely piece of engineering: the filters are per block so a write touches only a cache-resident range, not the whole (possibly huge) filter array. Think of it as giving each block its own little padlock instead of one giant lock everyone fights over.

The filter is sized by the number of distinct keys, which is estimated with a HyperLogLog counter while the set is being filled:

cpp
// src/Interpreters/DiskSetBlockFilters.h:29
    /// Counts the distinct keys of a set as they arrive, so that the bloom filters can be sized before the set
    /// writes its blocks. With 2^20 buckets, the estimate is within about 0.1% and the counter takes only 768 KiB.
    class DistinctKeyCounter
    {
    public:
        void add(std::span<const Key> keys)
        {
            for (const Key & key : keys)
                counter.insert(hashKey(key));
        }

and the bits-per-key is chosen to fit the memory budget, trading false positives for memory:

cpp
// src/Interpreters/DiskSetBlockFilters.h:46
    /// Creates the filters for about `expected_keys` keys. They take 8 bits per key, which lets about 3% of
    /// the keys that the set does not have pass. When that does not fit in `max_bytes` bytes with the ranges
    /// of the blocks, they take fewer bits per key, so that they fit: with 2 bits per key, about 40% of these
    /// keys pass, and with 1 bit per key, about 63%. Every block but the last holds at least
    /// `min_keys_per_block` keys, which bounds the number of ranges. Returns nothing when fewer than 1 bit
    /// per key fits, because such filters let so many keys pass that they do not pay for filling them.

The number of hash positions per key is capped at 3, with the reasoning spelled out:

cpp
// src/Interpreters/DiskSetBlockFilters.h:82
    /// A key sets the bits at most at this many positions in the range of its block. Each position costs a
    /// memory access when a lookup checks a key that the set has, while more positions let only slightly
    /// fewer other keys pass: with 8 bits per key, 3 positions let about 3.1% of them pass, and 6 positions,
    /// the most effective number, about 2.2%.
    static constexpr size_t max_positions_per_key = 3;

The lookup

A lookup against the disk set is a five-step algorithm: collect the probes, sort them, find the blocks, consult the filters, read the blocks.

cpp
// src/Interpreters/DiskSet.cpp:121
    /// Step 1: Collect the probes: the rows whose keys are in the range of the set, except a row that repeats
    /// the key of the previous row, which takes that row's answer in step 5. So a run of equal keys, as in a
    /// sorted column, takes one probe.
    struct Probe
    {
        Key key;
        size_t row;
    };

    VectorWithMemoryTracking<Probe> probes;
    probes.reserve(keys.size());
    const Key & first_key = directory.getFirstKey(0);
    for (size_t row = 0; row < keys.size(); ++row)
    {
        const auto & key = keys[row];
        if ((row == 0 || key != keys[row - 1]) && first_key <= key && key <= last_key)
            probes.push_back({key, row});
    }

Note the range check first_key <= key && key <= last_key: keys outside the set’s key range cannot be in the set, and are answered without any I/O.

Then the probes are sorted, so that each block is read once and in order:

cpp
// src/Interpreters/DiskSet.cpp:143
    /// Step 2: Sort the probes by key. Probes of equal keys then come together, so each distinct key is
    /// looked up once, and the probes of each block come together in block order, so they share a read.
    /// Results go back to the original rows, so evaluating `IN` does not reorder the input.
    ::sort(probes.begin(), probes.end(), [](const Probe & lhs, const Probe & rhs) { return lhs.key < rhs.key; });

and the filters decide which blocks are actually read:

cpp
// src/Interpreters/DiskSet.cpp:174
    for (size_t begin = 0; begin < probes.size();)
    {
        size_t end = begin + 1;
        while (end < probes.size() && probes[end].key == probes[begin].key)
            ++end;

        block = directory.findBlock(probes[begin].key, block);
        if (!filters || filters->mayContain(block, probes[begin].key))
        {
            if (lookups.empty() || block != lookups.back().block)
                needed_blocks.push_back(block); /// Each block once, in ascending order
            lookups.push_back({block, begin, end});
        }
        else
        {
            for (size_t i = begin; i < end; ++i)
                skipped_rows += count_rows_of_probe(probes[i]);
        }
        begin = end;
    }

The number of rows the filters answered is a profile event, so the effectiveness of the filters is directly observable:

cpp
// src/Interpreters/DiskSet.cpp:195
    /// A skipped key counts once for each of its rows, as `ExternalSetLookupKeys` does, so that the two
    /// events give the share of the rows that the filters answer.
    ProfileEvents::increment(ProfileEvents::ExternalSetBloomFilterSkippedKeys, skipped_rows);

When does it spill?

The threshold is a memory budget, and the default is “half of the available memory”:

cpp
// src/Core/Settings.cpp:3905
    DECLARE(Double, max_bytes_ratio_before_external_set, 0.5, R"(
Fraction of available server or user memory used to calculate the threshold for spilling the set of `IN`
with a subquery to disk, at the start of execution. For example, `0.5` uses half of the available memory.

The set spills while it is built and while it is used, and the two cases are handled differently. While it is built, the keys are handed to a DiskSetBuilder that sorts them into runs; while it is used, the keys are written out while the lookups keep reading the in-memory table, and the disk set replaces it at the end.

The bloom filter has its own budget:

cpp
// src/Core/Settings.cpp:3917
    DECLARE(UInt64, max_bytes_for_external_set_bloom_filter, 128_MiB, R"(
Memory budget, in bytes, for the Bloom filter that each set of `IN` written to disk keeps in memory. The
filter lets lookups of values that are not in the set skip reading the disk. It does not speed up lookups
of values that are in the set.

Note the asymmetry in that sentence: the filter helps for misses, not for hits. A key that is in the set still costs a block read. That’s the fundamental trade of a Bloom filter, and it’s why the filter is only worth it when the selectivity is low.

Trade-offs

What it buys: IN over a subquery is a hash-table probe per row, with a data structure chosen to fit the key type exactly. No row-at-a-time interpretation, no generic comparison, and for the common cases (one integer key, one string key) the key is the hash-table key.

What it costs:

Memory. The set is proportional to the number of distinct values on the right-hand side. x IN (SELECT id FROM orders) with 100 M distinct ids is a 100 M-entry hash table. This is why the spilling machinery exists, and why max_rows_in_set exists.
Spilling is much slower than RAM. A probe against a disk set is a sort, a binary search, a Bloom filter check and possibly a block read. ClickHouse sorts the probe keys to make the reads sequential, which is a lot of work per block. The alternative — failing the query with SET_SIZE_LIMIT_EXCEEDED — is often better for the user.
The hash fallback loses information. For a heterogeneous key, the set stores a 128-bit hash of the tuple, not the tuple. Two different tuples with the same hash would be “equal”; the probability is negligible, but it is not zero.
NULL semantics deviate from the standard, as documented above.
The hashed method is slower per probe than the packed methods, because the key must be serialised and hashed on every probe rather than once at build time. For a set of strings, the string method stores the string and its hash; the hashed method stores only the hash.

That last point is the general lesson of the whole design: ClickHouse’s IN is fast not because hashing is fast, but because the shape of the data is known at analysis time and the implementation specialises on it — thirteen variants, each with its own hash function, its own packing, its own arena, and its own micro-optimisations, some of which are enabled and some of which are deliberately disabled after measurement.

Wrapping up

The one thing to remember: IN is a hash set, and the set’s data structure is chosen from the type of the right-hand side at analysis time. That is why IN (1,2,3) on an integer column is essentially free.

What to go do about it: if you have a query with IN (SELECT ...) over a big table, check whether the right-hand side has an index or a small enough cardinality. If neither, the hash set is going to be the bottleneck — and spilling it to disk makes it worse, not better.

The part people get wrong: a Bloom filter in front of a disk set only helps with misses. If your IN mostly hits, the filter is pure overhead.