Optimized and MapJoin friendly Q2 (#3950)

2 files changed, 197 insertions, 65 deletions

diff --git a/ydb/library/benchmarks/queries/tpch/ydb/q2.sql b/ydb/library/benchmarks/queries/tpch/ydb/q2.sql
index a06b4711be..6824d9ffee 100644
--- a/ydb/library/benchmarks/queries/tpch/ydb/q2.sql
+++ b/ydb/library/benchmarks/queries/tpch/ydb/q2.sql
@@ -1,67 +1,133 @@
 -- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
 -- using 1680793381 as a seed to the RNG
 
-$r = (select r_regionkey from
+$r = (
+select
+    r_regionkey
+from 
     `{path}region`
-where r_name='AMERICA');
-
-$j1 = (select n_name,n_nationkey
-    from `{path}nation` as n
-    join $r as r on
-    n.n_regionkey = r.r_regionkey);
+where
+    r_name='AMERICA'
+);
 
-$j2 = (select s_acctbal,s_name,s_address,s_phone,s_comment,n_name,s_suppkey
-    from `{path}supplier` as s
-    join $j1 as j on
-    s.s_nationkey = j.n_nationkey
+$n = (
+select
+    n_name,
+    n_nationkey
+from
+    `{path}nation` as n 
+left semi join
+    $r as r
+on 
+    n.n_regionkey = r.r_regionkey
 );
 
-$j3 = (select ps_partkey,ps_supplycost,s_acctbal,s_name,s_address,s_phone,s_comment,n_name
-    from `{path}partsupp` as ps
-    join $j2 as j on
-    ps.ps_suppkey = j.s_suppkey
+$s1 = (
+select
+    s_suppkey
+from
+    `{path}supplier` as s
+left semi join
+    $n as n
+on
+    s.s_nationkey = n.n_nationkey
 );
 
-$min_ps_supplycost = (select min(ps_supplycost) as min_ps_supplycost,ps_partkey
-    from $j3
-    group by ps_partkey
+$min_ps_supplycost = (
+select
+    min(ps_supplycost) as min_ps_supplycost,
+    ps.ps_partkey as ps_partkey
+from
+    `{path}partsupp` as ps
+left semi join
+    $s1 as s
+on
+    ps.ps_suppkey = s.s_suppkey
+group by
+    ps.ps_partkey
 );
 
-$p = (select p_partkey,p_mfgr
-    from `{path}part`
-    where
+$p = (
+select
+    p_partkey,
+    p_mfgr
+from
+    `{path}part`
+where
     p_size = 10
     and p_type like '%COPPER'
 );
 
-$j4 = (select s_acctbal,
+$ps = (
+select
+    ps.ps_partkey as ps_partkey,
+    p.p_mfgr as p_mfgr,
+    ps.ps_supplycost as ps_supplycost,
+    ps.ps_suppkey as ps_suppkey
+from
+    `{path}partsupp` as ps
+join
+    $p as p
+on
+    p.p_partkey = ps.ps_partkey
+);
+
+$s2 = (
+select
+    s_acctbal,
     s_name,
-    n_name,
-    p_partkey,
+    s_address,
+    s_phone,
+    s_comment,
+    s_suppkey,
+    n_name
+from
+    `{path}supplier` as s
+join
+    $n as n
+on
+    s.s_nationkey = n.n_nationkey
+);
+
+$jp =(
+select
+    ps_partkey,
+    ps_supplycost,
     p_mfgr,
+    s_acctbal,
+    s_name,
     s_address,
     s_phone,
-    s_comment
-    from $p as p
-    join $j3 as j on p.p_partkey = j.ps_partkey
-    join $min_ps_supplycost as m on p.p_partkey = m.ps_partkey
-    where min_ps_supplycost=ps_supplycost
+    s_comment,
+    n_name
+from
+    $ps as ps
+join    
+    $s2 as s
+on
+    ps.ps_suppkey = s.s_suppkey  
 );
 
 select
     s_acctbal,
     s_name,
     n_name,
-    p_partkey,
+    jp.ps_partkey as p_partkey,
     p_mfgr,
     s_address,
     s_phone,
     s_comment
-from $j4
+from
+    $jp as jp
+join
+    $min_ps_supplycost as m
+on
+    jp.ps_partkey = m.ps_partkey
+where
+    min_ps_supplycost = ps_supplycost
 order by
     s_acctbal desc,
     n_name,
     s_name,
     p_partkey
 limit 100;
-
diff --git a/ydb/library/benchmarks/queries/tpch/yql/q2.sql b/ydb/library/benchmarks/queries/tpch/yql/q2.sql
index 5e228fd84a..bb66df6776 100644
--- a/ydb/library/benchmarks/queries/tpch/yql/q2.sql
+++ b/ydb/library/benchmarks/queries/tpch/yql/q2.sql
@@ -3,67 +3,133 @@
 -- TPC-H/TPC-R Minimum Cost Supplier Query (Q2)
 -- using 1680793381 as a seed to the RNG
 
-$r = (select r_regionkey from 
+$r = (
+select
+    r_regionkey
+from 
     {{region}}
-where r_name='AMERICA');
-
-$j1 = (select n_name,n_nationkey
-    from {{nation}} as n 
-    join $r as r on 
-    n.n_regionkey = r.r_regionkey);
+where
+    r_name='AMERICA'
+);
 
-$j2 = (select s_acctbal,s_name,s_address,s_phone,s_comment,n_name,s_suppkey
-    from {{supplier}} as s
-    join $j1 as j on
-    s.s_nationkey = j.n_nationkey
+$n = (
+select
+    n_name,
+    n_nationkey
+from
+    {{nation}} as n 
+left semi join
+    $r as r
+on 
+    n.n_regionkey = r.r_regionkey
 );
 
-$j3 = (select ps_partkey,ps_supplycost,s_acctbal,s_name,s_address,s_phone,s_comment,n_name
-    from {{partsupp}} as ps
-    join $j2 as j on
-    ps.ps_suppkey = j.s_suppkey
+$s1 = (
+select
+    s_suppkey
+from
+    {{supplier}} as s
+left semi join
+    $n as n
+on
+    s.s_nationkey = n.n_nationkey
 );
 
-$min_ps_supplycost = (select min(ps_supplycost) as min_ps_supplycost,ps_partkey
-    from $j3
-    group by ps_partkey
+$min_ps_supplycost = (
+select
+    min(ps_supplycost) as min_ps_supplycost,
+    ps.ps_partkey as ps_partkey
+from
+    {{partsupp}} as ps
+left semi join
+    $s1 as s
+on
+    ps.ps_suppkey = s.s_suppkey
+group by
+    ps.ps_partkey
 );
 
-$p = (select p_partkey,p_mfgr
-    from {{part}}
-    where
+$p = (
+select
+    p_partkey,
+    p_mfgr
+from
+    {{part}}
+where
     p_size = 10
     and p_type like '%COPPER'
 );
 
-$j4 = (select s_acctbal,
+$ps = (
+select
+    ps.ps_partkey as ps_partkey,
+    p.p_mfgr as p_mfgr,
+    ps.ps_supplycost as ps_supplycost,
+    ps.ps_suppkey as ps_suppkey
+from
+    {{partsupp}} as ps
+join
+    $p as p
+on
+    p.p_partkey = ps.ps_partkey
+);
+
+$s2 = (
+select
+    s_acctbal,
     s_name,
-    n_name,
-    p_partkey,
+    s_address,
+    s_phone,
+    s_comment,
+    s_suppkey,
+    n_name
+from
+    {{supplier}} as s
+join
+    $n as n
+on
+    s.s_nationkey = n.n_nationkey
+);
+
+$jp =(
+select
+    ps_partkey,
+    ps_supplycost,
     p_mfgr,
+    s_acctbal,
+    s_name,
     s_address,
     s_phone,
-    s_comment
-    from $p as p
-    join $j3 as j on p.p_partkey = j.ps_partkey
-    join $min_ps_supplycost as m on p.p_partkey = m.ps_partkey
-    where min_ps_supplycost=ps_supplycost
+    s_comment,
+    n_name
+from
+    $ps as ps
+join    
+    $s2 as s
+on
+    ps.ps_suppkey = s.s_suppkey  
 );
 
-select 
+select
     s_acctbal,
     s_name,
     n_name,
-    p_partkey,
+    jp.ps_partkey as p_partkey,
     p_mfgr,
     s_address,
     s_phone,
     s_comment
-from $j4 
+from
+    $jp as jp
+join
+    $min_ps_supplycost as m
+on
+    jp.ps_partkey = m.ps_partkey
+where
+    min_ps_supplycost = ps_supplycost
 order by
     s_acctbal desc,
     n_name,
     s_name,
     p_partkey
 limit 100;
-