[SERVER-22393] Index not used for query on capped collection using reverse $natural sort order Created: 01/Feb/16 Updated: 03/Feb/16 Resolved: 01/Feb/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.4, 3.2.1 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Alfie Kirkpatrick | Assignee: | Kelsey Schubert |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | Setup and query
Version 2.6.4 result
Version 3.2.1 result
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: |
| Description |
|
If sort({$natural: -1}) is specified on a capped collection query which would normally use an index scan, the query switches to a colscan. For a large collection this causes performance issues. For our use case it's important to be able to query a capped collection using an index, limit the result count and iterate backwards based on insertion order, latest first. Tested with 2.6.4 and 3.2.1 (WiredTiger storage engine). I didn't test with a non-capped collection. |
| Comments |
| Comment by Kelsey Schubert [ 03/Feb/16 ] |
|
Hi jugglingcats, Using ObjectId as _id to sort the documents that are returned is the best approach to achieve what you are trying to do. In capped collections, the insertion order is the guaranteed to be the the $natural order. Therefore, sorting by ObjectId on the _id index will always give you the expected results. If you are not using ObjectId as _id, the workaround would be to sort using on an insertion timestamp field rather than $natural. Regards, |
| Comment by Alfie Kirkpatrick [ 01/Feb/16 ] |
|
Thanks for taking the time to respond with doc link and apologies for raising a false bug report. It's a shame that this isn't supported. We worked around it by sorting on _id which is an ObjectId. I realise this may not be accurate in all cases, but it's good enough for us. Is there an enhancement request open for this feature (that I could follow)? |
| Comment by Kelsey Schubert [ 01/Feb/16 ] |
|
Hi Alfie, Thanks for the report. Sorting by natural order forces a collection scan and will not use an index. This is expected behavior and is described in our documentation here. Kind regards, |