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

Don't consider wildcard plans when a perfect specific index exists

    XMLWordPrintableJSON

Details

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

    Description

      If a specific index exists it is probably always better to use than the wildcard index. If not, we should probably warn or maybe even disallow adding the otherwise redundant index.

      One way to do this would be to model the wildcard index purely as a fallback in the QueryPlanner so that when it would go to use an index on a field but there is no index it can use, it will instead make a wildcard scan as-if there was that index on that field. I think that would also solve SERVER-53927

      > db.foo.ensureIndex({'$**': 1})
      {
              "createdCollectionAutomatically" : true,
              "numIndexesBefore" : 1,
              "numIndexesAfter" : 2,
              "ok" : 1
      }
      > db.foo.ensureIndex({a: 1})
      {
              "createdCollectionAutomatically" : false,
              "numIndexesBefore" : 2,
              "numIndexesAfter" : 3,
              "ok" : 1
      }
      > db.foo.find({a:1}).explain()
      {
              "queryPlanner" : {
                      "plannerVersion" : 1,
                      "namespace" : "test.foo",
                      "indexFilterSet" : false,
                      "parsedQuery" : {
                              "a" : {
                                      "$eq" : 1
                              }
                      },
                      "queryHash" : "4B53BE76",
                      "planCacheKey" : "4281AFAD",
                      "winningPlan" : {
                              "stage" : "FETCH",
                              "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",
                                      "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]"
                                                      ]
                                              }
                                      }
                              }
                      ]
              },
              "serverInfo" : {
                      "host" : "silversurfer-wsl",
                      "port" : 27017,
                      "version" : "4.4.3",
                      "gitVersion" : "913d6b62acfbb344dde1b116f4161360acd8fd13"
              },
              "ok" : 1
      }
      

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated: