-
Type: Improvement
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
Fully Compatible
-
QO 2023-07-10, QO 2023-07-24
When an index includes all the fields that a query requires:
coll.createIndex({a: 1, b: 1}); coll.find({a: 5}, {_id: 0, b: 1});
Then we want to generate a plan that only touches the index, with no FETCH / Seek stage. But indexes don't distinguish null and missing, so we do need to check the collection for any index entry where b is null/missing. This is unsatisfying because maybe b is actually never null/missing, in which case we're doing many expensive Seeks unnecessarily.
One solution could be to construct a plan like this:
Union | | | Scan {a:1, b:1} where a == 5 and b != null | Fetch Scan {a:1, b:1} where a == 5 and b == null
That way we only fetch the documents where b is null/missing.
In this example we would expect the b == null predicate to produce tight index bounds, so if that predicate is always false then the Fetch Scan branch will do very little work.
- depends on
-
SERVER-69026 [CQF] Support for index OR-ing
- Closed
-
SERVER-78588 [CQF] Preserve the common projections from the both children of RIDUnionNode
- Closed