MongoDB | 2652630

Info

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

Top User Comments

Steps to Reproduce

Collection with an field "last_processed_time" which is a regulare datetime. Create a standard index on this field. { "v" : 2.0, "key" : { "last_processed_time" : 1.0 }, "name" : "ix_last_processed_time", "background" : true } e this as $match stage in an aggregation q uery: { "$expr" : { "$gt" : [ "$last_processed_time", { "$dateSubtract" : { "startDate" : "$$NOW", "unit" : "day", "amount" : 21.0 } } ] } } Tested on: Atlas hosted MongoDB 7.0.8 (dedicated M30)