mongodb query not utilising index with $expr

Question

I have an index on the createdAt date field.

I have an aggregation with this $match stage as the first stage:

{   
    "$expr" : {
        "$gte" : [
            "$createdAt",
            {
                "$dateSubtract" : {
                    "startDate" : "$$NOW",
                    "unit" : "week",
                    "amount" : 2
                }
            }
        ]
    }
}

This query does not use an index when I look at the query plan. If I hardcode a date into the query it uses the index (e.g. "createdAt": { $gte: ISODate("2023-12-25") }).

If I give an index hint, it correctly uses the index. Am I doing something wrong? Is the query planner selecting a colscan because at this point in time it is quicker than using an index? I am concerned that when i release this query to production, performance will be drop as it wont use an index.

Can someone explain why the index is not being used unless i specifically give an index hint?

Wernfried Domscheit · Accepted Answer · 2024-01-03 08:08:57Z

0

The documentation is not 100% clear, it only states:

The $eq, $lt, $lte, $gt, and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage.

I would assume, if the index is used within a $lookup stage, then MongoDB should be also able to use an index in $match stage.

I think it is better to use the index hint.

answered Jan 3, 2024 at 8:08

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mongodb query not utilising index with $expr

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related