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

Simple $group runs much slower in SBE than Classic engine

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 7.3.0-rc1
    • Component/s: None
    • None
    • Query Execution

      Experiment:

      Collection0 contains 10,000 docs of the form:

      > db.Collection0.find().limit(3)
      { "_id" : ObjectId("65badbe9a940789d3800c3c1"), "arr" : [ 5, 6, 7, 0, 4 ] }
      { "_id" : ObjectId("65badbe9a940789d3800c3c2"), "arr" : [ 8, 4, 3, 7, 0 ] }
      { "_id" : ObjectId("65badbe9a940789d3800c3c3"), "arr" : [ 4, 6, 5, 5, 0 ] }
      

      Query:

      db.Collection0.aggregate([
         {"$unwind": {"path": "$arr"}},
         {"$group": {"_id": "$arr"}}
      ]);
      

      Per explain execution stats, this runs in

      • 58 msec - Classic
      • 642 msec (11x as long) - SBE

      Individual stage time estimates (e) and total actual times from db.Collection0.explain('executionStats'), all in milliseconds:

      Classic millisecs per stage

      Cumul Indiv Stage
      58   TOTAL (actual)
      52e 5e $group
      47e 7e $unwind
      40e 40e $cursor

      SBE millisecs per stage

      Cumul Indiv Stage
      642   TOTAL (actual)
      640e 0e project
      640e 540e group
      100e 10e project
      90e 41e project
      49e 0e unwind
      49e 0e project
      49e 30e project
      19e 19e scan

      I will add Genny-based repro instructions in a comment.

        1. genny.UnwindGroup.numbers
          199 kB
        2. genny.UnwindGroup.sh
          0.8 kB

            Assignee:
            backlog-query-execution [DO NOT USE] Backlog - Query Execution
            Reporter:
            kevin.cherkauer@mongodb.com Kevin Cherkauer
            Votes:
            0 Vote for this issue
            Watchers:
            11 Start watching this issue

              Created:
              Updated: