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

compound multikey index doesn't use tight bounds for trailing field in spite of $elemMatch

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • Query Optimization

      Sample data:

      db.elem.find()
      { "_id" : ObjectId("5967e1cbb6a1aa8d363befdd"), "a1" : [ { "id" : "a", "a2" : [ { "s" : 23 }, { "s" : 28 }, { "s" : 30 } ] } ] }
      { "_id" : ObjectId("5967e1d2b6a1aa8d363befde"), "a1" : [ { "id" : "b", "a2" : [ { "s" : 23 }, { "s" : 28 }, { "s" : 30 } ] } ] }
      

      And index on "a1.id":1, "a1.a2.s":1 I would expect the query below to use tight bounds on "a1.id" and on "a1.a2.s" but it uses Minkey,Maxkey on "a1.a2.s":

      db.elem.find({a1:{$elemMatch:{ id: "a", a2:{$elemMatch:{s:{$gt:25,$lt:29}}}}}}).explain(true)
      {
          "queryPlanner" : {
              "plannerVersion" : 1,
              "namespace" : "test.elem",
              "indexFilterSet" : false,
              "parsedQuery" : {
                  "a1" : {
                      "$elemMatch" : {
                          "$and" : [
                              {
                                  "a2" : {
                                      "$elemMatch" : {
                                          "$and" : [
                                              {
                                                  "s" : {
                                                      "$lt" : 29
                                                  }
                                              },
                                              {
                                                  "s" : {
                                                      "$gt" : 25
                                                  }
                                              }
                                          ]
                                      }
                                  }
                              },
                              {
                                  "id" : {
                                      "$eq" : "a"
                                  }
                              }
                          ]
                      }
                  }
              },
              "winningPlan" : {
                  "stage" : "FETCH",
                  "filter" : {
                      "a1" : {
                          "$elemMatch" : {
                              "$and" : [
                                  {
                                      "id" : {
                                          "$eq" : "a"
                                      }
                                  },
                                  {
                                      "a2" : {
                                          "$elemMatch" : {
                                              "$and" : [
                                                  {
                                                      "s" : {
                                                          "$lt" : 29
                                                      }
                                                  },
                                                  {
                                                      "s" : {
                                                          "$gt" : 25
                                                      }
                                                  }
                                              ]
                                          }
                                      }
                                  }
                              ]
                          }
                      }
                  },
                  "inputStage" : {
                      "stage" : "IXSCAN",
                      "keyPattern" : {
                          "a1.id" : 1,
                          "a1.a2.s" : 1
                      },
                      "indexName" : "a1.id_1_a1.a2.s_1",
                      "isMultiKey" : true,
                      "multiKeyPaths" : {
                          "a1.id" : [
                              "a1"
                          ],
                          "a1.a2.s" : [
                              "a1",
                              "a1.a2"
                          ]
                      },
                      "isUnique" : false,
                      "isSparse" : false,
                      "isPartial" : false,
                      "indexVersion" : 2,
                      "direction" : "forward",
                      "indexBounds" : {
                          "a1.id" : [
                              "[\"a\", \"a\"]"
                          ],
                          "a1.a2.s" : [
                              "[MinKey, MaxKey]"
                          ]
                      }
                  }
              },
              "rejectedPlans" : [ ]
          }
      

      If the data is structured with a scalar in a2 (or any other way such that the query on the indexed field is of form {{$elemMatch:{$gt: , $lt }}} then the tight bounds are used.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            5 Vote for this issue
            Watchers:
            24 Start watching this issue

              Created:
              Updated: