-
Type:
Improvement
-
Resolution: Works as Designed
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
The cardinality estimate for a FETCH that is an input to a NESTED_LOOP_JOIN_EMBEDDING is always zero, regardless of the actual estimate. This is not the case for FETCH that is an input to a hash join.
This causes the cardinality and the cost of all upstream stages to also be zero.
To reproduce:
const collSize = 1000; const documents = []; for (let i = 0; i < collSize; i++) { documents.push({ i_idx: i, }); } db.foo.drop(); db.foo.insertMany(documents); db.foo.createIndex({i_idx: 1}); db.foo.aggregate([{"$lookup":{"from":"foo","localField":"a","foreignField":"a","as":"a"}},{"$unwind":"$a"},{"$match":{"i_idx":{"$gt":500}}}]).explain().queryPlanner.winningPlan.queryPlan.inputStages[0];
Produces:
{
stage: 'FETCH',
planNodeId: 2,
costEstimate: 0,
cardinalityEstimate: 0, <<<=== HERE
estimatesMetadata: { ceSource: 'Code' },
nss: 'test.foo',
inputStage: {
stage: 'IXSCAN',
planNodeId: 1,
nss: 'test.foo',
keyPattern: { i_idx: 1 },
indexName: 'i_idx_1',
isMultiKey: false,
multiKeyPaths: { i_idx: [] },
isUnique: false,
isSparse: false,
isPartial: false,
indexVersion: 2,
direction: 'forward',
indexBounds: { i_idx: [ '(500, inf]' ] }
}
}