Question 241624 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Mongo: Why totalDocExamined is greater than zero

Refer to this document

explain.executionStats.totalKeysExamined
Number of index entries scanned.
totalKeysExamined
corresponds to the nscanned field returned by cursor.explain() in earlier versions of MongoDB.

explain.executionStats.totalDocsExamined
Number of documents examined during query execution. Common query execution stages that examine documents are COLLSCAN and FETCH.

I understand that totalKeysExamined is number of index scanned. totalDocsExamined is number of document get from regular collection to examine.

My question:

I have a collection Users:

{ name: String, age: Number }

I create an index for Users collection: name_1: { name: 1 }

My query: db.Users.find({ name: 'John' })

And I tried to explain the query, db.Users.find({ name: 'John' }).explain('executionStats')

and result of explain query:

{
    "explainVersion" : "1",
    "queryPlanner" : {
        "namespace" : "db.users",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "name" : {
                "$eq" : "John"
            }
        },
        "maxIndexedOrSolutionsReached" : false,
        "maxIndexedAndSolutionsReached" : false,
        "maxScansToExplodeReached" : false,
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "name" : 1.0
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [ 
                        "["John", "John"]"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 1,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 1,
            "executionTimeMillisEstimate" : 0,
            "works" : 2,
            "advanced" : 1,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "docsExamined" : 1,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1,
                "executionTimeMillisEstimate" : 0,
                "works" : 2,
                "advanced" : 1,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "keyPattern" : {
                    "name" : 1.0
                },
                "indexName" : "name_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "name" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "name" : [ 
                        "["John", "John"]"
                    ]
                },
                "keysExamined" : 1,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0
            }
        }
    },
}

I saw:

  "totalKeysExamined" : 1,
  "totalDocsExamined" : 1,

And the query is very simple, just name and it match 100% with the index.
SO, WHY totalDocsExamined still not be ZERO, WHY MONGO STILL NEED TO GET 1 DOCUMENT FROM REGULAR COLLECTION TO CHECK

Tags:

2

Answers


  1. 0

    When you create an index on a field, like in your case with the "name" field, the index stores the value of the indexed field along with a reference (_id) to the document. So, while the query uses the index to quickly find the matching value ("John" in your case) it still needs to retrieve the actual document from the collection using the reference in the index to fully satisfy the query.

    In the explain output you provided, you can see that the "winningPlan" indicates that an "IXSCAN" stage is used, which is an index scan. The query planner estimates that it will need to scan or examine 1 key (index entry) and 1 document. The "keysExamined" value is 1, which indicates that the index was used as expected. However, since it needs to retrieve the actual document, "docsExamined" is also 1.

    This behaviour ensures that the query result is accurate and up-to-date, even if the indexed field may have changed since the index was last updated. So even though the query is simple and matches the index 100%, MongoDB still needs to fetch the corresponding document to ensure that the query result is valid and consistent.

    Login or Signup to reply.
  2. 0

    To use only the data from the name index and see totalDocsExamined: 0 , you can try this:

    db.Users.find({ name: 'John' },{ name: 1 , _id: 0 }).explain('executionStats')

    Explained:

    The query in the above format is the so called "covered query" that is using only the indexed data already in memory and not fetching the full document of the found name via index. Please, note that the _id of the document need to be explicitly suppressed since by default it will be automatically fetched that will require access to the document.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search