[SERVER-71663] [SBE] GROUP + PROJECTION_COVERED + IXSCAN plans produce unused mkbson stage Created: 28/Nov/22  Updated: 29/Oct/23  Resolved: 16/Dec/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.3.0-rc0

Type: Task Priority: Major - P3
Reporter: Mihai Andrei Assignee: Rui Liu
Resolution: Fixed Votes: 0
Labels: pm2697-m3
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Sprint: QE 2022-12-26
Participants:
Linked BF Score: 100
Story Points: 5

 Description   

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.



 Comments   
Comment by Githook User [ 16/Dec/22 ]

Author:

{'name': 'Rui Liu', 'email': 'lriuui0x0@gmail.com', 'username': 'lriuui0x0'}

Message: SERVER-71663 Do not materialize document when group node doesn't have dependency
Branch: master
https://github.com/mongodb/mongo/commit/bc4d929b684303f89db3fcb8e33b097fa646f553

Generated at Thu Feb 08 06:19:39 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.