[SERVER-54768] Sort/project re-ordering is inconsistent between find() and aggregation Created: 24/Feb/21  Updated: 06/Dec/22

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

Type: Improvement Priority: Major - P3
Reporter: Ian Boros 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-26442 Push $sort before $project and $addFi... Open
is related to SERVER-54128 Computed projection should not be pus... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

In the find() layer, we attempt to push projects before sorts, in the case where the project makes the document smaller. See here. There is no equivalent optimization in the agg layer.

 

So, running:

db.c.explain().aggregate([{$sort: {a:1}}, {$project: {a: 1}}]) 

We use the following plan (PROJECT then SORT)

                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "a" : 1
                        },
                        "memLimit" : 104857600,
                        "type" : "simple",
                        "inputStage" : {
                                "stage" : "PROJECTION_SIMPLE",
                                "transformBy" : {
                                        "_id" : true,
                                        "a" : true
                                },
                                "inputStage" : {
                                        "stage" : "COLLSCAN",
                                        "direction" : "forward"
                                }
                        }
                },
 

 

On the other hand, this query (same as above, but only using the agg layer):

db.c.explain().aggregate([{$_internalInhibitOptimization: {}}, {$sort: {a:1}}, {$project: {a: 1}}]) 

Will use the following plan (SORT then PROJECT):

        "stages" : [
                {
                        "$cursor" : {
                                "queryPlanner" : {
                                        "namespace" : "test.c",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                
                                        },
                                        "queryHash" : "8B3D4AB8",
                                        "planCacheKey" : "8B3D4AB8",
                                        "maxIndexedOrSolutionsReached" : false,
                                        "maxIndexedAndSolutionsReached" : false,
                                        "maxScansToExplodeReached" : false,
                                        "winningPlan" : {
                                                "stage" : "COLLSCAN",
                                                "direction" : "forward"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$_internalInhibitOptimization" : {
                                
                        }
                },
                {
                        "$sort" : {
                                "sortKey" : {
                                        "a" : 1
                                }
                        }
                },
                {
                        "$project" : {
                                "_id" : true,
                                "a" : true
                        }
                }
        ] 

 



 Comments   
Comment by James Wahlin [ 10/Mar/21 ]

We could consider adding this optimization in the case where the $project is a simple inclusion/exclusion projection, so likely to reduce document size and allow for a smaller memory footprint on sort.

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