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

Optimize $sort+$group+$first pipeline to avoid full index scan

    XMLWordPrintableJSON

Details

    • Fully Compatible
    • Query 2018-06-04, Query 2018-08-13, Query 2018-08-27, Query 2018-09-10, Query 2018-09-24, Query 2018-10-08

    Description

      This is an analogue to SERVER-2094 ("distinct cheat with indexes"), but for the aggregation framework.

      This performance improvement is to allow $group operators like $first to be able to take advantage of the fact that the input to the pipeline is sorted, and thus reduce the number of index entries scanned by "skipping" processing of large portions of the pipeline.

      For example, suppose a user has a collection with an index {x:1,y:1}, and that x has low cardinality. Consider the following pipeline:

      db.foo.aggregate({$sort:{x:1,y:1}},{$group:{_id:{x:"$x"},y:{$first:"$y"}}})

      Currently, the above pipeline will perform a full scan of the index. After this optimization, the above pipeline will only have to scan on the order of |x| index entries, which is much smaller than the size of the index.

      This ticket is filed as a result of discussion in SERVER-9272 (full use case available there).

      Attachments

        Issue Links

          is depended on by

          Task - A task that needs to be done. SERVER-36517 Allow allPaths indexes to provide DISTINCT_SCAN

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

          Task - A task that needs to be done. DOCS-13065 Investigate changes in SERVER-9507: Optimize $sort+$group+$first pipeline to avoid full index scan

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

          New Feature - A new feature of the product, which has yet to be developed. SERVER-9272 Querying latest document based on a set of field

          • Minor - P4 - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-31269 Too many documents examined when using an index and $first/$last in $group stage

          • Minor - P4 - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. SERVER-69359 Aggregate query bails on DISTINCT_SCAN and uses IXSCAN

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

          New Feature - A new feature of the product, which has yet to be developed. SERVER-2130 Ability to use Limit() with Distinct()

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-27915 Make $group with $addToSet accumulator use DISTINCT_SCAN when applicable

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-2094 distinct cheat with indexes

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-15291 slow '$group' performance

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-29244 CLONE - distinct cheat with indexes

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

          Bug - A problem which impairs or prevents the functions of the product. SERVER-37459 $group with $$ROOT returns error

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-4507 aggregation: optimize $group to take advantage of sorted sequences

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-23732 Aggregation should optimize an irrelevant $sort preceding a $group

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-28980 aggregation can subsume $sort into $group when $first/$last are present

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-37715 Use DISTINCT_SCAN for $unwind-$group pipelines

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-37304 Extend $sort+$group+$first pipeline optimization to $last

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-40090 DISTINCT_SCAN in agg is only used when certain format of _id is specified

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

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-55576 Optimize queries on time-series collections which request the most recent value

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

          Activity

            People

              justin.seyster@mongodb.com Justin Seyster
              backlog-server-query Backlog - Query Team (Inactive)
              Votes:
              9 Vote for this issue
              Watchers:
              23 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: