Join optimization: top-level $match that can be pushed into subpipeline disables join optimization

XMLWordPrintableJSON

    • 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.

            Assignee:
            Unassigned
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: