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

aggregation $match before $lookup optimization doesn't happen when $expr: $eq is used

    • Fully Compatible
    • ALL
    • Query 2019-12-30, Query Optimization 2021-02-22, Query Optimization 2021-03-08, Query Optimization 2021-03-22, Query Optimization 2021-04-05
    • 120

      We check if $match has an expression that isn't on "as" and move it before $lookup - if it has an expression on "as.x" we move it into $lookup.

      Expected behavior:

      db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z
      ":10,x:{$eq:5}}})
      {
              "stages" : [
                      {
                              "$cursor" : {
                                      "query" : {
                                              "x" : {
                                                      "$eq" : 5
                                              }
                                      },
                                      "queryPlanner" : {
                                              "plannerVersion" : 1,
                                              "namespace" : "tpcds10.foo",
                                              "indexFilterSet" : false,
                                              "parsedQuery" : {
                                                      "x" : {
                                                              "$eq" : 5
                                                      }
                                              },
                                              "winningPlan" : {
                                                      "stage" : "EOF"
                                              },
                                              "rejectedPlans" : [ ]
                                      }
                              }
                      },
                      {
                              "$lookup" : {
                                      "from" : "b",
                                      "as" : "a",
                                      "localField" : "x",
                                      "foreignField" : "y",
                                      "unwinding" : {
                                              "preserveNullAndEmptyArrays" : false
                                      },
                                      "matching" : {
                                              "z" : {
                                                      "$eq" : 10
                                              }
                                      }
                              }
                      }
              ],
              "ok" : 1
      }
       

      Using $expr for x:5 breaks the $lookup matching optimization though equality for x:5 still gets moved before $lookup but does not get pushed into cursor:

      db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z
      ":10,$expr:{$eq:["$x",5]}}})
      {
              "stages" : [
                      {
                              "$cursor" : {
                                      "query" : {
                                      },
                                      "queryPlanner" : {
                                              "plannerVersion" : 1,
                                              "namespace" : "tpcds10.foo",
                                              "indexFilterSet" : false,
                                              "parsedQuery" : {
                                              },
                                              "winningPlan" : {
                                                      "stage" : "EOF"
                                              },
                                              "rejectedPlans" : [ ]
                                      }
                              }
                      },
                      {
                              "$match" : {
                                      "x" : {
                                              "$_internalExprEq" : 5
                                      }
                              }
                      },
                      {
                              "$lookup" : {
                                      "from" : "b",
                                      "as" : "a",
                                      "localField" : "x",
                                      "foreignField" : "y",
                                      "unwinding" : {
                                              "preserveNullAndEmptyArrays" : false
                                      }
                              }
                      },
                      {
                              "$match" : {
                                      "$and" : [
                                              {
                                                      "a.z" : {
                                                              "$eq" : 10
                                                      }
                                              },
                                              {
                                                      "x" : {
                                                              "$_internalExprEq" : 5
                                                      }
                                              },
                                              {
                                                      "$expr" : {
                                                              "$eq" : [
                                                                      "$x",
                                                                      {
                                                                              "$const" : 5
                                                                      }
                                                              ]
                                                      }
                                              }
                                      ]
                              }
                      }
              ],
              "ok" : 1
       

            Assignee:
            alya.berciu@mongodb.com Alya Berciu
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            1 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved: