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.
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
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.