-
Type: Bug
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: Querying
-
Fully Compatible
-
ALL
-
23
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.
- related to
-
SERVER-12769 Queries that scan an entire index to provide a sort should be covered when possible
- Open