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

Push $sort before $project and $addFields

    XMLWordPrintableJSON

Details

    • Query Optimization

    Description

      In some cases, a $sort aggregation stage can swap with a preceding $project. Consider the following example:

      db.c.createIndex({a: 1});
      		 
      db.c.aggregate([{$project: {a: 1, b: 1}}, {$sort: {a: 1}}]);

      An explain of this aggregation shows that a COLLSCAN will feed the $project => $sort pipeline. If the agg optimization phase were to swap the $sort with the $project, however, it could push down the $sort and obtain the desired ordering via an index scan. This would remove the sort stage from the query plan entirely, which could result in a substantial performance improvement.

      We would probably want to apply this optimization only if the query planner's sort analysis shows that the sort can be obtained via an appropriate index scan.

      Attachments

        Issue Links

          depends on

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-7568 Aggregation framework favors non-blocking sorts

          • Major - P3 - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. SERVER-38102 Project stage prevents sort stage from using index

          • Major - P3 - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. SERVER-59910 Queries with sort+projection+limit fail unnecessarily because the sort happens before projection

          • Major - P3 - Major loss of function.
          • Closed
          is related to

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-54768 Sort/project re-ordering is inconsistent between find() and aggregation

          • Major - P3 - Major loss of function.
          • Backlog

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-15200 Query planner should move projection stage below sort stage when possible

          • Major - P3 - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-31072 reorder $limit before $lookup in pipeline

          • Major - P3 - Major loss of function.
          • Closed
          related to

          New Feature - A new feature of the product, which has yet to be developed. SERVER-19153 Conditionally push $match before $project

          • Critical - P2 - Crashes, loss of data, severe memory leak.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. SERVER-53638 Enable pushdown of config.cache.chunks $lookup through $sort

          • Major - P3 - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-55417 Conditionally reorder $sort and $lookup depending on whether the query planner allows for an index-provided sort

          • Major - P3 - Major loss of function.
          • Backlog

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-54822 Push $sort before $unwind when possible

          • Major - P3 - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-54823 $sort should be pushed before $lookup when is possible

          • Major - P3 - Major loss of function.
          • Closed

          Activity

            People

              backlog-query-optimization Backlog - Query Optimization
              david.storch@mongodb.com David Storch
              Votes:
              6 Vote for this issue
              Watchers:
              35 Start watching this issue

              Dates

                Created:
                Updated: