Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
4.2.7, 4.2.8, 4.4.0-rc9
-
None
-
None
-
ALL
-
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)
}
ShowPlease 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
- duplicates
-
SERVER-26580 allow using partial index on query where predicate matches partial filter expression
-
- Backlog
-