-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
QO 2023-05-15, QO 2023-05-29, QO 2023-06-12, QO 2023-06-26, QO 2023-07-10, QO 2023-07-24, QO 2023-08-07, QO 2023-08-21, QO 2023-09-04, QO 2023-09-18, QO 2023-10-02, QO 2023-10-16, QO 2023-10-30
If you have an elemMatch with a conjunction:
db.c.find({a: {$elemMatch: {x: 1, y: 1}}})
Then we could generate a plan that scans a multikey index on {a: 1}, and apply the {x: 1, y: 1} predicate to each index key. (And then do the "is array" check after fetching.)
Bonsai does not consider this plan, because we relax the predicate to two separate Traverses:
Filter ... Get [a] Traverse ComposeM (Get [x] ...) (Get [y] ...) -> Sargable Get [a] Traverse Get [x] ... perfOnly Get [a] Traverse Get [y] ... perfOnly
This means we consider plans such as:
- Full index scan, filter one predicate, fetch, filter on whole predicate
- Index intersection of {a: 1} with itself
- Collection scan
(This is still an improvement over classic, which only considers a collection scan. Even with hint({a: 1}), classic does not consider evaluating either predicate on the index side to reduce fetching.)
Maybe this would break down into sub-features:
- an exploration rewrite that turns Traverse predicates into Unwind
- a rewrite that matches Unwind with a multikey index