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

Optimal index not chosen for query plan when many indexes match same prefix

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • 2.6.0-rc1
    • None
    • Querying
    • Query Optimization
    • ALL

    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);

      Attachments

        Issue Links

          is duplicated by

          Task - A task that needs to be done. SERVER-80233 Implement index prefix heuristic

          • Major - P3 - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. SERVER-16350 Wrong index choice when request contains _id in query + sort command

          • Critical - P2 - Crashes, loss of data, severe memory leak.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-20616 Plan ranker sampling from the beginning of a query's execution can result in poor plan selection

          • Major - P3 - Major loss of function.
          • Backlog

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-21697 Plan ranking should take query and index keys into consideration for breaking ties

          • Major - P3 - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-40844 Better tie breaking of "perfect" indexes

          • Major - P3 - Major loss of function.
          • Closed
          related to

          Bug - A problem which impairs or prevents the functions of the product. SERVER-24312 Mongo Query Taking Lot of Time Selecting Index

          • Major - P3 - Major loss of function.
          • Closed

          Activity

            People

              backlog-query-optimization Backlog - Query Optimization
              rassi J Rassi
              Votes:
              12 Vote for this issue
              Watchers:
              34 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: