Minor - P4 ISSUE SUMMARY
Queries using $in and .sort() can be handled efficiently by "exploding" the index bounds of the $in predicates and creating several child index scans. In the case where the sort order is reversed from the index spec the query planner adds a blocking sort stage at the end, which impacts the performance and can be avoided.
USER IMPACT
If users rely on these kinds of queries with $in and sort, they can experience slower than expected query results in some cases. If the size of returned documents exceeds the limit of 32MB, the query fails with an error.
WORKAROUNDS
If only the reverse sort order is required, the index can be re-created with the last field order reversed. If both sort orders are required, no workaround is known.
RESOLUTION
The query planner now correctly detects the reverse sort order and does not sort the documents in memory anymore.
AFFECTED VERSIONS
Version 2.6.0 is affected by this bug.
PATCHES
The patch is included in the 2.6.1 production release.
Original description
Say we have index {a: 1, b: 1, c: 1} and we issue the following query:
t.find({a: {$in: [1, 2]}, b: {$in: [3, 4]}}).sort({c: 1});
The explode for scans hack (see SERVER-1205), is able to avoid a blocking sort stage for this query by merge sorting over four child index scans with the following bounds:
- a: [[1, 1]], b: [[3, 3]]
- a: [[1, 1]], b: [[4, 4]]
- a: [[2, 2]], b: [[3, 3]]
- a: [[2, 2]], b: [[4, 4]]
We should be able to do the same thing for query
t.find({a: {$in: [1, 2]}, b: {$in: [3, 4]}}).sort({c: -1});
simply by reversing the direction of the index scans. However, explodeForSort() fails and we end up creating a plan with a blocking sort for the query with .sort({c: -1}).
- is related to
-
SERVER-13611 Missing sort order for compound index leads to unnecessary in-memory sort
-
- Closed
-
-
SERVER-1205 $or sort does not use index ranges expressed in or clauses
-
- Closed
-