-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: 3.6.0
-
Component/s: Aggregation Framework
-
Query Optimization
-
(copied to CRM)
Similarly to SERVER-31760 it's possible to rewrite $expr with $in expression to an indexable MatchExpression.
Semantically
{$expr:{$in:[ "value", [ "v1", "v2", "v3" ]] } }
is exactly equivalent to
{"value":{$in:[ "v1", "v2", "v3"]}}
if value is not an array.
If we restrict using an index for $expr $in expressions to non-multikey indexes/paths, then we will only get back matches from the index that are guaranteed to be correct semantically to the aggregation meaning of $in.
This is also exactly equivalent query that can use any index and return only agg semantics:
db.reviews.find({product_id:{$in:["hat","shirt"],$not:{$elemMatch:{$exists:true}}}})
The second part prevents reaching into arrays for comparison.
- is duplicated by
-
SERVER-36254 No index support for filtering on array fields inside $expr operator
- Closed
-
SERVER-36994 $expr does not use index for $in
- Closed
-
SERVER-38138 $expr doesn't use indexes
- Closed
- related to
-
SERVER-37470 Lookup sub-pipeline is not using index with the $in operator
- Backlog
-
SERVER-45326 Poor Aggregation Framework performance in relational queries
- Closed