You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Performance regression after adding support for SMJ with join filter #901
When running TPC-DS benchmarks against 100 GB data set I see a large regression in performance. For example, here are the timings for q72 before and after adding support for SMJ with join condition.
Adding support for SMJ with join condition means that more of the plan is likely running natively and the performance issue isn't necessarily directly related to SMJ.
select i_item_desc
,w_warehouse_name
,d1.d_week_seq
,sum(case when p_promo_sk is null then 1 else 0 end) no_promo
,sum(case when p_promo_sk is not null then 1 else 0 end) promo
,count(*) total_cnt
from catalog_sales
join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
join item on (i_item_sk = cs_item_sk)
join inventory on (cs_item_sk = inv_item_sk)
join warehouse on (w_warehouse_sk=inv_warehouse_sk)
join date_dim d2 on (inv_date_sk = d2.d_date_sk)
join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
left outer join promotion on (cs_promo_sk=p_promo_sk)
left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)
where d1.d_week_seq = d2.d_week_seq
and inv_quantity_on_hand < cs_quantity
and d3.d_date > d1.d_date + interval '5' day
and d1.d_year = 1999
group by i_item_desc,w_warehouse_name,d1.d_week_seq
order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
LIMIT 100
HashJoin - 9sec, SMJ - 20 sec
Having LEFT OUTER joins removed the results are still the same
Looks like a lot of time spent on arrow_select::take::take_impl which is specific for filtered join. take gets filtered matched indices from the joined data
Activity
andygrove commentedon Sep 2, 2024
Disabling sortMergeJoin via configs restores the original performance.
comphead commentedon Sep 3, 2024
Thanks @andygrove I'm planning to profile it.
Just for reference Q72 using INNER and LEFT join types
viirya commentedon Sep 3, 2024
Hmm, @andygrove Can you verify it again? In unit test, we have a test for CometSortMergeJoinExec metrics. It should have SQL metrics.
andygrove commentedon Sep 4, 2024
I ran again with latest from main (0033), and then with SMJ + join filter disabled manually (0034). Here are the event logs.
app-20240904131653-0033.gz
app-20240904132048-0034.gz
andygrove commentedon Sep 4, 2024
Here is a screenshot comparing the plans with SMJ+filter enabled on the left and disabled on the right.
viirya commentedon Sep 4, 2024
Hmm, I will run locally to see why the metrics are not there but they are in unit test.
comphead commentedon Sep 5, 2024
Im running slightly changed Q72 in DF
HashJoin - 9sec, SMJ - 20 sec
Having LEFT OUTER joins removed the results are still the same
HashJoin - 8.5 SMJ - 19 sec
I'll build a flamegraph for SMJ soon
comphead commentedon Sep 6, 2024
Looks like a lot of time spent on
arrow_select::take::take_implwhich is specific for filtered join.takegets filtered matched indices from the joined dataviirya commentedon Sep 9, 2024
@andygrove I just ran a simple sort merge join query locally on Spark 4.0 + Comet built from latest
mainbranch:Looks like the metrics are shown for CometSort / CometSortMergeJoin.
comphead commentedon Sep 25, 2025
This is still relevant checked on TPCH Q21
SMJ without filter
with filtered SMJ
Sort Merge Join: Reduce batch concatenation, use `BatchCoalescer`, ne…