SQL planning slow with huge IN filters
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Affected Version
0.14.2
### Description
When running a SQL query with an IN clause with ~14k elements, planning on the broker took quite a long time (over a minute). A lot of time seems to be spent in areas of Calcite code that look like the following (I saw this pattern repeat over lots of thread dumps). Maybe some algorithmic blowup when there are a lot of OR conditions, which is what a big IN would get translated to.
```
"sql[af077a4e-23e8-4440-9f12-42919d7b8b5b]" #113 daemon prio=5 os_prio=0 tid=0x00007f3142179000 nid=0x36c runnable [0x00007f3096fb3000]
java.lang.Thread.State: RUNNABLE
at org.apache.calcite.rex.RexUtil.decompose(RexUtil.java:355)
at org.apache.calcite.rex.RexUtil.gatherConstraints(RexUtil.java:366)
at org.apache.calcite.rex.RexUtil.predicateConstants(RexUtil.java:323)
at org.apache.calcite.plan.RelOptPredicateList.of(RelOptPredicateList.java:145)
at org.apache.calcite.plan.RelOptPredicateList.union(RelOptPredicateList.java:158)
at org.apache.calcite.rex.RexSimplify.simplifyOrTerms(RexSimplify.java:350)
at org.apache.calcite.rex.RexSimplify.simplifyOr(RexSimplify.java:1058)
at org.apache.calcite.rex.RexSimplify.simplify_(RexSimplify.java:183)
at org.apache.calcite.rex.RexSimplify.lambda$simplify$0(RexSimplify.java:175)
at org.apache.calcite.rex.RexSimplify$$Lambda$258/1039224919.apply(Unknown Source)
at org.apache.calcite.rex.RexSimplify.verify(RexSimplify.java:1097)
at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:175)
at org.apache.calcite.rex.RexUtil$ExprSimplifier.visitCall(RexUtil.java:2607)
at org.apache.calcite.rex.RexUtil$ExprSimplifier.visitCall(RexUtil.java:2567)
at org.apache.calcite.rex.RexCall.accept(RexCall.java:107)
at org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:151)
at org.apache.calcite.rex.RexShuttle.visitCall(RexShuttle.java:100)
at org.apache.calcite.rex.RexUtil$ExprSimplifier.visitCall(RexUtil.java:2604)
at org.apache.calcite.rex.RexUtil$ExprSimplifier.visitCall(RexUtil.java:2567)
```
Contributor guide
Research direction
Start by reproducing the reported SQL query with roughly 14k IN-clause elements on version 0.14.2 and capture planning time or thread dumps. Read the referenced Calcite paths in RexUtil, RelOptPredicateList, and RexSimplify to trace the repeated simplification work. Done means the excessive planning delay is addressed and the large-filter case is verified.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 30/100