Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
3.2.9, 3.3.15
-
None
-
None
-
ALL
Description
It appears that when querying with $elemMatch a partial index that could satisfy the query is not being considered, resulting in a collection scan.
If a hint is added to use the partial index the execution stats confirm that the index can be used efficiently.
Also if the query is amended to include the $elemMatch predicate covered by the partial index the same efficient plan is selected.
Consider the following test data and partial index:
db.foo.insert({ state: 'active', properties : [{ substatus : 'scheduled', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
db.foo.insert({ state: 'active', properties : [{ substatus : 'unknown', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
db.foo.insert({ state: 'active', properties : [{ substatus : 'inprogress', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
db.foo.insert({ state: 'completed', properties : [{ substatus : 'processed', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
db.foo.insert({ state: 'completed', properties : [{ substatus : 'returned', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
db.foo.insert({ state: 'completed', properties : [{ substatus : 'failed', date : ISODate("2016-09-15T19:47:35.255Z") }] }) |
|
db.foo.createIndex({
|
state : 1, |
'properties.substatus' : 1 |
}, {
|
partialFilterExpression : {
|
state : 'active', |
'properties.substatus' : 'scheduled' |
}
|
})
|
The following query results in a plan with a collection scan, even though the query can be satisfied by the partial index:
db.foo.find({
|
state : 'active', |
properties : {
|
$elemMatch : {
|
substatus : 'scheduled', |
date : { $lte : new ISODate() } |
}
|
}
|
})
|
However if an index hint is added to use the partial index the correct plan is chosen:
db.foo.find({
|
state : 'active', |
properties : {
|
$elemMatch : {
|
substatus : 'scheduled', |
date : {
|
$lte : new ISODate() |
}
|
}
|
}
|
}).hint({
|
state : 1, |
'properties.substatus' : 1 |
})
|
|
|
"stage" : "IXSCAN", |
"totalKeysExamined" : 1 |
"totalDocsExamined" : 1 |
Also note that if the top-level of the query is modified to include the 'properties.substatus' predicate this same plan is chosen:
db.foo.find({
|
state : 'active', |
'properties.substatus' : 'scheduled', |
properties : {
|
$elemMatch : {
|
substatus : 'scheduled', |
date : {
|
$lte : new ISODate() |
}
|
}
|
}
|
})
|
Should the winning plan be one that uses the partial index?
Attachments
Issue Links
- duplicates
-
SERVER-17853 Allow more complex expressions to be specified in partial index document filter
-
- Backlog
-