Details
-
Question
-
Resolution: Duplicate
-
Major - P3
-
None
-
3.4.2
-
None
Description
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.
Attachments
Issue Links
- duplicates
-
SERVER-29347 Why is MongoDB treating queries differently when served from same index?
-
- Closed
-