[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.
Checking executionStats, totalDocsExamined is 1. According to the executionStats, there is an IDHACK stage. If I manually specify the index to use
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
You will see
| |||
| 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:
|