-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
3
-
TBD
-
None
-
None
-
None
-
None
-
None
-
None
This ticket is spun off from related ticket SERVER-105057. Consider the following toy example:
db.c.drop(); assert.commandWorked(db.c.insert([ {_id: 1, a: 1}, {_id: 2, a: null}, {_id: 3, a: undefined}, {_id: 4}, ])); assert.commandWorked(db.c.createIndex({a: 1})); let explain = db.c.find({$expr: {$eq: ["$a", "$$REMOVE"]}}).explain("executionStats"); printjson(explain); let results = db.c.find({$expr: {$eq: ["$a", "$$REMOVE"]}}).toArray(); printjson(results);
If you run this, you will see from the explain that the system uses a COLLSCAN plan. Even without changing the index format to distinguish between null and missing values (SERVER-12869), it is possible to use an index scan for this query. The index scan would find any null, missing, and undefined values. Then, a residual filter must be applied to discard the literal nulls. (Currently the query matches undefined in addition to missing, which is arguably incorrect. Since the undefined type has long been deprecated and is little used, I'm not sure that we should worry about this detail.)
A predicate like this is typically not written directly by the application, since it is more appropriate to use something like {$exists:false} or {$eq:null}. However, this is still an important optimization since it can matter when this query occurs on the inner side of a $lookup. See related ticket SERVER-105057 for a detailed description of how this can happen, as well as a draft fix implemented by our very own bernard.gorman@mongodb.com.
- is related to
-
SERVER-12869 Index null values and missing values differently
-
- Backlog
-
-
SERVER-105057 SQL-style NULL semantics for equijoins are difficult to emulate with $lookup
-
- Needs Scheduling
-