Question(optimizer): why InList be rewritten as a series of OR expressions?

[KarinaMilet]

In sql/planner/DomainTranslator.java L876 - L880, we will expand the elements in InList into an expression composed of multiple OR expressions linked together to form the predicate. Some optimizer rules such as PushPredicateIntoTableScan will call it.

However, I personally consider this to be an unnecessary rewrite, and when InList contains a large number of elements, it can significantly slow down the optimizer's processing time (as other optimization rules also call it).

In practical testing, when an InList containing 10,000 values was present, the optimizer time collected via logs was approximately 3.5 seconds and rewritten into OR expressions costs 2.6 seconds.

In Datafusion, it will combine multi OR expressions into single InList.

So I wonder why such a rewritten rule exists here? Or is this a potential point for optimization?

References

1. core/sql/planner/DomainTranslator.java
2. PushPredicateIntoTableScan rule
3. Datafusion expr_simplifier.rs

[wendigo]

cc @raunaqmorarka @kasiafi

[KarinaMilet]

I examined the implementation of DuckDB. For InList that can be treated as constants, DuckDB will expand them when the number of elements in the InList is less than five. However, when the number of elements becomes very large, they are rewritten as mark join. Trino, however, does not appear to support mark join.

DuckDB in_clause_rewriter

[KarinaMilet]

Oh! I understand it, we can close this discussion.