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

Allow swapping $match stages that contain an $elemMatch predicate

    XMLWordPrintableJSON

Details

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Major - P3 Major - P3
    • None
    • None
    • None
    • None
    • Query Optimization
    • QO 2024-02-05, QO 2024-02-19

    Description

      The logic to swap a given aggregation stage with a subsequent $match has a very conservative check to consider "array-matching" expressions as basically fully dependent on the input document. This has the effect of preventing any sort of swapping/pushdown of such stages.

      As an example, consider the following query with a sort squeezed between two $match stages:

      [{$match: {a: '07'}}, {$sort: {a: 1}}, {"$match":{"arrayField":{"$elemMatch":{"label":{"$eq":"abc"}}}}}]
      

      And assuming we have an index on {a: 1, arrayField.label: 1}. With the current planner, the $elemMatch ends up as a residual predicate after an IXSCAN:

      {
          "queryPlanner" : {
                  "namespace" : "test.test",
                  "indexFilterSet" : false,
                  "parsedQuery" : {
                          "a" : {
                                  "$eq" : "07"
                          }
                  },
                  "queryHash" : "57073E11",
                  "planCacheKey" : "D0FC46D6",
                  "maxIndexedOrSolutionsReached" : false,
                  "maxIndexedAndSolutionsReached" : false,
                  "maxScansToExplodeReached" : false,
                  "winningPlan" : {
                          "stage" : "FETCH",
                          "inputStage" : {
                                  "stage" : "IXSCAN",
                                  "keyPattern" : {
                                          "a" : 1,
                                          "arrayField.label" : 1
                                  },
                                  "indexName" : "a_1_arrayField.label_1",
                                  "isMultiKey" : true,
                                  "multiKeyPaths" : {
                                          "a" : [ ],
                                          "arrayField.label" : [
                                                  "arrayField"
                                          ]
                                  },
                                  "isUnique" : false,
                                  "isSparse" : false,
                                  "isPartial" : false,
                                  "indexVersion" : 2,
                                  "direction" : "forward",
                                  "indexBounds" : {
                                          "a" : [
                                                  "[\"07\", \"07\"]"
                                          ],
                                          "arrayField.label" : [
                                                  "[MinKey, MaxKey]"
                                          ]
                                  }
                          }
                  },
                  "rejectedPlans" : [ ]
          }
      }
      

      I don't see any particular reason to not use dependency analysis and at least move the $elemMatch ahead of the $sort if it's path is not a prefix or exact match of the dependencies for the preceding stage. In this example, it would allow us to get tighter bounds on the `arrayField.label` component of the index scan.

      Attachments

        Activity

          People

            milena.ivanova@mongodb.com Milena Ivanova
            nicholas.zolnierz@mongodb.com Nicholas Zolnierz
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated: