[SERVER-86162] Simple $group runs much slower in SBE than Classic engine Created: 02/Feb/24  Updated: 06/Feb/24

Status: Backlog
Project: Core Server
Component/s: None
Affects Version/s: 7.3.0-rc1
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Kevin Cherkauer Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File genny.UnwindGroup.numbers     File genny.UnwindGroup.sh    
Assigned Teams:
Query Execution
Participants:

 Description   

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.


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