[SERVER-25785] Sparse index defined with partialFilterExpression is not used Created: 24/Aug/16 Updated: 25/Aug/16 Resolved: 25/Aug/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | 3.2.8 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Critical - P2 |
| Reporter: | Xavier Del Castillo | Assignee: | David Storch |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Operating System: | ALL |
| Steps To Reproduce: | 1. Define an index which has a partialFilterExpression that checks the absence of a property with. {{ field: null }} Expected: The index should be used at all times without hinting. |
| Participants: |
| Description |
|
I have the following index defined on my collection:
The winning plan for a simple query db.messages.find({ userId: 1 }) is:
Hinting the index to the query makes it use it as expected:
I've tried defining partialFilterExpression using $type or $gt but it won't work. deletedAt is a Date field that only exists for soft deleted documents documents |
| Comments |
| Comment by David Storch [ 25/Aug/16 ] |
|
You're welcome, xdc. |
| Comment by Xavier Del Castillo [ 25/Aug/16 ] |
|
Makes complete sense. Thanks for your time explaining this! |
| Comment by David Storch [ 25/Aug/16 ] |
|
Hi xdc, Thanks for raising this ticket. As far as I can tell, this behavior is working as designed. In general, a partial index cannot be used to answer a query unless the system can prove that all matching documents will be included in the index. Suppose, for example, that you have a partial index which only includes last names beginning with the letter "S". If you issue a query that asks for last names beginning with either "S" or "T", you can't use the index since you'd end up missing all the last names that begin with "T". The same principle is at play in your example. You have defined a partial index which only indexes documents where deletedAt is equal to null. Then you deliver a query asking for documents where userId is equal to 1. How can we prove that all documents with userId equal to 1 also have deletedAt equal to null? We can't! Therefore, the planner removes the partial index from consideration, and you end up with a COLLSCAN plan. I'm closing this ticket as "Works as Designed". Please re-open if you have any concerns about this analysis. Best, |
| Comment by Xavier Del Castillo [ 24/Aug/16 ] |
|
Apologies for the incorrect formatting. I'm unable to edit the original description to fix it. |