Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-14252

Investigate changes in SERVER-39943: Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.9.0
    • Component/s: manual, Server
    • Labels:
      None
    • Last comment by Customer:
      true
    • Story Points:
      2
    • Sprint:
      ServerDocs2020: Mar2 - Mar9, ServerDocs2020: Mar9 - Mar16

      Description

      Description

      Downstream Change Summary

      Marking this for documentation changes since I noticed we document that we only use indexes for equality predicates in the 'let' parameter documentation for $lookup:
      https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#lookup-join-let

      After this ticket, any comparison inside $expr is eligible for index use, if the right index exists: $eq, $lt, $lte, $gt, $gte

      Description of Linked Ticket

      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
      }
      

      Scope of changes

      Impact to Other Docs

      MVP (Work and Date)

      Resources (Scope or Design Docs, Invision, etc.)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jason.price Jason Price
              Reporter:
              backlog-server-pm Backlog - Core Eng Program Management Team
              Participants:
              Last commenter:
              Jason Price Jason Price
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Days since reply:
                4 weeks, 6 days ago
                Date of 1st Reply: