[SERVER-22182] Compound index not used for sort if sort spec doesn't match index spec, even with equality predicate on sort prefix Created: 14/Jan/16  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Jon Rangel (Inactive) Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 2
Labels: query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

The query planner elects to use a blocking sort for queries where the sort spec doesn't match the index spec, even in the case where there is an equality match predicate on some prefix of the sort spec. In this special case a sort is only truly required on the remaining suffix of the sort spec, for which the index could possibly be used. For example, with an index on {a:1,b:1}:

  1. db.foo.find({a:2}).sort({a:1,b:1}) - sort uses the index
  2. db.foo.find({a:2}).sort({a:-1,b:1}) - blocking sort
  3. db.foo.find({a:2}).sort({a:-1,b:-1}) - sort uses the index
  4. db.foo.find({a:2}).sort({a:1,b:-1}) - blocking sort

Logically, query 2 is the same as 1, and query 4 is the same as 3


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