|
Additional information, when I try to use alternation of my index as:
> db.names.createIndex({ 'name.text': 1 }, { name: 'name_de', partialFilterExpression: { 'name.locale': 'de' }, collation: { locale: 'de' } });
|
i.e. omitting the field `name.locale` from index. It gives and interesting results, bud sadly still wrong ones. First query explain plan is:
...
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 1,
|
"executionTimeMillis" : 0,
|
"totalKeysExamined" : 1,
|
"totalDocsExamined" : 1,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"name.locale" : {
|
"$eq" : "de"
|
}
|
},
|
"nReturned" : 1,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 2,
|
"advanced" : 1,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 1,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 1,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 2,
|
"advanced" : 1,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"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.text" : [
|
"name"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : true,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"name.text" : [
|
"[\"KQ/9\u0001D�\u0006\u0001�\b\", \"KQ/9\u0001D�\u0006\u0001�\b\"]"
|
]
|
},
|
"keysExamined" : 1,
|
"seeks" : 1,
|
"dupsTested" : 1,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
}
|
}
|
...
|
It still does filter in FETCH but via `name.locale` and not via `name.text`. The `indexBounds` changed too and contains `name.text` pattern.
Second query explain plan is:
...
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 9,
|
"executionTimeMillis" : 0,
|
"totalKeysExamined" : 18,
|
"totalDocsExamined" : 9,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"name.locale" : {
|
"$eq" : "de"
|
}
|
},
|
"nReturned" : 9,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 19,
|
"advanced" : 9,
|
"needTime" : 9,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 9,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 9,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 19,
|
"advanced" : 9,
|
"needTime" : 9,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"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.text" : [
|
"name"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : true,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"name.text" : [
|
"[MinKey, MaxKey]"
|
]
|
},
|
"keysExamined" : 18,
|
"seeks" : 1,
|
"dupsTested" : 18,
|
"dupsDropped" : 9,
|
"seenInvalidated" : 0
|
}
|
}
|
}
|
...
|
The `indexBounds` pattern contains min/max ranges of `name.text` (which is clear, while used in sort) and FETCH contains the filter. But ... result is wrong, documents are no more sorted by german translation as before:
...
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524f"), "name" : [ { "locale" : "de", "text" : "Mathias" }, { "locale" : "sk", "text" : "Abel" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524e"), "name" : [ { "locale" : "de", "text" : "Rudi" }, { "locale" : "sk", "text" : "Ábel" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d765248"), "name" : [ { "locale" : "de", "text" : "Brünhild" }, { "locale" : "sk", "text" : "Žofia" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d765247"), "name" : [ { "locale" : "de", "text" : "Brunhilde" }, { "locale" : "sk", "text" : "Šimon" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d765249"), "name" : [ { "locale" : "de", "text" : "Cäcilia" }, { "locale" : "sk", "text" : "Chruňo" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524a"), "name" : [ { "locale" : "de", "text" : "Caecilia" }, { "locale" : "sk", "text" : "Hana" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524b"), "name" : [ { "locale" : "de", "text" : "Günther" }, { "locale" : "sk", "text" : "Cecília" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524c"), "name" : [ { "locale" : "de", "text" : "Gunther" }, { "locale" : "sk", "text" : "Zina" } ] }
|
{ "_id" : ObjectId("591dbcd877d4ede22d76524d"), "name" : [ { "locale" : "de", "text" : "Rüdi" }, { "locale" : "sk", "text" : "Stano" } ] }
|
...
|
IMHO there is really something weird by using this index in MongoDB.
|