[SERVER-44931] Covered queries do not work when $ne/$nin operators are present Created: 03/Dec/19 Updated: 19/Dec/19 Resolved: 19/Dec/19 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Index Maintenance |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Forest Trimble | Assignee: | Jacob Evans |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | qopt-team | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Operating System: | ALL | ||||||||
| Steps To Reproduce: | You can create a very simple database with an index on only a single field and query on $ne on that field, your query will need to look at every document that matches the query rather than just returning the index. The more complicated example above was purely to illustrate that there is some utility in being able to leverage covered queries for this type of thing. |
||||||||
| Sprint: | Query 2019-12-30 | ||||||||
| Participants: | |||||||||
| Description |
|
For queries that have $ne/$nin operators, they seem to be unable to use covered queries. This is seemingly not particularly useful for indexes that apply only to that field, but if the $ne is a compound index, we could potentially be dropping a dramatically large amount of data even with the $ne filter. For example, consider this query:
It seems like the fact that there is a FETCH stage in this plan (even though it will never eliminate any documents as the index scan prevents it) prevents us from being able to use a covered query (`published` is part of the index, so should not require examining any documents) |
| Comments |
| Comment by Forest Trimble [ 19/Dec/19 ] |
|
Brilliant, thank you! |
| Comment by Jacob Evans [ 19/Dec/19 ] |
|
Hi Forest Trimble, This was fixed in |
| Comment by Forest Trimble [ 18/Dec/19 ] |
|
I ran this on 4.0.13 |
| Comment by Jacob Evans [ 13/Dec/19 ] |
|
Hello, I was unable to reproduce this locally. The provided query produces an IXSCAN followed by a PROJECTION_COVERED on master. Forest Trimble can you provide the version that you witnessed this behavior on? Carl can you provide details of how you recreated a similar behavior? |
| Comment by Carl Champain (Inactive) [ 04/Dec/19 ] |
|
Thanks for taking the time to submit this report. Kind regards, |
| Comment by Forest Trimble [ 03/Dec/19 ] |
|
seems like this issue is related: https://jira.mongodb.org/browse/SERVER-29300 - i just wanted to point out the utility of covered queries in this type of thing. |