[SERVER-25221] Sort with limit/skip hits memory limit Created: 22/Jul/16 Updated: 30/Jul/16 Resolved: 27/Jul/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance, Querying |
| Affects Version/s: | 3.2.4 |
| Fix Version/s: | None |
| Type: | Question | Priority: | Major - P3 |
| Reporter: | Paul C [X] | Assignee: | Kelsey Schubert |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Participants: |
| Description |
|
We have a complex compound index that is used on a large(ish) data set to satisfy a query along with a sort. The compound follows the ESR (equality, sort, and ranged) ordering and does get used when queries are made. However... when sorting and then using limit/skip we hit the server memory limit for sorts which suggests the index is not used for the sort. So the question here is about if the index is being used for the sort correctly, and more specifically are we doing something wrong here or is there a server bug?
Using explain we see the following winning plan:
|
| Comments |
| Comment by Paul C [X] [ 27/Jul/16 ] | ||||||||||
|
Thanks for the reply, will direct further questions to the groups mentioned. | ||||||||||
| Comment by Kelsey Schubert [ 27/Jul/16 ] | ||||||||||
|
Hi Crispy1975, Thanks for the report - this is expected behavior. Consider a compound index {a:1,b:1}. The documents would be ordered in the index as
As you can see, if the query includes a range of a values then the returned documents will not be sorted by the b field. Consequently, a sort stage is required. For more information, please review our documentation on sorting results with indexes. To resolve this issue, I would recommend modifying your indexes. Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-users group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-users group. Kind regards, | ||||||||||
| Comment by Paul C [X] [ 27/Jul/16 ] | ||||||||||
|
Any thoughts on this, would love to think we have done something wrong, and find it's not a bug, and can get these sorts working. |