Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-54768

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

    XMLWordPrintableJSON

Details

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Major - P3 Major - P3
    • None
    • None
    • Querying
    • None
    • Query Optimization

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

       

      Attachments

        Activity

          People

            backlog-query-optimization Backlog - Query Optimization
            ian.boros@mongodb.com Ian Boros
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated: