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

Optimize queries on time-series collections which request the most recent value

    • Type: Icon: Improvement Improvement
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Query Planning
    • None
    • Query Optimization 2021-04-05, Query Optimization 2021-04-19, Query Optimization 2021-05-03, Query Optimization 2021-05-17, Query Optimization 2021-05-31, Query Optimization 2021-06-14

      There are a couple ways to write a similar query by flipping the sort and $first/$last, but consider this example:

      db.my_timeseries.aggregate([
        {$sort: {ts: 1}},
        {$group: {_id: "$meta.x", most_recent_foo: {$last: "$foo"}}}
      ])
      

      This is the same pattern as described in SERVER-9507, and we should be able to have this query do something like a DISTINCT_SCAN if one of the following indexes exist:

      {ts: +/-1, meta: +/-1}
      {meta: +/-1, ts: +/-1}
      {_id: +/-1, meta: +/-1}
      {meta: +/-1, _id: +/-1}
      

      The last two would probably be pretty challenging to implement and would have to do some similar analysis to SERVER-55106 in order to translate the "ts" predicate/scan into something on _id. It may not even be possible.

      We could also instead consider transforming the query not into a distinct scan but into a reverse _id scan to ensure whatever we find is most recent, and performing a streaming $group implementation (SERVER-4507). This is generally a hard operation to perform, but within the context of a time-series collection it might be easier to prove that the optimization is correct.

        1. g1-1.png
          5 kB
          Ruslan Abdulkhalikov

            Assignee:
            ruslan.abdulkhalikov@mongodb.com Ruslan Abdulkhalikov (Inactive)
            Reporter:
            charlie.swanson@mongodb.com Charlie Swanson
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: