I'm answering my own question in the hope that it may help someone else. I am using mongodb (3.4), running a count() on an indexed string field, in a collection of 10m documents. I am looking for non-empty strings. Here are some performance metrics, where slow == collection_scan and fast == index_scan:
+----------------+-----------+
| Constraint |Performance|
+----------------+-----------+
| $exists(false) | slow |
| $exists(true) | slow |
| $ne: '' | slow |
| $eq: '' | fast |
| $eq: null | slow |
+----------------+-----------+
I think I mostly understand what is going on, namely:
1. $exists(false)
Non-existing fields will not enter the index, therefore mongodb must perform a collection scan in order to gather all documents for the count().
2. $exists(true)
This is the one that got me. I thought it would be fast, but I was wrong. I think this is because if a field exists, and it's value is set to undefined, $exists() returns true. I can only assume that fields set to undefined do not enter the index either, and therefore again, mongodb must perform a collection scan in order to gather all documents for the count().
3. $ne: ''
I guess it's the same as $exists(false)
4. $eq: ''
This is a genuine string value, so it must exist in the index, therefore fast.
4. $eq: null
This is the other one that got me. This document helped a lot (https://jira.mongodb.org/browse/SERVER-18653). In short, with the 2.6 release (https://docs.mongodb.com/manual/release-notes/2.6-compatibility/#null-comparison-queries):
null equality queries (i.e. field: null ) now match fields with values undefined
This query is slow. I can only assume that undefined fields are not indexed, and since null matches both, again, mongodb must perform a collection scan in order to gather all documents for the count().
So, what do?
