Details
-
Question
-
Resolution: Done
-
Major - P3
-
None
-
3.4.2
-
None
Description
I have a collection where my documents looks like:
{
|
"_id" : ObjectId("591dbe4a77d4ede22d765250"),
|
"name" : [
|
{
|
"de" : true,
|
"text" : "Brunhilde"
|
},
|
{
|
"sk" : true,
|
"text" : "Šimon"
|
}
|
]
|
}
|
I have defined an index as:
> db.names.createIndex({ 'name.de': 1, 'name.text': 1 }, { name: 'name_de', partialFilterExpression: { 'name.de': { $exists: true } }, collation: { locale: 'de' } });
|
When I do a query like:
> db.names.find({ 'name.de': true, 'name.text': 'Rüdi' }).collation({ locale: 'de' });
|
The explain plan looks like:
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"name.text" : {
|
"$eq" : "Rüdi"
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"name.de" : 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.de" : [
|
"name"
|
],
|
"name.text" : [
|
"name"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : true,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"name.de" : [
|
"[true, true]"
|
],
|
"name.text" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
It does IXSCAN followed by FETCH stage with filter. I've already created an question about the filter here.
The more interesting is what will happen when I just change the matching part of the query to:
> db.names.find({ 'name.de': { $exists: true }, 'name.text': 'Rüdi' }).collation({ locale: 'de' });
|
i.e. expression `'name.de': { $exists: true }` should be still subset of `partialFilterExpression`. As stated in documentation:
> To use the partial index, a query must contain the filter expression (or a modified filter expression that specifies a subset of the filter expression) as part of its query condition.
But the explain plan looks like this:
...
|
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"name.de" : {
|
"$exists" : true
|
}
|
},
|
{
|
"name.text" : {
|
"$eq" : "Rüdi"
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"name.de" : 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.de" : [
|
"name"
|
],
|
"name.text" : [
|
"name"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : true,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"name.de" : [
|
"[MinKey, MaxKey]"
|
],
|
"name.text" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
...
|
As you can see index is used, but the whole filtering is happening in FETCH stage.
Question is: *why the filtering is done in FETCH stage and what is so different between these 2 queries that MongoDB them differently?*
Additionaly, sort query with `$exists` as:
> db.names.find({ 'name.de': { $exists: true } }).sort({ 'name.text': 1 }).collation({ locale: "de" })
|
Behaves the same, whole filtering and sorting is done after IXSCAN stage:
...
|
"winningPlan" : {
|
"stage" : "SORT",
|
"sortPattern" : {
|
"name.text" : 1
|
},
|
"inputStage" : {
|
"stage" : "SORT_KEY_GENERATOR",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"name.de" : {
|
"$exists" : true
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"name.de" : 1,
|
"name.text" : 1
|
}
|
...
|
It even produces the incorrect results, while index is not used for sorting.
Attachments
Issue Links
- is duplicated by
-
SERVER-29346 Why is MongoDB not using index correctly? Is this bug?
-
- Closed
-