Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-6997

Semijoin is changing the join ordering for some tpcds queries.




      TPCDS query 95 runs 50% slower with semi-join enabled compared to semi-join disabled at scale factor 100. It runs 100% slower at scale factor 1000. This issue was introduced with commit 71809ca6216d95540b2a41ce1ab2ebb742888671. DRILL-6798: Planner changes to support semi-join.

      with ws_wh as
       (select ws1.ws_order_number,ws1.ws_warehouse_sk wh1,ws2.ws_warehouse_sk wh2
       from web_sales ws1,web_sales ws2
       where ws1.ws_order_number = ws2.ws_order_number
       and ws1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
       [_LIMITA] select [_LIMITB]
       count(distinct ws_order_number) as "order count"
       ,sum(ws_ext_ship_cost) as "total shipping cost"
       ,sum(ws_net_profit) as "total net profit"
       web_sales ws1
       d_date between '[YEAR]-[MONTH]-01' and
       (cast('[YEAR]-[MONTH]-01' as date) + 60 days)
       and ws1.ws_ship_date_sk = d_date_sk
       and ws1.ws_ship_addr_sk = ca_address_sk
       and ca_state = '[STATE]'
       and ws1.ws_web_site_sk = web_site_sk
       and web_company_name = 'pri'
       and ws1.ws_order_number in (select ws_order_number
       from ws_wh)
       and ws1.ws_order_number in (select wr_order_number
       from web_returns,ws_wh
       where wr_order_number = ws_wh.ws_order_number)
       order by count(distinct ws_order_number)

      I have attached two profiles. 240abc6d-b816-5320-93b1-2a07d850e734 has semi-join enabled. 240aa5f8-24c4-e678-8d42-0fc06e5d2465 has semi-join disabled. Both are executed with commit id 6267185823c4c50ab31c029ee5b4d9df2fc94d03 and scale factor 100.

      The plan with semi-join enabled has moved the first hash join:

      and ws1.ws_order_number in (select ws_order_number
      from ws_wh)
      It used to be on the build side of the first HJ on the left hand side (04-05). It is now on the build side of the fourth HJ on the left hand side (01-13).

      The plan with semi-join enabled has a hash_partition_sender (operator 05-00) that takes 10 seconds to execute. But all the fragments take about the same amount of time.

      The plan with semi-join enabled has two HJ that process 1B rows while the plan with semi-joins disabled has one HJ that processes 1B rows.

      The plan with semi-join enabled has several senders and receivers that wait more than 10 seconds, (00-07, 01-07, 03-00, 04-00, 07-00, 08-00, 14-00, 17-00). When disabled, there is no operator waiting more than 10 seconds.


        1. 240aa5f8-24c4-e678-8d42-0fc06e5d2465.sys.drill
          3.15 MB
          Hanumath Rao Maduri
        2. 240abc6d-b816-5320-93b1-2a07d850e734.sys.drill
          3.23 MB
          Hanumath Rao Maduri

        Issue Links



              hanu.ncr Hanumath Rao Maduri
              hanu.ncr Hanumath Rao Maduri
              Aman Sinha Aman Sinha
              0 Vote for this issue
              2 Start watching this issue