[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: |
|
||||||||||||
| 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:
The following query results in a plan with a collection scan, even though the query can be satisfied by the partial index:
However if an index hint is added to use the partial index the correct plan is chosen:
Also note that if the top-level of the query is modified to include the 'properties.substatus' predicate this same plan is chosen:
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, |