Mongodb covered query executionStats

mongodb

Hello i'd like some help understanding the following execution stats.

For this query

db.family.find({ "_id" : ObjectId("5c87890b3adcde7da566f0d7")},{_id:1}).explain("executionStats")

        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,

Specifically not sure why totalDocsExamined is 1 because I would have imagined that sine there is an index on ObjectId, and only that is returned , no documents need to be examined?

Kindly help.

Best Answer

Your covered query example is hitting a special case optimisation: an equality query on the _id index.

Since the _id index is always both required and unique, equality queries on _id choose this index and bypass some of the usual stages of query planning and execution. This is a fast path for common queries looking up documents by their primary key.

When this optimisation happens your explain output will include an IDHACK input stage and the detailed executionStatswill not show any rejectedPlans (since the query planner is bypassed for this path). The IDHACK stage always examines the document so won't be a covered query (as at MongoDB 4.2).

The IDHACK optimisation is specific to equality queries, so if you want a workaround for a covered query on _id you could use a different expression like $in:

db.family.find({ "_id" : { $in: [ObjectId("5c87890b3adcde7da566f0d7")]}},{_id:1}).
     explain("executionStats")

A $in query goes through the normal query planning path so other candidate plans will be considered and the explain results will include an IXSCAN stage instead of IDHACK. This query can also be covered (only index keys examined).

Related Question