Update: I created a ticket: https://jira.mongodb.org/browse/SERVER-48777
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' }
])
db.getCollection('inventory').createIndex({ type: 1, name: 1 }, { name: 'byType' })
db.getCollection('inventory').createIndex({ name: 1 }, { name: 'onlyRed', partialFilterExpression: { color: 'red' } })
// find cherry by type -> totalDocsExamined = 0 (as expected)
db.getCollection('inventory').find({ type: 'food', name: /c/ }, { name: 1, _id: 0 }).explain('executionStats').executionStats.totalDocsExamined
// find cherry by color -> totalDocsExamined = 1 :-(
db.getCollection('inventory').find({ color: 'red', name: /c/ }, { name: 1, _id: 0 }).explain('executionStats').executionStats.totalDocsExamined
The indices of the example above are:
db.getCollection('inventory').getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"type" : 1.0,
"name" : 1.0
},
"name" : "byType"
},
{
"v" : 2,
"key" : {
"name" : 1.0
},
"name" : "onlyRed",
"partialFilterExpression" : {
"color" : "red"
}
}
]
All the following version show this behaviour: 4.2.1, 4.2.7, 4.4.0-rc9
I can't find anything in the documentation for Covered Queries and Partial Indexes why it shouldn't be supported.
explain of query1:
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "demo.inventory",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"type" : {
"$eq" : "food"
}
},
{
"name" : {
"$regex" : "c"
}
}
]
},
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"name" : 1.0,
"_id" : 0.0
},
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "c"
}
},
"keyPattern" : {
"type" : 1.0,
"name" : 1.0
},
"indexName" : "byType",
"isMultiKey" : false,
"multiKeyPaths" : {
"type" : [],
"name" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"type" : [
"[\"food\", \"food\"]"
],
"name" : [
"[\"\", {})",
"[/c/, /c/]"
]
}
}
},
"rejectedPlans" : []
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 2,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION_COVERED",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"transformBy" : {
"name" : 1.0,
"_id" : 0.0
},
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"name" : {
"$regex" : "c"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"type" : 1.0,
"name" : 1.0
},
"indexName" : "byType",
"isMultiKey" : false,
"multiKeyPaths" : {
"type" : [],
"name" : []
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"type" : [
"[\"food\", \"food\"]"
],
"name" : [
"[\"\", {})",
"[/c/, /c/]"
]
},
"keysExamined" : 2,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "mongo",
"port" : 27017,
"version" : "4.4.0-rc9",
"gitVersion" : "bea79f76addfe4b754c8696db029c5b3c762041c"
},
"ok" : 1.0,
"$clusterTime" : {
"clusterTime" : Timestamp(1591984674, 1),
"signature" : {
"hash" : { "$binary" : "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type" : "00" },
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1591984674, 1)
}
explain of query2 (using partial index):
{
"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)
}
While as you know there is a limitation in these types of indexes tracked in MongoDB Jira, there is a simple workaround to this - add the field in the partial index expression to the index definition as a key. This will allow index only plan to be chosen.
That was tracked by SERVER-28889 which was recently fixed.