Details
-
Improvement
-
Status: Open
-
Major - P3
-
Resolution: Unresolved
-
None
-
None
-
Query Optimization
-
(copied to CRM)
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
-
SERVER-7568 Aggregation framework favors non-blocking sorts
-
- Closed
-
- is duplicated by
-
SERVER-38102 Project stage prevents sort stage from using index
-
- Closed
-
-
SERVER-59910 Queries with sort+projection+limit fail unnecessarily because the sort happens before projection
-
- Closed
-
- is related to
-
SERVER-54768 Sort/project re-ordering is inconsistent between find() and aggregation
-
- Backlog
-
-
SERVER-15200 Query planner should move projection stage below sort stage when possible
-
- Closed
-
-
SERVER-31072 reorder $limit before $lookup in pipeline
-
- Closed
-
- related to
-
SERVER-19153 Conditionally push $match before $project
-
- Closed
-
-
SERVER-53638 Enable pushdown of config.cache.chunks $lookup through $sort
-
- Closed
-
-
SERVER-55417 Conditionally reorder $sort and $lookup depending on whether the query planner allows for an index-provided sort
-
- Backlog
-
-
SERVER-54822 Push $sort before $unwind when possible
-
- Closed
-
-
SERVER-54823 $sort should be pushed before $lookup when is possible
-
- Closed
-