apache / apache/druid

SQL planning slow with huge IN filters

Open
#7,904 4 comments 1 reaction 0 assignees View on GitHub
Area - SQL Performance
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.