[SERVER-15886] Caching of plan on rooted $or plan tie can lead to poor index choice Created: 30/Oct/14  Updated: 11/Jul/16  Resolved: 02/Dec/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.6.5
Fix Version/s: 2.8.0-rc2

Type: Bug Priority: Major - P3
Reporter: James Wahlin Assignee: David Storch
Resolution: Done Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Related
Tested
Operating System: ALL
Steps To Reproduce:

db.test2.drop()
var count=0;
var a=0, b=0, c=0;
for (; count < 10000; ++count){ a += 1; b +=1; c += 1; db.test2.save({"a": a%1000, "b": b%1000, "c": c}); }
db.test2.ensureIndex({a:1, _id:1})
db.test2.ensureIndex({a:1, b:1, c:1})
db.test2.getPlanCache().clear()
 
// Query that matches no document and puts bad plan in cache ({a:1, _id: 1})
db.test2.find({$or: [{a: 999999999, b: 500, c: 500},{a: 999999999, b: 999, c: 999}]})
 
// Query match using cached {a:1, _id: 1} index, resulting in poor performance. A choice of {a:1, b:1, c:1} would have provided much better performance.
db.test2.find({$or: [{a: 999, b: 999, c: 999},{a: 999, b: 999, c: 999}]})
db.test2.getPlanCache().getPlansByQuery({$or: [{a: 999, b: 999, c: 999},{a: 999, b: 999, c: 999}]})[0].details.solution
 
db.test2.getPlanCache().clear()
// The same query run after clearing the query cache chooses {a:1, b:1, c:1} demonstrating efficient execution
db.test2.find({$or: [{a: 999, b: 999, c: 999},{a: 999, b: 999, c: 999}]})
db.test2.getPlanCache().getPlansByQuery({$or: [{a: 999, b: 999, c: 999},{a: 999, b: 999, c: 999}]})[0].details.solution

Participants:

 Description   

In the following reproduction we have:

  • A rooted $or query that matches no documents
  • 2 indexes that are equally efficient at determining there are no matches

When the query is run, the 2 indexes tie and the first index created is chosen as winner and cached.

In a subsequent query which has 1 match, the cached plan in used with no evaluation. This results in the scanning of 20 index entries where the alternate index can perform the same scanning only 2.

The query planner should allow for correct index choice in this scenario.



 Comments   
Comment by Githook User [ 02/Dec/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-15886 SubplanStage tries to plan each branch from the cache

Fixes a bug in which the SubplanStage could put a bad plan into the plan cache.
Branch: master
https://github.com/mongodb/mongo/commit/a4bb064660fe9d6db93085f7be9564f90afa9b0c

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