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?

Answer 1

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.