[SERVER-13324] Sparse index is not used to filter {field: {$ne: null}} queries Created: 24/Mar/14 Updated: 10/Dec/14 Resolved: 24/Mar/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.0-rc2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Michael Henson | Assignee: | David Storch |
| Resolution: | Done | Votes: | 0 |
| Labels: | index, sparse | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
In previous version of mongo a sparse index on a field could not be used to find those documents that only had the indexed field. Take the following collection:
If one were to naively write a query to find all documents that had the "bar" property one would start with:
In mongo 2.4 and below this particular query would result in a full collection scan with a BasicCursor. For an application in which "bar" would never be null this particular query could be rewritten as:
This query would use the sparse index. It appears that while mongo 2.5 can now correctly use the sparse index for the $exists: true query it will fail to use any index for the {$ne: null} variety unless an explicit hint is given. |
| Comments |
| Comment by David Storch [ 24/Mar/14 ] | |||||
|
To be documented as part of | |||||
| Comment by David Storch [ 24/Mar/14 ] | |||||
|
Good point, this should be documented in the release notes. I'm going to get the ball rolling with our documentation team. | |||||
| Comment by Michael Henson [ 24/Mar/14 ] | |||||
|
Thanks for the detailed explanation Dave. I agree that the fix makes sense. Do you think it's worth specifically calling out this change in the release notes? I wager a number of other people might have addressed the lack of an index optimized {$exists: true} with a similar hack and might find themselves in a tricky situation if they were update to the newest release without doing much testing. | |||||
| Comment by David Storch [ 24/Mar/14 ] | |||||
|
Hi Michael, Thanks for the detailed bug report and repro steps. Although the 2.6 behavior indeed differs from 2.4, this was an intentional change, so I'm going to close this ticket as "Works as Designed". Read on for an explanation of why 2.6 works as it does. The simple answer is that negations such as $not and $ne will never use a sparse index. Consider the code fragment below:
What should the query in the last line above return? 2.4's answer for this question was "the document with _id: 2 if there is no index, but nothing if there is an index on 'foo'". It is a bad idea for the results of a query to differ depending on whether or not an index is available. Therefore, in 2.6 the query above will always return the doc with _id: 2, regardless of whether or not there is an index. So, how does this relate to whether or not the index is sparse? For a sparse index, there will be no index key for a document that does not contain the indexed field. This means that there is no way to use a sparse index to retrieve documents missing the indexed field. It follows that negations (which require retrieval of docs which don't have the negated field) cannot be answered using a scan over a sparse index. The story is different for $exists: true. The query {foo: {$exists: true}} asks for all documents which contain a value (null or non-null) for field 'foo'. If there is a sparse index over 'foo', then all documents which have a value for 'foo' will have keys in the index. Conversely, all documents which are missing a value for 'foo' will be absent from the index. As such, this query can be answered by a full index scan from MinKey to MaxKey over the sparse index. I hope that this explanation makes sense. Please let us know if you have any further bug reports or questions. Thanks, |