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

Covered Queries do not work with partial indices

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 4.2.7, 4.2.8, 4.4.0-rc9
    • Fix Version/s: None
    • Component/s: Performance
    • Labels:
      None
    • Operating System:
      ALL
    • Steps To Reproduce:
      Hide

      Please see the example below. Why does 2nd query perform document lookups?

      db.createCollection('inventory')
      db.getCollection('inventory').insertMany([
       
          { type: 'food', color: 'yellow', name: 'banana' },
          { type: 'food', color: 'red',    name: 'cherry' },
          { type: 'car',  color: 'yellow', name: 'taxi' }
       
      ])
      // create partial index, which "should" cover name & color field
      db.getCollection('inventory').createIndex({ name: 1 },          { name: 'onlyRed', partialFilterExpression: {  color: 'red' } })
       
      // totalDocsExamined is one but should be zero
      db.getCollection('inventory').find({ color: 'red', name: /c/ }, { name: 1, _id: 0 }).explain('executionStats').executionStats.totalDocsExamined
      

      Here is the full explain result:

      {
          "queryPlanner" : {
              "plannerVersion" : 1,
              "namespace" : "demo.inventory",
              "indexFilterSet" : false,
              "parsedQuery" : {
                  "$and" : [ 
                      {
                          "color" : {
                              "$eq" : "red"
                          }
                      }, 
                      {
                          "name" : {
                              "$regex" : "c"
                          }
                      }
                  ]
              },
              "winningPlan" : {
                  "stage" : "PROJECTION_SIMPLE",
                  "transformBy" : {
                      "name" : 1.0,
                      "_id" : 0.0
                  },
                  "inputStage" : {
                      "stage" : "FETCH",
                      "filter" : {
                          "color" : {
                              "$eq" : "red"
                          }
                      },
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "filter" : {
                              "name" : {
                                  "$regex" : "c"
                              }
                          },
                          "keyPattern" : {
                              "name" : 1.0
                          },
                          "indexName" : "onlyRed",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "name" : []
                          },
                          "isUnique" : false,
                          "isSparse" : false,
                          "isPartial" : true,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "name" : [ 
                                  "[\"\", {})", 
                                  "[/c/, /c/]"
                              ]
                          }
                      }
                  }
              },
              "rejectedPlans" : []
          },
          "executionStats" : {
              "executionSuccess" : true,
              "nReturned" : 1,
              "executionTimeMillis" : 0,
              "totalKeysExamined" : 1,
              "totalDocsExamined" : 1,
              "executionStages" : {
                  "stage" : "PROJECTION_SIMPLE",
                  "nReturned" : 1,
                  "executionTimeMillisEstimate" : 0,
                  "works" : 2,
                  "advanced" : 1,
                  "needTime" : 0,
                  "needYield" : 0,
                  "saveState" : 0,
                  "restoreState" : 0,
                  "isEOF" : 1,
                  "transformBy" : {
                      "name" : 1.0,
                      "_id" : 0.0
                  },
                  "inputStage" : {
                      "stage" : "FETCH",
                      "filter" : {
                          "color" : {
                              "$eq" : "red"
                          }
                      },
                      "nReturned" : 1,
                      "executionTimeMillisEstimate" : 0,
                      "works" : 2,
                      "advanced" : 1,
                      "needTime" : 0,
                      "needYield" : 0,
                      "saveState" : 0,
                      "restoreState" : 0,
                      "isEOF" : 1,
                      "docsExamined" : 1,
                      "alreadyHasObj" : 0,
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "filter" : {
                              "name" : {
                                  "$regex" : "c"
                              }
                          },
                          "nReturned" : 1,
                          "executionTimeMillisEstimate" : 0,
                          "works" : 2,
                          "advanced" : 1,
                          "needTime" : 0,
                          "needYield" : 0,
                          "saveState" : 0,
                          "restoreState" : 0,
                          "isEOF" : 1,
                          "keyPattern" : {
                              "name" : 1.0
                          },
                          "indexName" : "onlyRed",
                          "isMultiKey" : false,
                          "multiKeyPaths" : {
                              "name" : []
                          },
                          "isUnique" : false,
                          "isSparse" : false,
                          "isPartial" : true,
                          "indexVersion" : 2,
                          "direction" : "forward",
                          "indexBounds" : {
                              "name" : [ 
                                  "[\"\", {})", 
                                  "[/c/, /c/]"
                              ]
                          },
                          "keysExamined" : 1,
                          "seeks" : 1,
                          "dupsTested" : 0,
                          "dupsDropped" : 0
                      }
                  }
              }
          },
          "serverInfo" : {
              "host" : "mongo",
              "port" : 27017,
              "version" : "4.4.0-rc9",
              "gitVersion" : "bea79f76addfe4b754c8696db029c5b3c762041c"
          },
          "ok" : 1.0,
          "$clusterTime" : {
              "clusterTime" : Timestamp(1591984064, 1),
              "signature" : {
                  "hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" },
                  "keyId" : NumberLong(0)
              }
          },
          "operationTime" : Timestamp(1591984064, 1)
      }
      

       

      Show
      Please see the example below. Why does 2nd query perform document lookups? db.createCollection( 'inventory' ) db.getCollection( 'inventory' ).insertMany([   { type: 'food' , color: 'yellow' , name: 'banana' }, { type: 'food' , color: 'red' , name: 'cherry' }, { type: 'car' , color: 'yellow' , name: 'taxi' }   ]) // create partial index, which "should" cover name & color field db.getCollection( 'inventory' ).createIndex({ name: 1 }, { name: 'onlyRed' , partialFilterExpression: { color: 'red' } })   // totalDocsExamined is one but should be zero db.getCollection( 'inventory' ).find({ color: 'red' , name: /c/ }, { name: 1, _id: 0 }).explain( 'executionStats' ).executionStats.totalDocsExamined Here is the full explain result: { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "demo.inventory" , "indexFilterSet" : false , "parsedQuery" : { "$and" : [ { "color" : { "$eq" : "red" } }, { "name" : { "$regex" : "c" } } ] }, "winningPlan" : { "stage" : "PROJECTION_SIMPLE" , "transformBy" : { "name" : 1.0, "_id" : 0.0 }, "inputStage" : { "stage" : "FETCH" , "filter" : { "color" : { "$eq" : "red" } }, "inputStage" : { "stage" : "IXSCAN" , "filter" : { "name" : { "$regex" : "c" } }, "keyPattern" : { "name" : 1.0 }, "indexName" : "onlyRed" , "isMultiKey" : false , "multiKeyPaths" : { "name" : [] }, "isUnique" : false , "isSparse" : false , "isPartial" : true , "indexVersion" : 2, "direction" : "forward" , "indexBounds" : { "name" : [ "[\"\", {})" , "[/c/, /c/]" ] } } } }, "rejectedPlans" : [] }, "executionStats" : { "executionSuccess" : true , "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "PROJECTION_SIMPLE" , "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "transformBy" : { "name" : 1.0, "_id" : 0.0 }, "inputStage" : { "stage" : "FETCH" , "filter" : { "color" : { "$eq" : "red" } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN" , "filter" : { "name" : { "$regex" : "c" } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "keyPattern" : { "name" : 1.0 }, "indexName" : "onlyRed" , "isMultiKey" : false , "multiKeyPaths" : { "name" : [] }, "isUnique" : false , "isSparse" : false , "isPartial" : true , "indexVersion" : 2, "direction" : "forward" , "indexBounds" : { "name" : [ "[\"\", {})" , "[/c/, /c/]" ] }, "keysExamined" : 1, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0 } } } }, "serverInfo" : { "host" : "mongo" , "port" : 27017, "version" : "4.4.0-rc9" , "gitVersion" : "bea79f76addfe4b754c8696db029c5b3c762041c" }, "ok" : 1.0, "$clusterTime" : { "clusterTime" : Timestamp(1591984064, 1), "signature" : { "hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=" , "$type" : "00" }, "keyId" : NumberLong(0) } }, "operationTime" : Timestamp(1591984064, 1) }  

      Description

      A query matching a partial index performs document lookups, although all fields could be covered by the index.

      This issue was observed with the following versions of MongoDB: 4.2.1, 4.2.7, 4.4.0-rc9

      I raised this already on StackOverflow: https://stackoverflow.com/questions/62342543/covered-queries-do-not-work-with-partial-indices

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              carl.champain Carl Champain (Inactive)
              Reporter:
              thilo@staffbase.com Thilo Schmalfuß
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: