-
Type:
Question
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 3.4.2
-
Component/s: Querying
-
None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
I have a collection where I have a data in multilanguage form like this:
> db.names.findOne();
{
"_id" : ObjectId("591dbcd877d4ede22d765247"),
"name" : [
{
"locale" : "de",
"text" : "Brunhilde"
},
{
"locale" : "sk",
"text" : "Šimon"
}
]
}
I have defined an indices for each language as:
db.names.createIndex({ 'name.locale': 1, 'name.text': 1 }, { name: 'name_de', partialFilterExpression: { 'name.locale': 'de' }, collation: { locale: 'de' } });
db.names.createIndex({ 'name.locale': 1, 'name.text': 1 }, { name: 'name_sk', partialFilterExpression: { 'name.locale': 'sk' }, collation: { locale: 'sk' } });
When I'm quering the collection as:
> db.names.find({ 'name.locale': 'de', 'name.text': 'Rüdi' }).collation({ locale: 'de' });
The query uses index, but explain plan looks a bit weird:
...
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"name.text" : {
"$eq" : "Rüdi"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 1,
"needTime" : 8,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 9,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 9,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 9,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"name.locale" : 1,
"name.text" : 1
},
"indexName" : "name_de",
"collation" : {
"locale" : "de",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 3,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : true,
"multiKeyPaths" : {
"name.locale" : [
"name"
],
"name.text" : [
"name"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name.locale" : [
"[\"/1\u0001\u0006\u0001\u0006\", \"/1\u0001\u0006\u0001\u0006\"]"
],
"name.text" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 9,
"seeks" : 1,
"dupsTested" : 9,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
It makes an IXSCAN, but then in FETCH stage MongoDB filters thru "name.text".
On other hand when I make query where "name.text" is used to sort the results as:
> db.names.find({ 'name.locale': 'de' }).sort({ 'name.text': 1 }).collation({ locale: "de" });
The explain plan looks like:
...
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 9,
"executionTimeMillis" : 1,
"totalKeysExamined" : 9,
"totalDocsExamined" : 9,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 9,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 9,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 9,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 9,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 9,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"name.locale" : 1,
"name.text" : 1
},
"indexName" : "name_de",
"collation" : {
"locale" : "de",
"caseLevel" : false,
"caseFirst" : "off",
"strength" : 3,
"numericOrdering" : false,
"alternate" : "non-ignorable",
"maxVariable" : "punct",
"normalization" : false,
"backwards" : false,
"version" : "57.1"
},
"isMultiKey" : true,
"multiKeyPaths" : {
"name.locale" : [
"name"
],
"name.text" : [
"name"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : true,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name.locale" : [
"[\"/1\u0001\u0006\u0001\u0006\", \"/1\u0001\u0006\u0001\u0006\"]"
],
"name.text" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 9,
"seeks" : 1,
"dupsTested" : 9,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
Now the IXSCAN is followed by FETCH without filter.
Question is: why in the first case the filter in FETCH is used and it is not enought to use just index?
In both cases it the `indexBounds` in explain plan are the same, that can explain why in the 2nd query no filter is necessary. I didn't find any constraints in MongoDB documentation which can explain this behavior.
- duplicates
-
-
- Closed
-