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

Optimization for sorted $in queries not applied to reverse sort order

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Minor - P4 Minor - P4
    • 2.6.1, 2.7.0
    • Affects Version/s: 2.6.0
    • Component/s: Querying
    • None
    • ALL

      Issue Status as of April 21, 2014

      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.

      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.

      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.

      The query planner now correctly detects the reverse sort order and does not sort the documents in memory anymore.

      Version 2.6.0 is affected by this bug.

      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}).

            david.storch@mongodb.com David Storch
            david.storch@mongodb.com David Storch
            0 Vote for this issue
            7 Start watching this issue