[SERVER-74293] Improve spilling algorithm for HashLookupStage Created: 22/Feb/23  Updated: 14/Mar/23

Status: Open
Project: Core Server
Component/s: Query Execution
Affects Version/s: None
Fix Version/s: None

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

Issue Links:
Related
is related to SERVER-70395 Slot-Based Engine too aggressively us... Closed
Assigned Teams:
Query Execution
Participants:
Story Points: 0

 Description   

It looks to me like HashLookupStage suffers from a similar performance problem as was corrected for HashAggStage in related ticket SERVER-70395. That is, the data that is spilled either to HashLookupStage::_recordStoreHt and HashLookupStage::_recordStoreBuf may be repeatedly deserialized when consuming the input from the outer child.

My thinking after discussing with anna.wawrzyniak@mongodb.com is that we should probably choose to do nothing about this problem right now, and instead just close this ticket as "Won't Do". I'm filing the ticket so that we have this conversation and make an explicit decision about it as a team. The primary reason to avoid scheduling this improvement is that we currently only use HashLookupStage to support the pushdown of $lookup to SBE. There are heuristics in place so that we only choose a hash-based plan for $lookup when the data size is sufficiently small. This makes spilling possible but extremely unlikely. For this reason, we probably don't want to invest lots of engineering effort into spilling performance for hash-based $lookup plans in SBE right now. Furthermore, the regular HashJoinStage in SBE does not support spilling yet. It would be wise to design and implement a good spilling algorithm for regular hash join before we try to implement something similar for the special case of HashLookupStage.



 Comments   
Comment by David Storch [ 28/Feb/23 ]

mihai.andrei@mongodb.com I'm sticking this in m4 of the SBE perf project so that we can review it as a team. I think we should probably close it as "Won't Do" as I suggested above, but I wanted to run it by the SBE perf team first.

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