Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
4.0.5
-
None
-
Query Optimization
Description
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 |
}
|