Mongo DB Collection Scan OR Index Scan

user_2224750 0
Viewed 185

I have an index on "timeofcollection". There is an issue that one query using same field shown collection is being scanned while on shown index scan. These are the "$match" steps in an aggregation pipeline I am posting below. Can someone help me out explaining what is an issue and how should I handle it?

If I have following in $match step in pipeline, it evaluates as an index scan

{
   "timeofcollection":{$gte:ISODate("2020-09-24T00:00:00.000+0000"),$lt:ISODate('2020-09-25T00:00:00.000+0000')}
}

If I have following step in pipeline, it evaluates as collection scan

{

        $match: {
                "$expr": {
                    "$and": [{
                            "$gte": [
                                "$_id.dt",
                                {
                                    "$subtract": [{
                                            "$toDate": {
                                                "$dateToString": {
                                                    "date": "$$NOW",
                                                    "format": "%Y-%m-%dT00:00:00.000+0000"
                                                }
                                            }
                                        },
                                        86400000
                                    ]
                                }
                            ],
                        },
                        {
                            "$lt": [
                                "$_id.dt",
                                {
                                    "$toDate": {
                                        "$dateToString": {
                                            "date": "$$NOW",
                                            "format": "%Y-%m-%dT00:00:00.000+0000"
                                        }
                                    }
                                }
                            ]
                        }
                    ]
                }
            }
        }

Basically what I am trying to achieve is to pull records falling in last day. This works fine but involves collection scan which I can not do.

Any help?

mongodb aggregation match
2 Answers

The query planner will only use an index for equality comparison when using the $expr operator.

It will also only use the index when the values of the expressions are constant for the query. Since the $$NOW variable is not bound until query execution begins, and will have a different value for every execution, the query planner will not use an index for a query using that variable.

user_22826340

This may not be a complete answer, but one obvious problem I see with your above aggregation is that, for some reason, you seem to be converting dates to text, only to convert them back to dates again. Typically, if your filter were to contain a function of timeofcollection, then the index on timeofcollection might not be usable. Try this version:

$match: {
            "$expr": {
                "$and": [
                    {
                        "$gte": [
                            "$_id.dt",
                            {
                                "$subtract": [ "$$NOW", 86400000 ]
                            }
                        ],
                    },
                    {
                        "$lt": [
                            "$_id.dt", "$$NOW",
                        ]
                    }
                ]
            }
        }

Note that I am assuming here that dt in the above fragment is an alias for timeofcollection, defined somewhere earlier.

The key point here is that using timeofcollection inside a function might render your index unusable. The above version may get around this problem.

user_18632290
Related