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

Index used for whole IXSCAN plan is determined by index creation order

    XMLWordPrintable

    Details

    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      ALL
    • Linked BF Score:
      23

      Description

      The following query is eligible for a whole IXSCAN solution on any index prefixed by "a":

      db.c.find({}, {"b": 1, _id: 0}).sort({a: 1}).explain()
      

      However, because we "break" in this block as soon as any index which provides the sort is available, we miss out on covered plans. That is, we only consider the "first" whole IXSCAN solution we see.

      So, for example if I were to run:

      > db.c.drop()
      > db.c.createIndex({a: 1})
      > db.c.createIndex({a: 1, b: 1})
      > db.c.find({}, {"b": 1, _id: 0}).sort({a: 1}).explain()
      ...
      

      We would get a non-covered plan, because the "a" index comes first in the list of indexes in the catalog.

      But switch the order we create the indexes in:

      > db.c.drop()
      > db.c.createIndex({a: 1, b: 1})
      > db.c.createIndex({a: 1})
      > db.c.find({}, {"b": 1, _id: 0}).sort({a: 1}).explain()
      ...
      

      And now we have a covered plan because the "a, b" index came first when looking for a whole IXSCAN solution.

      Note that this is a separate issue from SERVER-12769, which is a problem when using a whole IXSCAN solution and the filter is non-empty. This ticket is relevant for cases where the filter is empty.

      Ideas to fix this:
      -Delete that "break" statement (and the similar one for reverse index scans). This will cause both plans to go through the multi planner and plan ranker. Since both plans will have the same output/work ratio, the covered one will be chosen by the plan ranker.
      -If we want the planner to only ever generate one whole IXSCAN solution, we can have it generate all of the possible ones and then see if any are covered, picking that one. This would save work of multi planning (which is basically useless for this case, since there's no filter and all of the indexes are equally selective) but add yet another special code path to the planner.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              james.wahlin James Wahlin
              Reporter:
              ian.boros Ian Boros
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: