-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 3.2.9, 3.3.15
-
Component/s: Querying
-
None
-
ALL
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?
- duplicates
-
SERVER-17853 Allow more complex expressions to be specified in partial index document filter
-
- Backlog
-