Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney closed issue #16364: Optimize `NestedLoopJoinExec` Memory Usage URL: https://github.com/apache/datafusion/issues/16364 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2998537931 > I plan to implement "Limit intermediate_batch Size During Filtering" and "Yield Partial Batches on Demand" first, and then I will work on "Process the Cartesian Product Incrementally". https://github.com/apache/datafusion/pull/16443 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2975520489 > By joining only one left row with the right batch at a time? However when `right_side_ordered == True` we need maintains right_side order. https://github.com/apache/datafusion/blob/69dfe6c499d39563f4e6d9835fcdf3793f7d98c8/datafusion/physical-plan/src/joins/nested_loop_join.rs#L727-L728 Perhaps we can "Process the Cartesian Product Incrementally" by generating Cartesian Product of `(left_batch, right_batch[i, i+N])` at a time I plan to implement "Limit intermediate_batch Size During Filtering" and "Yield Partial Batches on Demand" first, and then I will work on "Process the Cartesian Product Incrementally". cc @2010YOUY01 @xudong963 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
2010YOUY01 commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2969378247 > > limiting the the intermediate result to ~1 batch size is enough to keep the performance. > > Do you mean we should also limit num_row of [`left_side, right_side`](https://github.com/apache/datafusion/blob/69dfe6c499d39563f4e6d9835fcdf3793f7d98c8/datafusion/physical-plan/src/joins/nested_loop_join.rs#L986) to 1 batch size ? By joining only one left row with the right batch at a time? > > This is the easiest way to implement. This is the only limitation we need apply > > I thought that increasing the size of the intermediate result for (left_side, right_side) might lead to better performance. ~But then I realized that if we truly want better performance, we should avoid using nlj~ We can implement it first then do some benchmarking. [@2010YOUY01](https://github.com/2010YOUY01) Adding micro-benches for NLJ sounds great, we can enumerate scenarios like small build side + large probe side + less selective join filter large build side + small probe side + selective join filter ... And later decide the what to implement according to bench results. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2968778612 > limiting the the intermediate result to ~1 batch size is enough to keep the performance. Do you mean we should also limit num_row of [`left_side, right_side`](https://github.com/apache/datafusion/blob/69dfe6c499d39563f4e6d9835fcdf3793f7d98c8/datafusion/physical-plan/src/joins/nested_loop_join.rs#L986) to 1 batch size ? By joining only one left row with the right batch at a time? This is the easiest way to implement. This is the only limitation we need apply I thought that increasing the size of the intermediate result for (left_side, right_side) might lead to better performance. ~~But then I realized that if we truly want better performance, we should avoid using nlj~~ We can implement it first then do some benchmarking. @2010YOUY01 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2967225093 > limiting the the intermediate result to ~1 batch size is enough to keep the performance. Do you mean we should also limit num_row of [`left_side, right_side`](https://github.com/apache/datafusion/blob/69dfe6c499d39563f4e6d9835fcdf3793f7d98c8/datafusion/physical-plan/src/joins/nested_loop_join.rs#L986) to 1 batch size ? By joining only one left row with the right batch at a time? This is the easiest way to implement. This is the only limitation we need apply I thought that increasing the size of the intermediate result for (left_side, right_side) might lead to better performance. But then I realized that if we truly want better performance, we should **avoid** using nlj 😂 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
2010YOUY01 commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2964936843 Thanks for working on it. `3.i.` is definitely not efficient for memory usage. 🤦🏼 Perhaps limiting the the intermediate result to ~1 batch size is enough to keep the performance. Also it's a good idea it ignore right* joins in the first patch, but to avoid regression, it seems you can swap the join children during planning and always make it left* joins? 🤔 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
jonathanc-n commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2964570048 Yeah this was much needed thanks for bringing this up @UBarney -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
Re: [I] Optimize `NestedLoopJoinExec` Memory Usage [datafusion]
UBarney commented on issue #16364: URL: https://github.com/apache/datafusion/issues/16364#issuecomment-2961161122 take -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] - To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
