Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-15886

Caching of plan on rooted $or plan tie can lead to poor index choice

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.8.0-rc2
    • Affects Version/s: 2.6.5
    • Component/s: Index Maintenance
    • None
    • ALL
    • Hide 
      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
      Show
      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

      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.

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            james.wahlin@mongodb.com James Wahlin
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: