[SERVER-23530] Sort function does not use the compound index without equality matches on prefix keys Created: 05/Apr/16  Updated: 16/Nov/21  Resolved: 06/Apr/16

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 3.2.4
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Tom Grossman 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:
  1. create a collection with the above schema
  2. create compound index with all 3 fields
  3. query with find on the first field and sort on the 3rd field
  4. use explain()
Participants:

 Description   
Original summary

Sort function does not use the compound index

Original Description

This is the collection schema:

{
    a: 11,
    b: 22,
    c: 33
}

The index is:

{
    a: 1,
    b: 1,
    c: 1
}

If the query is:

db.test_coll.find({a: 11, b: 22}).sort({ c: 1}).explain()

I can see that there is no SORT stage which is find.
But, If I remove the b value from the query:

db.test_coll.find({a: 11}).sort({ c: 1}).explain()

The queryPlanner adds another SORT stage which is very bad for performance issues.

In general, I noticed that when mongo uses minKey and maxKey index bounds, it doesn't use the index for sorting.

If I'll use $in for b value with all the possible values, it will use the index correctly also for sorting.
But if it uses the minKey and maxKey values, it does't use the index for sorting.
I don't understand what is the difference between the two.
Looks like a bug.



 Comments   
Comment by Kelsey Schubert [ 05/Apr/16 ]

Hi tomgrossman,

The behavior that you describe is expected and is documented here. I will present a simple example with two fields to try and give you an idea of what is going on.

Please consider the following collection sorted on the compound index {a:1,b:1}:

db.foo.find().sort({a:1,b:1})
{ "_id" : ObjectId("5703cbf1d30acd083f35143e"), "a" : 1, "b" : 1 }
{ "_id" : ObjectId("5703cbf4d30acd083f35143f"), "a" : 1, "b" : 2 }
{ "_id" : ObjectId("5703cbf7d30acd083f351440"), "a" : 2, "b" : 1 }
{ "_id" : ObjectId("5703cbf9d30acd083f351441"), "a" : 2, "b" : 2 }

These documents are not sorted by b, and so sorting on b would not utilize this index.

However, if we add an equality match on a, we can see that the results will be sorted by b:

db.foo.find({a:1}).sort({a:1,b:1})
{ "_id" : ObjectId("5703cbf1d30acd083f35143e"), "a" : 1, "b" : 1 }
{ "_id" : ObjectId("5703cbf4d30acd083f35143f"), "a" : 1, "b" : 2 }

Consequently, the compound index {a:1,b:1} cannot be used to sort

db.foo.find({}).sort({b:1})

but can sort

 
db.foo.find({a:1}).sort({b:1})

I hope this clarifies this behavior.

Kind regards,
Thomas

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