Hi,
I have a collection named “words” and the document has this format
{
_id: ObjectId("5f5645e0738d5c3d17009ed5"),
value: "a"
}
The query is like this:
db.words.find({value: 'a'}, {_id: 0, value: 1}).explain()
I create an Index like this:
db.words.createIndex({value: 1})
The result is:
{
"queryPlanner" : {
"plannerVersion" : 1.0,
"namespace" : "testCollation.words",
"indexFilterSet" : false,
"parsedQuery" : {
"value" : {
"$eq" : "a"
}
},
"queryHash" : "7949E00B",
"planCacheKey" : "F031A68B",
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"_id" : 0.0,
"value" : 1.0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"value" : 1.0
},
"indexName" : "value_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"value" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"value" : [
"[\"a\", \"a\"]"
]
}
}
},
"rejectedPlans" : [
]
},
"serverInfo" : {
"host" : "97e3c723542e",
"port" : 27017.0,
"version" : "4.2.18",
"gitVersion" : "f65ce5e25c0b26a00d091a4d24eec1a8b3a4c016"
},
"ok" : 1.0
}
As you can see, the stage is PROJECT_COVERED. That’s perfect.
If I re-create the Index with a Collation like this:
db.words.createIndex({value: 1}, {collation: {locale: 'da'}})
and update the query to:
db.words.find({value: 'a'}, {_id: 0, value: 1}).collation({locale: "da"}).explain()
The result is not PROJECT_COVERED anymore:
{
"queryPlanner" : {
"plannerVersion" : 1.0,
"namespace" : "testCollation.words",
"indexFilterSet" : false,
"parsedQuery" : {
"value" : {
"$eq" : "a"
}
},
"collation" : {
"locale" : "da",
"caseLevel" : false,
"caseFirst" : "upper",
"strength" : 3.0,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"queryHash" : "C75CDE76",
"planCacheKey" : "C0A8436D",
"winningPlan" : {
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"_id" : 0.0,
"value" : 1.0
},
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"value" : 1.0
},
"indexName" : "value_1",
"collation" : {
"locale" : "da",
"caseLevel" : false,
"caseFirst" : "upper",
"strength" : 3.0,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : false,
"multiKeyPaths" : {
"value" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"value" : [
"[\")\u0001\u0005\u0001�\", \")\u0001\u0005\u0001�\"]"
]
}
}
}
},
"rejectedPlans" : [
]
},
"serverInfo" : {
"host" : "97e3c723542e",
"port" : 27017.0,
"version" : "4.2.18",
"gitVersion" : "f65ce5e25c0b26a00d091a4d24eec1a8b3a4c016"
},
"ok" : 1.0
}
Why does the Index with Collation has the stage Fetch?
And how can I make the Collation works with Covered Indexes?
Thank you