[SERVER-13211] Optimal index not chosen for query plan when many indexes match same prefix Created: 14/Mar/14  Updated: 11/Sep/23  Resolved: 11/Sep/23

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.0-rc1
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: J Rassi Assignee: Backlog - Query Optimization
Resolution: Duplicate Votes: 12
Labels: 26qa, bonsai, query-44-grooming
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-80233 Implement index prefix heuristic Closed
Related
related to SERVER-24312 Mongo Query Taking Lot of Time Select... Closed
is related to SERVER-16350 Wrong index choice when request conta... Closed
is related to SERVER-20616 Plan ranker sampling from the beginni... Backlog
is related to SERVER-21697 Plan ranking should take query and in... Closed
is related to SERVER-40844 Better tie breaking of "perfect" indexes Closed
Assigned Teams:
Query Optimization
Operating System: ALL
Participants:
Case:

 Description   

The plan enumerator outputs 6 plans for the below query (one for each of the first 6 six indexes created), none of which use the 7th / most optimal index {a: 1, h: 1}.

db.foo.ensureIndex({a: 1, b: 1});
db.foo.ensureIndex({a: 1, c: 1});
db.foo.ensureIndex({a: 1, d: 1});
db.foo.ensureIndex({a: 1, e: 1});
db.foo.ensureIndex({a: 1, f: 1});
db.foo.ensureIndex({a: 1, g: 1});
db.foo.ensureIndex({a: 1, h: 1});
 
for (var i = 0; i < 10000; i++) {
    db.foo.insert({a: 1, h: i});
}
 
// Below assertion fails with "Error: [1] != [10000] are not equal"
assert.eq(1, db.foo.find({a: 1, h: 1}).explain().nscanned);



 Comments   
Comment by Alexander Ignatyev [ 11/Sep/23 ]

The fix was implemented in PM-3316 via SERVER-80233.

Comment by Simon Schäfer [ 07/Oct/21 ]

This seems like a major bug. Any updates?

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