[SERVER-9640] Distinct command does not use covering multi-key indexes. Created: 09/May/13 Updated: 21/Jul/15 Resolved: 29/Jan/15 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.4.2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Will Shaver | Assignee: | Unassigned |
| Resolution: | Duplicate | Votes: | 1 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Operating System: | ALL | ||||||||
| Participants: | |||||||||
| Description |
As expected with a non-nested distinct query the nscannedObjects is 0.
As you can see, the nscannedObjects is 2 for the second query, where it would be expected to be 1. Doing a find on this data uses the index as expected:
|
| Comments |
| Comment by David Storch [ 29/Jan/15 ] | |
|
Closing as a duplicate of | |
| Comment by Matthieu Rigal [ 12/Aug/14 ] | |
|
And even better, it is consistently taking the worst index ! Even with 2.6.4 : > db.runCommand({distinct: "journals", key: "_cls", query: {cust_id: 5001, _cls: {$gt: 'a'}}}) , > db.runCommand({distinct: "journals", key: "shift_uuid", query: {cust_id: 5001, shift_uuid: {$exists: true}}}) , | |
| Comment by Matthieu Rigal [ 11/Aug/14 ] | |
|
Here another failed example with 2.6.3: Indexes available : [ ..., > db.runCommand({distinct: "journals", key:"shift_uuid", query: {"cust_id": 5001}}) , Why doesn't the hint use the "cust_id_1_shift_uuid_1__cls_1_synced_at_1" index ??? | |
| Comment by Will Shaver [ 13/May/13 ] | |
|
Also fails to use the index for arrays of strings, such as the classic blog post/tags schema:
|