[SERVER-69351] [CQF] Use a union plan avoid fetching non-null indexed fields Created: 31/Aug/22  Updated: 29/Oct/23  Resolved: 13/Jul/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.1.0-rc0

Type: Improvement Priority: Major - P3
Reporter: David Percy Assignee: Chi-I Huang
Resolution: Fixed Votes: 0
Labels: bonsai-onboarding, optimization
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-69026 [CQF] Support for index OR-ing Closed
depends on SERVER-78588 [CQF] Preserve the common projections... Closed
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Sprint: QO 2023-07-10, QO 2023-07-24
Participants:

 Description   

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.



 Comments   
Comment by Githook User [ 13/Jul/23 ]

Author:

{'name': 'Chi-I Huang', 'email': 'chii.huang@mongodb.com', 'username': 'chiihuang'}

Message: SERVER-69351 Reduce fetching by splitting interval requirement with null
Branch: master
https://github.com/mongodb/mongo/commit/1d855848f5cd56bda645c8acca32e97dbfc1696f

Generated at Thu Feb 08 06:13:14 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.