Sort/project re-ordering is inconsistent between find() and aggregation

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • Query Optimization
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      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
                              }
                      }
              ] 

       

            Assignee:
            [DO NOT USE] Backlog - Query Optimization
            Reporter:
            Ian Boros
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated: