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

Support AND_SORTED plans using wildcard indexes

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Unresolved
    • Icon: Major - P3 Major - P3
    • None
    • None
    • Querying
    • None
    • Query Optimization
    • ALL
    • Hide

      > db.foo.ensureIndex({'$**': 1})
      {
              "createdCollectionAutomatically" : false,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      > db.foo.find({a:1, b:1}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.foo",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                              "$and" : [
                                      {
                                              "a" : {
                                                      "$eq" : 1
                                              }
                                      },
                                      {
                                              "b" : {
                                                      "$eq" : 1
                                              }
                                      }
                              ]
                      },
                      "queryHash" : "43CAB4C5",
                      "planCacheKey" : "CEC1F6AF",
                      "winningPlan" : {
                              "stage" : "FETCH",
                              "filter" : {
                                      "b" : {
                                              "$eq" : 1
                                      }
                              },
                              "inputStage" : {
                                      "stage" : "IXSCAN",
                                      "keyPattern" : {
                                              "$_path" : 1,
                                              "a" : 1
                                      },
                                      "indexName" : "$**_1",
                                      "isMultiKey" : false,
                                      "multiKeyPaths" : {
                                              "$_path" : [ ],
                                              "a" : [ ]
                                      },
                                      "isUnique" : false,
                                      "isSparse" : false,
                                      "isPartial" : false,
                                      "indexVersion" : 2,
                                      "direction" : "forward",
                                      "indexBounds" : {
                                              "$_path" : [
                                                      "[\"a\", \"a\"]"
                                              ],
                                              "a" : [
                                                      "[1.0, 1.0]"
                                              ]
                                      }
                              }
                      },
                      "rejectedPlans" : [
                              {
                                      "stage" : "FETCH",
                                      "filter" : {
                                              "a" : {
                                                      "$eq" : 1
                                              }
                                      },
                                      "inputStage" : {
                                              "stage" : "IXSCAN",
                                              "keyPattern" : {
                                                      "$_path" : 1,
                                                      "b" : 1
                                              },
                                              "indexName" : "$**_1",
                                              "isMultiKey" : false,
                                              "multiKeyPaths" : {
                                                      "$_path" : [ ],
                                                      "b" : [ ]
                                              },
                                              "isUnique" : false,
                                              "isSparse" : false,
                                              "isPartial" : false,
                                              "indexVersion" : 2,
                                              "direction" : "forward",
                                              "indexBounds" : {
                                                      "$_path" : [
                                                              "[\"b\", \"b\"]"
                                                      ],
                                                      "b" : [
                                                              "[1.0, 1.0]"
                                                      ]
                                              }
                                      }
                              }
                      ]
              },
              "serverInfo" : {
                      "host" : "silversurfer-wsl",
                      "port" : 27017,
                      "version" : "4.4.3",
                      "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
              },
              "ok" : 1
      }
      

      Compare that to:

      > db.foo.ensureIndex({a: 1})
      {
              "createdCollectionAutomatically" : true,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      > db.foo.ensureIndex({b: 1})
      {
              "createdCollectionAutomatically" : false,
              "numIndexesBefore" : 2,
              "numIndexesAfter" : 3,
              "ok" : 1
      }
      > db.foo.find({a:1, b:1}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.foo",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                              "$and" : [
                                      {
                                              "a" : {
                                                      "$eq" : 1
                                              }
                                      },
                                      {
                                              "b" : {
                                                      "$eq" : 1
                                              }
                                      }
                              ]
                      },
                      "queryHash" : "43CAB4C5",
                      "planCacheKey" : "CEC1F6AF",
                      "winningPlan" : {
                              "stage" : "FETCH",
                              "filter" : {
                                      "b" : {
                                              "$eq" : 1
                                      }
                              },
                              "inputStage" : {
                                      "stage" : "IXSCAN",
                                      "keyPattern" : {
                                              "a" : 1
                                      },
                                      "indexName" : "a_1",
                                      "isMultiKey" : false,
                                      "multiKeyPaths" : {
                                              "a" : [ ]
                                      },
                                      "isUnique" : false,
                                      "isSparse" : false,
                                      "isPartial" : false,
                                      "indexVersion" : 2,
                                      "direction" : "forward",
                                      "indexBounds" : {
                                              "a" : [
                                                      "[1.0, 1.0]"
                                              ]
                                      }
                              }
                      },
                      "rejectedPlans" : [
                              {
                                      "stage" : "FETCH",
                                      "filter" : {
                                              "a" : {
                                                      "$eq" : 1
                                              }
                                      },
                                      "inputStage" : {
                                              "stage" : "IXSCAN",
                                              "keyPattern" : {
                                                      "b" : 1
                                              },
                                              "indexName" : "b_1",
                                              "isMultiKey" : false,
                                              "multiKeyPaths" : {
                                                      "b" : [ ]
                                              },
                                              "isUnique" : false,
                                              "isSparse" : false,
                                              "isPartial" : false,
                                              "indexVersion" : 2,
                                              "direction" : "forward",
                                              "indexBounds" : {
                                                      "b" : [
                                                              "[1.0, 1.0]"
                                                      ]
                                              }
                                      }
                              },
                              {
                                      "stage" : "FETCH",
                                      "filter" : {
                                              "$and" : [
                                                      {
                                                              "a" : {
                                                                      "$eq" : 1
                                                              }
                                                      },
                                                      {
                                                              "b" : {
                                                                      "$eq" : 1
                                                              }
                                                      }
                                              ]
                                      },
                                      "inputStage" : {
                                              "stage" : "AND_SORTED",
                                              "inputStages" : [
                                                      {
                                                              "stage" : "IXSCAN",
                                                              "keyPattern" : {
                                                                      "a" : 1
                                                              },
                                                              "indexName" : "a_1",
                                                              "isMultiKey" : false,
                                                              "multiKeyPaths" : {
                                                                      "a" : [ ]
                                                              },
                                                              "isUnique" : false,
                                                              "isSparse" : false,
                                                              "isPartial" : false,
                                                              "indexVersion" : 2,
                                                              "direction" : "forward",
                                                              "indexBounds" : {
                                                                      "a" : [
                                                                              "[1.0, 1.0]"
                                                                      ]
                                                              }
                                                      },
                                                      {
                                                              "stage" : "IXSCAN",
                                                              "keyPattern" : {
                                                                      "b" : 1
                                                              },
                                                              "indexName" : "b_1",
                                                              "isMultiKey" : false,
                                                              "multiKeyPaths" : {
                                                                      "b" : [ ]
                                                              },
                                                              "isUnique" : false,
                                                              "isSparse" : false,
                                                              "isPartial" : false,
                                                              "indexVersion" : 2,
                                                              "direction" : "forward",
                                                              "indexBounds" : {
                                                                      "b" : [
                                                                              "[1.0, 1.0]"
                                                                      ]
                                                              }
                                                      }
                                              ]
                                      }
                              }
                      ]
              },
              "serverInfo" : {
                      "host" : "silversurfer-wsl",
                      "port" : 27017,
                      "version" : "4.4.3",
                      "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
              },
              "ok" : 1
      }
      

      Show
      > db.foo.ensureIndex({'$**': 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "a" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"a\", \"a\"]" ], "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "$_path" : 1, "b" : 1 }, "indexName" : "$**_1", "isMultiKey" : false, "multiKeyPaths" : { "$_path" : [ ], "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "$_path" : [ "[\"b\", \"b\"]" ], "b" : [ "[1.0, 1.0]" ] } } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 } Compare that to: > db.foo.ensureIndex({a: 1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > db.foo.ensureIndex({b: 1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } > db.foo.find({a:1, b:1}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "queryHash" : "43CAB4C5", "planCacheKey" : "CEC1F6AF", "winningPlan" : { "stage" : "FETCH", "filter" : { "b" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } } }, "rejectedPlans" : [ { "stage" : "FETCH", "filter" : { "a" : { "$eq" : 1 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } }, { "stage" : "FETCH", "filter" : { "$and" : [ { "a" : { "$eq" : 1 } }, { "b" : { "$eq" : 1 } } ] }, "inputStage" : { "stage" : "AND_SORTED", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "a" : 1 }, "indexName" : "a_1", "isMultiKey" : false, "multiKeyPaths" : { "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "a" : [ "[1.0, 1.0]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "b" : 1 }, "indexName" : "b_1", "isMultiKey" : false, "multiKeyPaths" : { "b" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "b" : [ "[1.0, 1.0]" ] } } ] } } ] }, "serverInfo" : { "host" : "silversurfer-wsl", "port" : 27017, "version" : "4.4.3", "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13" }, "ok" : 1 }
    • Query Optimization 2021-03-08

    Description

      Ideally wildcard indexes should be usable anywhere an infinite number of {a:1}, {b:1}, etc indexes would be. But right now a query like {a:1, b:1} will use AND_SORTED of two IX_SCANs, but it won't try to use the equivalent plan if you have a wildcard index

      Attachments

        Activity

          People

            backlog-query-optimization Backlog - Query Optimization
            mathias@mongodb.com Mathias Stearn
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated: