[SERVER-13728] Index Intersection and Sort Created: 24/Apr/14 Updated: 10/Dec/14 Resolved: 28/Apr/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.0 |
| Fix Version/s: | None |
| Type: | Question | Priority: | Minor - P4 |
| Reporter: | Jim Wang | Assignee: | David Storch |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Participants: |
| Description |
|
For 2.6, index intersection does not apply when the sort( ) operation requires an index completely separate from the query predicate. Do you have any plan to support index intersection for both query and sort in the future. Currently our query criteria are different from sort field, we have to create lots of compound indexes, which uses lots of memory. It would be nice if we only need to create single field indexes and let Mongo to choose them for both query and sort. It is ok to use hint to tell Mongo which index for query and which index for sort. |
| Comments |
| Comment by David Storch [ 28/Apr/14 ] | |
Does MongoDB 2.6 support query + sort index intersection?Yes, but with the limitation that query+sort index intersection requires predicates over both indices. I will reuse the example from my comment on
As a workaround, you can provide a "dummy" predicate for the sort field. For instance, the query optimizer will consider index intersection plans for the following query:
We use the $type predicate over 'last_name' because it will match all documents with a string for 'last_name', and it will cause the optimizer to consider a query+sort intersection plan. Will this limitation be relaxed in the future?Hopefully yes, but I can't promise anything as this work has not yet been scheduled. Can you hint to use a query + sort index intersection plan?Currently there is no way to hint an index intersection plan. I hope this is helpful information. Please let us know if you have any further questions. I'm going to close this ticket, but feel free to re-open if you have further related questions or concerns. Thanks, |