[SERVER-26442] Push $sort before $project and $addFields Created: 03/Oct/16 Updated: 02/Feb/24 |
|
| Status: | Open |
| Project: | Core Server |
| Component/s: | Aggregation Framework, Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | David Storch | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 7 |
| Labels: | bonsai, optimization, query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
In some cases, a $sort aggregation stage can swap with a preceding $project. Consider the following example:
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. |
| Comments |
| Comment by Anshu Avinash [ 05/Jun/23 ] | ||
|
We have a use case where this would be useful. We are using "$addFields" to dynamically add new fields to every aggregation pipeline. Our aggregation pipeline currently looks like this: | ||
| Comment by Githook User [ 06/Sep/22 ] | ||
|
Author: {'name': 'Ted Tuckman', 'email': 'TedTuckman@users.noreply.github.com', 'username': 'TedTuckman'}Message: SERVER-26442 Remove extra perf test (#733) | ||
| Comment by Githook User [ 16/Aug/22 ] | ||
|
Author: {'name': 'Ted Tuckman', 'email': 'TedTuckman@users.noreply.github.com', 'username': 'TedTuckman'}Message: SERVER-26442 Add perf test for project sort swap for sort pushdown (#725) | ||
| Comment by Asya Kamsky [ 30/Sep/19 ] | ||
|
This is the same when sort is followed by a limit and that makes it even more important to push it down so that we don't return a full batch to aggregation to find a single highest value. | ||
| Comment by Danny Hatcher (Inactive) [ 13/Nov/18 ] | ||
|
This issue also can prevent queries on views from obtaining non-blocking sort plans. | ||
| Comment by Asya Kamsky [ 27/Sep/17 ] | ||
|
I also realized that when we have
| ||
| Comment by Asya Kamsky [ 06/Jan/17 ] | ||
|
$sort can also be pushed in front of $*lookup and $unwind when on fields not involved in being unwound and not coming from lookups. |