Mongodb covered query executionStats

Question

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.

• Mani: When _id field is used in the filter condition, the query plan uses a special stage called "stage" : "IDHACK". Few reference groups.google.com/forum/#!topic/mongodb-user/RZgz9P2wD3Y dba.stackexchange.com/questions/174345/… jira.mongodb.org/browse/SERVER-16891
• smk: Ok but that doesnt explain why 1 doc needed to be examined
• Kay: If your query is executed against a sharded cluster (mongos) and _id is not your shardkey, your query would only be only covered when you add also the shardkey to your index.

Answer 1

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).