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

Query optimizer should consider the projection when selecting a plan

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.4.6
    • Component/s: Querying
    • None

      Given indexes:

      { a: 1, b: 1 }

      and

      { a: 1, c: 1 }

      , a range query on 'a' with a projection on

      { a: 1, c: 1, _id: 0 }

      will choose the non-covering index

      { a: 1, b: 1 }

      when it should choose the covering index

      { a: 1, c: 1 }

      .

      This happens because the optimizer doesn't consider the projection when selecting a plan, so it chooses the first plan that seems optimal (which is a: 1, b: 1 because that index appears first in the list of indexes for the namespace).

      To reproduce:
      db.a.ensureIndex(

      { a: 1, b: 1 }

      )
      db.a.ensureIndex(

      { a: 1, c: 1 }

      )
      db.a.insert(

      { a: 10, b: 10, c: 10 }

      )
      db.a.find({ a:

      { $gte: 10, $lte: 12 }

      },

      { a: 1, c: 1, _id: 0 }

      )

      The current work-around is to add a hint for the proper covering index. It would be neat if the correct index was chosen without a hint, though.

            Assignee:
            Unassigned Unassigned
            Reporter:
            esmet John Esmet
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: