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

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • 2.8.0-rc2
    • 2.6.5
    • 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

    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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: