0

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.

  • Once the initial build completes for an index, there isn't an extra step to make a new index active. Can you edit your question to include the output of db.product.getIndexes()? Field names are case-sensitive, so perhaps the index wasn't created on the expected field. – Stennie Aug 3 at 7:55
  • Wow this is really strange...the index I created is not there. I can see it in the " indexes "list, but I can't see it using the comand you told me to use. I will try to create it again and will post the results. – Racer SQL Aug 5 at 11:32
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.