Skip to content

Performance regression after adding support for SMJ with join filter #901

Closed
apache/datafusion
#18875

Description

@andygrove

Describe the bug

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.

before

    "72": [
        22.729433059692383,
        18.11495876312256,
        17.545786142349243
    ]

after

    "72": [
        38.576566219329834,
        35.433213233947754,
        35.262585401535034
    ]

A secondary issue is that I do not see metrics for CometSort / CometSortMergeJoin.

Screenshot from 2024-09-01 10-48-04

Steps to reproduce

No response

Expected behavior

No response

Additional context

No response

Activity

  1. andygrove commented on Sep 2, 2024

    @andygrove
    MemberAuthor

    Disabling sortMergeJoin via configs restores the original performance.

  2. comphead commented on Sep 3, 2024

    @comphead
    Contributor

    Thanks @andygrove I'm planning to profile it.
    Just for reference Q72 using INNER and LEFT join types

  3. viirya commented on Sep 3, 2024

    @viirya
    Member

    A secondary issue is that I do not see metrics for CometSort / CometSortMergeJoin.

    Hmm, @andygrove Can you verify it again? In unit test, we have a test for CometSortMergeJoinExec metrics. It should have SQL metrics.

  4. andygrove commented on Sep 4, 2024

    @andygrove
    MemberAuthor

    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

  5. andygrove commented on Sep 4, 2024

    @andygrove
    MemberAuthor

    Here is a screenshot comparing the plans with SMJ+filter enabled on the left and disabled on the right.

    Screenshot from 2024-09-04 13-34-37

  6. viirya commented on Sep 4, 2024

    @viirya
    Member

    Hmm, I will run locally to see why the metrics are not there but they are in unit test.

  7. comphead commented on Sep 5, 2024

    @comphead
    Contributor

    Im running slightly changed Q72 in DF

    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

    HashJoin - 8.5 SMJ - 19 sec

    I'll build a flamegraph for SMJ soon

  8. comphead commented on Sep 6, 2024

    @comphead
    Contributor

    smj

    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

  9. viirya commented on Sep 9, 2024

    @viirya
    Member

    A secondary issue is that I do not see metrics for CometSort / CometSortMergeJoin.

    Hmm, @andygrove Can you verify it again? In unit test, we have a test for CometSortMergeJoinExec metrics. It should have SQL metrics.

    @andygrove I just ran a simple sort merge join query locally on Spark 4.0 + Comet built from latest main branch:

    Screenshot 2024-09-09 at 1 28 20 PM

    Looks like the metrics are shown for CometSort / CometSortMergeJoin.

  10. added this to the 0.3.0 milestone on Sep 16, 2024
  11. modified the milestones: 0.3.0, 0.4.0 on Sep 24, 2024
  12. modified the milestones: 0.4.0, 0.5.0 on Nov 11, 2024
  13. modified the milestones: 0.5.0, 0.6.0 on Jan 13, 2025
  14. modified the milestones: 0.6.0, 0.7.0, 0.8.0 on Feb 3, 2025
  15. removed this from the 0.8.0 milestone on Apr 7, 2025
  16. comphead commented on Sep 25, 2025

    @comphead
    Contributor

    This is still relevant checked on TPCH Q21
    SMJ without filter

    Query 21 took 121.45100903511047 seconds
    

    with filtered SMJ

    Query 21 took 557.8474290370941 seconds
    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions