[SERVER-49845] allow indices to optionally cover missing values Created: 23/Jul/20 Updated: 28/Jul/20 Resolved: 28/Jul/20 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | New Feature | Priority: | Minor - P4 |
| Reporter: | Jeremy Todd | Assignee: | Asya Kamsky |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | qopt-team | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Participants: | |||||||||
| Description |
|
I often need to run queries of the form {a: {$ne:null}}, i.e. find documents in which the field "a" is not missing and not equal to null. As I understand it, an index on "a" will not cover this query because the index will not include documents where "a" is missing. In these cases I find myself forced to always include a value for "a". This isn't hard to do - generally there's always some value I can reserve for this purpose (often "null" of course, but sometimes another value if "null" doesn't mean the same thing as a missing value). It seems to me that mongo could do this for me transparently (by reserving a value to indicate "missing" and including those documents in the index). I completely understand that minimizing index size is important, and this wouldn't be appropriate in many contexts, and certainly not as a default. Nevertheless, this comes up over and over again (I've been running an expansive production environment on mongo for many years). So I think it would make an incredibly useful option when creating an index. Apologies if I'm missing something about why this isn't feasible (or perhaps feasible but not simple) to implement, or why it's not a good idea. I tried to cover this in a bit more detail on stack overflow before submitting this request: https://stackoverflow.com/questions/63003917
|
| Comments |
| Comment by Asya Kamsky [ 28/Jul/20 ] |
|
I'm closing this as fundamentally being the same as SERVER-18861 - there are some cases where null equality still causes a FETCH and a less efficient plan to be selected and you may have been looking at one of them. |
| Comment by Jeremy Todd [ 28/Jul/20 ] |
|
Understood - sorry for the bogus feature request! I must have been thinking about queries of this form not leading to COUNT_SCAN operations, discussed here: https://mongodb-user.narkive.com/MKpJkCNh/count-scan-vs-ixscan-when-query-includes-null which led to feature request SERVER-18861. Those are all issues separate from this feature request though.
|
| Comment by Asya Kamsky [ 28/Jul/20 ] |
|
|
| Comment by Asya Kamsky [ 28/Jul/20 ] |
|
> I often need to run queries of the form {a: {$ne:null}}, i.e. find documents in which the field "a" is not missing and not equal to null. As I understand it, an index on "a" will not cover this query because the index will not include documents where "a" is missing. This understanding is incorrect - an index will cover this. |
| Comment by Jonathan Streets (Inactive) [ 24/Jul/20 ] |
|
hi Jeremy, |