[SERVER-10728] Query optimizer should consider the projection when selecting a plan Created: 10/Sep/13  Updated: 10/Dec/14  Resolved: 11/Sep/13

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

Type: Improvement Priority: Major - P3
Reporter: John Esmet Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-2109 Query optimizer should pick plans tha... Closed
Participants:

 Description   

Given indexes:

{ a: 1, b: 1 }

and

{ a: 1, c: 1 }

, a range query on 'a' with a projection on

{ a: 1, c: 1, _id: 0 }

will choose the non-covering index

{ a: 1, b: 1 }

when it should choose the covering index

{ a: 1, c: 1 }

.

This happens because the optimizer doesn't consider the projection when selecting a plan, so it chooses the first plan that seems optimal (which is a: 1, b: 1 because that index appears first in the list of indexes for the namespace).

To reproduce:
db.a.ensureIndex(

{ a: 1, b: 1 }

)
db.a.ensureIndex(

{ a: 1, c: 1 }

)
db.a.insert(

{ a: 10, b: 10, c: 10 }

)
db.a.find({ a:

{ $gte: 10, $lte: 12 }

},

{ a: 1, c: 1, _id: 0 }

)

The current work-around is to add a hint for the proper covering index. It would be neat if the correct index was chosen without a hint, though.



 Comments   
Comment by Daniel Pasette (Inactive) [ 11/Sep/13 ]

Thanks for the clear report. I think this is duplicated by SERVER-2109.

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