[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 ]

Hi jim.wang@disney.com,

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 SERVER-3071.

Q: Will v2.6 support query + sort intersection?
A: Yes. One caveat is that the query must contain predicates over both indices. Consider a collection with indices on {zipcode:1} and {last_name:1}. We will not perform index intersection for db.phonebook.find({zipcode: "12345"}).sort({last_name: 1}). On the other hand, we will consider an index intersection plan for db.phonebook.find({zipcode: "12345", last_name: /^S/}).sort({last_name: 1}).

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:

db.phonebook.find({zipcode: 12345, last_name: {$type: 2}}).sort({last_name: 1});

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,
Dave

Generated at Thu Feb 08 03:32:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.