Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-71663

[SBE] GROUP + PROJECTION_COVERED + IXSCAN plans produce unused mkbson stage

    • Type: Icon: Task Task
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 6.3.0-rc0
    • Affects Version/s: None
    • Component/s: None
    • Query Execution
    • Fully Compatible
    • QE 2022-12-26
    • 100
    • 5

      Consider the following aggregation pipeline: 

      [{$match:{type: "Movie", mpaaRating: {$in: ["PG", "PG-13"]}, theatricalReleaseDate: {$ne: null}}}, {$count: "count"}] 

      When executing this in SBE, the winning plan is a GROUP -> PROJECTION_COVERED -> IXSCAN, and the generated plan is as follows:

      MongoDB Enterprise > db.products.aggregate([{$match:{type: "Movie", mpaaRating: {$in: ["PG", "PG-13"]}, theatricalReleaseDate: {$ne: null}}}, {$count: "count"}], {explain:true}).stages[0].$cursor.queryPlanner.winningPlan.slotBasedPlan.stages
      [2] mkobj s32 [_id = s30, count = s31] true false
      [2] group [s30] [s31 = sum(1)]
      [2] project [s30 = (null ?: null)]
      [1] project [s29 = newObj("type", s4, "mpaaRating", s5, "format", s6, "genre", s7, "theatricalReleaseDate", s8)]
      [1] branch {s27} [s4, s5, s6, s7, s8, s28]
      [s9, s10, s11, s12, s13, s19] [1] ixscan_generic s20 none s19 none [s9 = 0, s10 = 1, s11 = 2, s12 = 3, s13 = 4] @"1493b656-b437-4cbb-b21c-62af9cd74203" @"type_1_mpaaRating_1_format_1_genre_1_theatricalReleaseDate_1" true
      [s14, s15, s16, s17, s18, s21] [1] nlj [] [s22, s23]
          left
              [1] project [s22 = getField(s25, "l"), s23 = getField(s25, "h")]
              [1] unwind s25 s26 s24 false
              [1] limit 1
              [1] coscan
          right
              [1] ixseek s22 s23 none s21 none [s14 = 0, s15 = 1, s16 = 2, s17 = 3, s18 = 4] @"1493b656-b437-4cbb-b21c-62af9cd74203" @"type_1_mpaaRating_1_format_1_genre_1_theatricalReleaseDate_1" true 

      This plan produces a unused object in slot 29 that is never referenced in the group above:

      [1] project [s29 = newObj("type", s4, "mpaaRating", s5, "format", s6, "genre", s7, "theatricalReleaseDate", s8)]  

      This ticket tracks the work to investigate and fix this issue that is causing a performance regression.

            Assignee:
            rui.liu@mongodb.com Rui Liu
            Reporter:
            mihai.andrei@mongodb.com Mihai Andrei
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: