[SERVER-26405] Query planner does not consider $elemMatch criteria to select partial index Created: 30/Sep/16  Updated: 30/Sep/16  Resolved: 30/Sep/16

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.2.9, 3.3.15
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: John Murphy Assignee: David Storch
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-17853 Allow more complex expressions to be ... Backlog
Related
Operating System: ALL
Participants:

 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?



 Comments   
Comment by David Storch [ 30/Sep/16 ]

Hi john.murphy,

Thanks for reporting this issue. Partial indexes do not yet support $elemMatch predicates inside the partialFilterExpression. (The list of supported predicates is documented here: https://docs.mongodb.com/manual/core/index-partial/.) As a result, the analysis code which determines whether a query predicate is eligible to use a partial index also lacks any support for $elemMatch. If you're curious, the implementation of this analysis can be found here:

https://github.com/mongodb/mongo/blob/r3.2.9/src/mongo/db/matcher/expression_algo.cpp#L226

This is a function which, given two query predicate ASTs, returns whether the first is guaranteed to match a subset of the documents matched by the second. In order to be defensive, this will always return false if it encounters a predicate which it does not recognize, which prevents the planner from generating an incorrect plan using a partial index. In particular, it will return false if the first predicate is an $elemMatch—the limitation which you have described here.

SERVER-17853 tracks the work to add support for new predicate types for partial indexes, including $elemMatch. This ticket would be fixed as part of that work, so I am going to close as a duplicate. Please watch and vote for SERVER-17853. Let me know if you have any questions.

Best,
Dave

Generated at Thu Feb 08 04:12:01 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.