-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 7.0.8
-
Component/s: None
-
None
-
Query Optimization
-
ALL
-
-
(copied to CRM)
When using an $expr expression in a $match stage, the query engine does use COLLSCAN instead of an IXSCAN. I used this $match stage. There is an index on "last_processed_time:
{ "$expr" : { "$gt" : [ "$last_processed_time", { "$dateSubtract" : { "startDate" : "$$NOW", "unit" : "day", "amount" : 21.0 } } ] } }
Giving an Index hint does not help, it just adds a separete Filter stage to the winning plan.
{ "stage" : "FETCH", "filter" : { "$expr" : { "$gt" : [ "$last_processed_time", { "$dateSubtract" : { "startDate" : "$$NOW", "unit" : { "$const" : "day" }, "amount" : { "$const" : 21.0 } } } ] } } }
If no $expr stage is used, the query engine uses the existing index. Seems to happen since MongoDB 7.0. In MongoDB 6.0, an IXSCAN is used.
Also see https://www.mongodb.com/community/forums/t/expr-not-using-indices/276830
- duplicates
-
SERVER-83524 Enable indexed plans for expressions with $$NOW, $$CLUSTER_TIME, and $$USER_ROLES
- Closed