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

Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite

    XMLWordPrintableJSON

Details

    • Fully Compatible
    • v4.4
    • Query 2020-11-30, Query 2020-12-14, Query 2020-12-28, Query 2021-01-11, Query 2021-01-25, Query 2021-02-08

    Description

      When aggregation operators such as $gt/$gte/$lt/$lte are used within $expr, we will not rewrite to match expression. This is because they would not be eligible to use indexes due to type bracketing differences between match and aggregation. We should consider adding regardless, as the rewrite would make these expressions eligible for aggregation match optimization, such as repositioning a comparison expression in the pipeline.

      The following is an example of where this would be useful. Ideally we would rewrite the {$expr": {"$gt" : [ "$x",

      {"$const": 5}

      ]}} and move before the $lookup stage as an optimization.

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

      Attachments

        Issue Links

          Activity

            People

              katherine.wu@mongodb.com Katherine Wu (Inactive)
              james.wahlin@mongodb.com James Wahlin
              Votes:
              0 Vote for this issue
              Watchers:
              22 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: