-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: 4.0.5
-
Component/s: Querying
-
Labels:None
-
Query Optimization
Expected: IXSCAN returns only items where the key is either missing or matched by $mod
Observed: IXSCAN returns all documents in collection, filter is applied during FETCH.
To be clear, in separate queries, using only $exists works fine and using only $mod works fine.
Setup:
db.foo.insert({"_id": 1, "bar": 15})
db.foo.insert({"_id": 2, "bar": 16})
db.foo.insert({"_id": 3})
db.foo.createIndex({"bar": 1})
Demo query:
db.foo.find({
"$or": [
{
"bar": {
"$mod": [
2,
1
]
}
},
{
"bar": {
"$exists": false
}
}
]
}).explain("executionStats")
Observed execution stats:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "test.foo",
"indexFilterSet": false,
"parsedQuery": {
"$or": [
{
"bar": {
"$mod": [
2,
1
]
}
},
{
"$nor": [
{
"bar": {
"$exists": true
}
}
]
}
]
},
"winningPlan": {
"stage": "SUBPLAN",
"inputStage": {
"stage": "FETCH",
"filter": {
"$or": [
{
"bar": {
"$mod": [
2,
1
]
}
},
{
"$nor": [
{
"bar": {
"$exists": true
}
}
]
}
]
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"bar": 1
},
"indexName": "bar_1",
"isMultiKey": false,
"multiKeyPaths": {
"bar": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"bar": [
"[null, null]",
"[nan.0, inf.0]"
]
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 2,
"executionTimeMillis": 0,
"totalKeysExamined": 3,
"totalDocsExamined": 3,
"executionStages": {
"stage": "SUBPLAN",
"nReturned": 2,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 2,
"needTime": 1,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"invalidates": 0,
"inputStage": {
"stage": "FETCH",
"filter": {
"$or": [
{
"bar": {
"$mod": [
2,
1
]
}
},
{
"$nor": [
{
"bar": {
"$exists": true
}
}
]
}
]
},
"nReturned": 2,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 2,
"needTime": 1,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"invalidates": 0,
"docsExamined": 3,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 3,
"executionTimeMillisEstimate": 0,
"works": 4,
"advanced": 3,
"needTime": 0,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"invalidates": 0,
"keyPattern": {
"bar": 1
},
"indexName": "bar_1",
"isMultiKey": false,
"multiKeyPaths": {
"bar": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"bar": [
"[null, null]",
"[nan.0, inf.0]"
]
},
"keysExamined": 3,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0,
"seenInvalidated": 0
}
}
}
},
"serverInfo": {
"host": "My-PC",
"port": 27017,
"version": "4.0.5",
"gitVersion": "3739429dd92b92d1b0ab120911a23d50bf03c412"
},
"ok": 1
}