-
Type:
Improvement
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
This query is not eligible for join optimization:
db.foo.insert({a:1});
db.bar.insert({a:1});
db.foo.aggregate([{"$lookup":{"from":"bar","localField":"x","foreignField":"y","as":"bar"}},{"$unwind":"$bar"},{"$match":{"bar.b":0}}]).explain();
stages: [
{
'$cursor': {
queryPlanner: {
namespace: 'tpch.foo',
parsedQuery: {},
indexFilterSet: false,
queryHash: '4E45B701',
planCacheShapeHash: '4E45B701',
planCacheKey: '36FF8BFB',
optimizationTimeMillis: 0,
cursorType: 'regular',
maxIndexedOrSolutionsReached: false,
maxIndexedAndSolutionsReached: false,
maxScansToExplodeReached: false,
prunedSimilarIndexes: false,
winningPlan: {
isCached: false,
usedJoinOptimization: false,
stage: 'COLLSCAN',
nss: 'tpch.foo',
direction: 'forward'
},
rejectedPlans: []
}
}
},
{
'$lookup': {
from: 'bar',
as: 'bar',
localField: 'x',
foreignField: 'y',
let: {},
pipeline: [
{ '$match': { b: { '$eq': 0 } } }
],
unwinding: { preserveNullAndEmptyArrays: false }
}
}
],
But if the predicate is pushed down manually, it is:
db.foo.aggregate([{"$lookup":{"from":"bar","localField":"x","foreignField":"y","as":"bar", pipeline: [{$match: {b: 0}}]}},{"$unwind":"$bar"}]).explain();
explain() with join optimization disabled indicates that the predicate is correctly pushed into the subpipeline.
- duplicates
-
SERVER-116033 Support absorbed single-table filters when constructing join graph
-
- Needs Scheduling
-
- is related to
-
SERVER-116033 Support absorbed single-table filters when constructing join graph
-
- Needs Scheduling
-