MongoDB is not using the index I created for this specific query

indexmongodb

MongoDB 4.0.

I have a simple query: 

db.product.find({
        modifiedOn: {
            $gte: ISODate("2019-08-02T03:00:00.000Z")
        }
    })
    .limit(10)

And a simple index on modifiedOn.

For some reason, the query is taking a long time to run against 40 million docs.

Explain says that the query is using a collection scan (COLLSCAN) instead of an index:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "dbname",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "modifiedOn" : {
                "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
            }
        },
        "winningPlan" : {
            "stage" : "LIMIT",
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "modifiedOn" : {
                        "$gte" : ISODate("2019-08-02T00:00:00.000-03:00")
                    }
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "serverhost",
        "port" : 27017,
        "version" : "4.0.11",
        "gitVersion" : "417d1a712e9f040d54beca8e4943edce218e9a8c"
    },
    "ok" : 1,
    "operationTime" : Timestamp(1564756397, 18),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1564756397, 18),
        "signature" : {
            "hash" : BinData(0,"2D2uJyey5qPMYEk17CekUzpdqpU="),
            "keyId" : 6662358951796932610
        }
    }
}

This index took some hours to be created. Is there something I don't know with MongoDB that I need to do after creating an index to make it active?

Using explain("executionStats") I can see NO INDEX AVAILABLE FOR THIS QUERY.

Best Answer

Try using $hint in your query to enforce your optimizer to use this specific index for the query and post the output.

Thank you!

-Anban

Related Question