[SERVER-56581] Flip window-function sortBy and bounds to save space Created: 03/May/21  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: David Percy Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-56450 Allow range-based windows with descen... Backlog
related to SERVER-56574 Coalesce $setWindowFields stages with... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

Right-unbounded windows can use a lot of space.  For example:

{$setWindowFields: {
  sortBy: {time: 1},
  output: {
    s: {$sum: "$x", window: {documents: ['current', 'unbounded']}},
  }
}}

The first output document has to read until the end of the partition to compute the sum.

Flipping the sortBy and bounds would compute the same result, but using less space:

{$setWindowFields: {
  sortBy: {time: -1},
  output: {
    s: {$sum: "$x", window: {documents: ['unbounded', 'current']}},
  }
}}

If there is more than one window function in the stage, we would have to either flip all of them, or split up the stage.

Some window functions, like $rank or $expMovingAvg, can't be flipped. Internally they use a window of [unbounded, current], so flipping them would mean using a window of [current, unbounded], which is what we want to avoid.

Internally, we have to deal with the fact that $_internalSetWindowFields depends on a separate $sort stage. For example, after parsing we have:

{$sort: {time: 1}},
{$_internalSetWindowFields: { sortBy: {time: 1}, ... }}

We could have $_internalSetWindowFields look for a $sort stage before it. Or, it could emit a new $sort stage and let the $sort+$sort optimization clean it up:

{$sort: {time: 1}},
{$sort: {time: -1}},
{$_internalSetWindowFields: { sortBy: {time: -1}, ... }}

{$sort: {time: -1}},
{$_internalSetWindowFields: { sortBy: {time: -1}, ... }}



 Comments   
Comment by David Percy [ 03/May/21 ]

Without SERVER-56450, we would only be able to flip document-based windows.

Comment by David Percy [ 03/May/21 ]

This seems related to SERVER-56574, which is about flipping the sortBy to allow combining two $setWindowFields stages.

Generated at Thu Feb 08 05:39:38 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.