-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Critical - P2
-
Affects Version/s: 9.0.0-rc2
-
Component/s: None
-
Query Optimization
-
Fully Compatible
-
ALL
-
v9.0
-
None
-
None
-
None
-
None
-
None
-
None
-
None
Prior to SERVER-123100, we used isIdHackEligibleQuery to check if a query is IDHack-eligible in QueryPlannerParams::fillOutMainCollectionPlannerParams.
isIdHackEligibleQuery checks against the current query whereas the new code uses the cached value from getExpCtx()->isIdHackQuery().
This causes an issue for aggregations with a $lookup which may lead to an IDHack eligible subquery:
{$lookup: {from: "coll", localField: "local", foreignField: "_id", as: "results"}}
Here, the predicate on _id becomes $match: {_id: <value of local>}. For $lookup, the sub-query execution shares one ExpressionContext, and the predicate is instantiated with whatever value localField has.
Therefore, the $lookup could result in queries which are IDHack eligible and IDHack ineligible. The approach in SERVER-123100 leads to us setting isIdHackQuery when the query is eligible, but we do not unset it when the query is not eligible, which is the case when localField is null or missing.
This leads to us making an incorrect decision later. We skip the planning due to isIdHackQuery=true, but then we cannot use an index for the null case, so we are left with COLLSCAN.
Symptoms
Queries with $lookup with foreignField: _id and localField which can be null or missing regress to collection scans.
- is caused by
-
SERVER-123100 Remove isIdHackEligibleQuery() in favor of ExpCtx::isIdHackQuery() checks
-
- Closed
-
- is related to
-
SERVER-129528 Implement SbeSingleDocumentLookupExecutor for local _id point lookups
-
- Closed
-
-
SERVER-130061 Validate user-facing query settings and enable featureFlagAllowUserFacingQuerySettings
-
- Closed
-
-
SERVER-131542 Skip query knob configuration materialization on IDHACK caching fast path
-
- Closed
-