MongoDB possibly scanning documents for an operation that could be covered by an index

Question

I have a collection with a locked field in each document. I have the following index:

{
    locked : 1
}

when I perform this explain over a count operation

db.scheduled.find({locked: false}).explain({executionStats:1})
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "connectivity_recruiter.scheduled",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "locked" : {
                                "$eq" : false
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "locked" : 1
                                },
                                "indexName" : "locked_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "locked" : [
                                                "[false, false]"
                                        ]
                                }
                        }
                },

        .....

        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 53045,
                "executionTimeMillis" : 299,
                "totalKeysExamined" : 53045,
                "totalDocsExamined" : 53045,
                "executionStages" : {
                        "stage" : "FETCH",
                        "nReturned" : 53045,
                        "executionTimeMillisEstimate" : 180,
                        "works" : 53046,
                        "advanced" : 53045,
                        "needTime" : 0,
                        "needFetch" : 0,
                        "saveState" : 417,
                        "restoreState" : 417,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 53045,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 53045,
                                "executionTimeMillisEstimate" : 70,
                                "works" : 53046,
                                "advanced" : 53045,
                                "needTime" : 0,
                                "needFetch" : 0,
                                "saveState" : 417,
                                "restoreState" : 417,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "locked" : 1
                                },
                                "indexName" : "locked_1",
                                "isMultiKey" : false,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "locked" : [
                                                "[false, false]"
                                        ]
                                },
                                "keysExamined" : 53045,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0
                        }
                },
        ...........
}

totalDocsExamined seems indicate that all documents are being scanned in order to count them, while this operation could be performed by using the index alone. What is happening? Is this normal? Is a full scan of the collection going on?

Thanks

score 0 · Answer 1 · answered Jul 01 '16 at 18:06

All the returned docs were examined, the index was used only to filter not to retrieve the documents.

If you look at your explain, you'll notice that the number of docs is equals the number of documents examined.

Why that? Your index only contains one field while you're fetching the entire document, what mongodb does is to query the index for the keys and then go for the collection to fetch the document.

The only situation were no document will need to be examined is for covered queries, when the index contains all the projected fields.

See more at this link: https://docs.mongodb.com/manual/core/query-optimization/#covered-query

MongoDB possibly scanning documents for an operation that could be covered by an index

1 Answers1