[SERVER-34331] _id query not covered by index if $hint not specified Created: 05/Apr/18  Updated: 09/May/18  Resolved: 30/Apr/18

Status: Closed
Project: Core Server
Component/s: Performance, Querying
Affects Version/s: 3.6.3
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: Richard Smith Assignee: Asya Kamsky
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

I'm using mongodb 3.6.3 and my queries are fully covered by the _id index, e.g.

db.getCollection('inventory').find({ _id: "myid123"}, { _id: 1 })

Checking executionStats, totalDocsExamined is 1.

According to the executionStats, there is an IDHACK stage. If I manually specify the index to use

db.getCollection('inventory').find({ _id: "foobar"}, { _id: 1 }).hint( { _id: 1 } ).explain('executionStats')

I get an IDXSCAN stage instead and totalDocsExamined is 0. Is this desired behavior?



 Comments   
Comment by Asya Kamsky [ 28/Apr/18 ]

The IDHACK stage is a special stage optimized for equality point queries on _id, and it utilizes the _id index. IDHACK always calls WorkingSetcommon::fetch(), so it will always examine one document as we generally don't expect a query on exact _id value that only returns the _id (that means it would return the exact same value you already passed to it).

I'm closing this ticket as we don't plan to change this behavior.

If you need a workaround for this that uses a covered index scan you can use count command which will return 1 or 0 depending on whether the _id value was found and will only scan the index.

In addition, since the optimization is only for equality it does not apply when $in expression is used, so if you change your query to

db.getCollection('inventory').find({ _id: {$in:[ "myid123"]}}, { _id: 1 })

You will see

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

Comment by Asya Kamsky [ 08/Apr/18 ]

rmsmith can you provide your use case - it seems like it's unusual to fetch exactly just the single _id value you are passing in.

Comment by Richard Smith [ 05/Apr/18 ]

Sorry, somehow the formatting got messed up. The queries are:

db.getCollection('inventory').find({ _id: "myid123"}, { _id: 1 })

db.getCollection('inventory').find({ _id: "foobar"}, { _id: 1 }).hint( { _id: 1 } ).explain('executionStats')

Generated at Thu Feb 08 04:36:19 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.