Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
None
-
Query Optimization
Description
Currently the MatchExpression $or->$in rewrite is only capable of collapsing a single field with multiple equality disjunctions into a $in. For example,
{ $or: [
|
{ a: 10 }, |
{ b: 11 }, |
{ c: 12 }, |
{ d: 13 }, |
{ a: 14 }, |
{ b: 15 }, |
{ c: 16 }, |
{ d: 17 }, |
{ a: 18 }, |
{ b: 19 } |
]
|
}
|
is optimized to
{
|
$or: [
|
{ b: 11 }, |
{ b: 15 }, |
{ b: 19 }, |
{ c: 12 }, |
{ c: 16 }, |
{ d: 13 }, |
{ d: 17 }, |
{ a: { $in : [ 10, 14, 18 ] } } |
]
|
}
|
This leads to an SBE plan with a large number of unnecessary comparisons. SBE is able to evaluate $in predicates more efficiently by using a hashset, and the interval builder is able to generate index bounds more easily on an $in list.
These expressions can be further reduce by allowing the $or->$in rewrite to handle multiple fields.
There are two high-value workloads (MatchExpressionWidePredicate and MatchExpressionWidePredicateWithDeepFieldpaths) which could be improved by this optimization.