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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: 2.6.5
    • Fix Version/s: 2.8.0-rc2
    • Component/s: Index Maintenance
    • Labels:
      None
    • Operating System:
      ALL
    • Steps To Reproduce:
      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

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

              Dates

              Created:
              Updated:
              Resolved: