[SERVER-7950] MongoDB not using the most optimal index when sorting on 2 keys Created: 15/Dec/12 Updated: 07/Mar/14 Resolved: 19/Dec/12 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance, Querying |
| Affects Version/s: | 2.2.2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Vincent Bernat | Assignee: | Aaron Staple |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Debian GNU/Linux unstable. |
||
| Attachments: |
|
||||||||||||
| Issue Links: |
|
||||||||||||
| Operating System: | ALL | ||||||||||||
| Steps To Reproduce: | 1. Execute the script attached to populate the database. You should get : no index used, scan and order used. |
||||||||||||
| Participants: | |||||||||||||
| Description |
|
With the following request:
And one of the following indexes:
MongoDB is never using the index, always using BasicCursor and a scan and order to get the appropriate result. The dataset is about 5 millions entries. The find() alone would return about 2 millions entries. The ns field is not very selective ("default" is 9 out of 10). Another oddity that may be related is the fact that despite not using an index, MongoDB is able to not scan the whole dataset to get a result (nscannedObjects may be equal to 2 millions instead of 5). Without index, MongoDB is able to answer the request in about 10s. By hinting the last specified index, I am able to get an answer in 3s and no scan and order. |
| Comments |
| Comment by Vincent Bernat [ 18/Dec/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Oh, I just had a look at | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Vincent Bernat [ 18/Dec/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi Aaron! Thanks for the explanation. What about the "scanAndOrder"? Shouldn't be able to use the index to avoid a sort? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Aaron Staple [ 17/Dec/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi Vincent, I think the main issue here is that you are running into In the case below, the indexed (ordered) cursor runs until it finds its first match, and then the query finishes. At that time, the unindexed cursor has found two matches (though it hasn't found all results or done any sorting).
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Vincent Bernat [ 15/Dec/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The correct way to populate the database. |