Repository navigation
Further improve performance of IN list evaluation #19241
Description
Activity
Amazing plan!
What about
slice::contains? Seems like it should be somewhere between the const-sized approach and binary search in terms of threshold window.There is a related bit of work to untangle with is the ScalarValue references that
InListExpris forced to use even if we are starting from an array. It uses these to look up in bloom filters, do predicate pruning, etc. We could make all of the relevant APIs work with an enum ofVec<ScalarValue>(heterogenous lists) orArrayRefs (homogenous lists) and that would avoid converting array -> ScalarValue and then in some places back to an array (deep in pruning code iirc). Some of this is planning time / build time stuff so it is amortized over the data scans, but some of it happens for each file opened. It's not as big as for each row, but it adds up.A second thing is that
col IN (...)is inefficient when it hits a bloom filter oncolif the list is large because it loops over the values in the list. I'm not sure how or where we would do this but in theory we could build a bloom filter out of the InListExpr and then do a binary operation between that bloom filter and Parque's bloom filter instead of looping over each item in the list and looking it up in the columns bloom filter. At the very least if we did the point above and pushed down an array we could probably be more efficient about converting all of the values into something we can look up in the bloom filter (currently it goes through ScalarValue).What about
slice::contains? Seems like it should be somewhere between the const-sized approach and binary search in terms of threshold window.It loses all the time against the branchless and the binary search.
slice::containsis just a wrapper around:self.iter().any(|y| y == x)
So it needs to do bounds checks and can't optimize for the small arrays.
What about
slice::contains? Seems like it should be somewhere between the const-sized approach and binary search in terms of threshold window.It loses all the time against the branchless and the binary search.
slice::containsis just a wrapper around:self.iter().any(|y| y == x)
So it needs to do bounds checks and can't optimize for the small arrays.Hmm interesting. I would have thought it would be faster somwehere in between.
Note it is not equal toself.iter().any(|y| y == x),slice::containsis specialized for primitive types to create unrolled/vectorized code.@Dandandan See geoffreyclaude#14 for an in-depth micro benchmark and analysis of the different search algorithms.
TL;DR: It's always branchless up to the SIMD limit, then hashset.
Slice Search Benchmark
Reacted by Daniël HeresI've opened #19376 as a preliminary PR to extend the benchmarks.
- added a commit that references this issue
on Dec 18, 2025 - Type Normalization
Note you can potentially use the
RowFormatfor this purpose - https://docs.rs/arrow-row/latest/arrow_row/, perhaps as a fallback when more general methods aren't availableIt handles pretty much every arrow type
The downside is that the input needs to be first converted into row format
- added a commit that references this issue
on Dec 22, 2025 - added a commit that references this issue
on Apr 16, 2026 - added a commit that references this issue
on Apr 21, 2026 - added a commit that references this issue
on Apr 27, 2026 - added a commit that references this issue
on Apr 29, 2026 - added a commit that references this issue
on Jun 22, 2026 2 remaining items
- added a commit that references this issue
on Jun 29, 2026 - added a commit that references this issue
on Jul 3, 2026 - added a commit that references this issue
on Jul 5, 2026 - added a commit that references this issue
on Jul 7, 2026 - added a commit that references this issue
on Jul 26, 2026 - added a commit that references this issue
on Aug 3, 2026 - added a commit that references this issue
on Aug 18, 2026 - added a commit that references this issue
on Aug 26, 2026 - added a commit that references this issue
on Sep 6, 2026 - added a commit that references this issue
on Sep 13, 2026 @geoffreyclaude -- is this epic done? It looks like we have these left?
@geoffreyclaude -- is this epic done? It looks like we have these left?
Yes, we still have these two. I need to update #25186 following reviews, and #25187 should follow. No reason they can't make it to the DF56 release!
Reacted by Andrew Lamb@alamb Sorry for the delay, this was trickier than anticipated!
I gave it some thought and suggest inverting the two PRs order. First the optimization OR-rewrite, and then the signed zero bug fix. Simply because the signed zero issue is pre-existing and might need a deeper discussion.
I've rebased #25187 and opened it for review. It's not trivial though as the gains depend on the data type: for some we should keep the OR-rewrite.
Summary
IN LISTevaluates expressions such as:When the right-hand list is constant, DataFusion can build a membership filter once and reuse it for every input row. Dynamic-filter pushdown can apply the same check millions of times during a scan.
This epic adds exact lookup strategies selected by physical representation and non-null list length. If none applies, DataFusion uses the generic static filter.
All strategies share the same result construction, so this work does not change SQL behavior for
IN,NOT IN, input nulls, nulls in the list, dictionaries, or sliced arrays.Stack
The PRs should be read in this order.
Landed
UInt8UInt16Int8andInt16Float16Decimal128listsFixedSizeBinarywidthsRemaining
Utf8ViewandBinaryViewlistsHow the strategies work
Generic fallback
#21927 improves the path available to every supported type. It precomputes Arrow hashes for the constant list, stores list indexes in a compact hash table, and uses Arrow's exact comparator to confirm equality. Membership is produced as a bitmap and then combined with input validity, list nulls, and
NOT INsemantics by shared result-building code.Bitmap lookup for small fixed domains
A one- or two-byte value has only 256 or 65,536 possible bit patterns. That entire domain can be represented by one bit per pattern:
UInt8andInt8use a 256-bit (32-byte) bitmap.UInt16,Int16, andFloat16use a 65,536-bit (8 KiB) bitmap.Building the filter sets the bit corresponding to each non-null list value. Probing a row is then one indexed bit test, with no hashing and no scan of the list. Signed integers and
Float16map their native bit patterns directly into the same finite bitmap domain.Direct comparisons for very small primitive lists
For a tiny fixed-width list, a hash lookup can cost more than comparing the input directly with every constant. #23014 stores the constants in a fixed-size array and combines all equality checks into a predictable comparison chain.
The maximum non-null list sizes are:
Larger one- and two-byte lists use the bitmap strategy. Larger native 32- and 64-bit integer lists use primitive hash sets; #24283 extends that same shared fallback to
Decimal128. Other unsupported primitive types continue through their existing exact filter or the generic fallback.Shared primitive selection and
Decimal128#24283 puts the direct-comparison thresholds and larger-list choices in one primitive selector. Native arrays and representation adapters call that selector, so there is no second dispatch table.
For native
Decimal128, up to four non-null list values use direct comparisons. Larger lists store and probe the native unscaledi128values in the shared primitive hash-set filter instead of using the generic Arrow array filter. Only membership storage changes; decimal expression typing, precision/scale compatibility, null behavior, and exact native-value equality remain unchanged.FixedSizeBinary#24102 recognizes
FixedSizeBinarywidths that exactly match an existing primitive representation. The list and input are converted in the same way, so primitive equality and hashing are exact equality over the original fixed-width bytes; no numeric or decimal operations are involved.FixedSizeBinarywidthUInt8UInt16UInt32UInt64Other widths keep using the generic filter.
Aligned Arrow buffers are reused directly. If a valid array has an unaligned buffer, the adapter copies its values into aligned primitive storage first. Alignment affects whether a copy is needed, not correctness.
Inline
Utf8ViewandBinaryViewArrow stores a
Utf8VieworBinaryViewvalue of at most 12 bytes completely inside its 128-bit view: the view contains the length and the zero-padded bytes. For these values, the view holds the full value, so view equality is exact and needs no backing-buffer read.#24088 uses this representation only when every non-null value in the constant list is inline:
Decimal128hash-set filter over the same 128-bit key; andInput arrays may still contain long values. Their encoded length distinguishes them from every inline list key, so they produce a miss without reading their backing bytes.
Utf8ViewandBinaryViewremain separate typed paths, and dictionary inputs continue through the shared dictionary handling.Expected impact
Decimal128listsFixedSizeBinarywidthsUtf8View/BinaryViewlists