Optimized and MapJoin friendly Q3 (#3951)

2 files changed, 48 insertions, 36 deletions

diff --git a/ydb/library/benchmarks/queries/tpch/ydb/q3.sql b/ydb/library/benchmarks/queries/tpch/ydb/q3.sql
index 5da862c45c..dd66e153eb 100644
--- a/ydb/library/benchmarks/queries/tpch/ydb/q3.sql
+++ b/ydb/library/benchmarks/queries/tpch/ydb/q3.sql
@@ -2,35 +2,45 @@
 -- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
 -- using 1680793381 as a seed to the RNG
 
-$join1 = (
+$c = (
 select
-    c.c_mktsegment as c_mktsegment,
-    o.o_orderdate as o_orderdate,
-    o.o_shippriority as o_shippriority,
-    o.o_orderkey as o_orderkey
+    c_custkey
+from
+    `{path}customer`
+where
+    c_mktsegment = 'MACHINERY'
+);
+
+$o = (
+select
+    o_orderdate,
+    o_shippriority,
+    o_orderkey
 from
-    `{path}customer` as c
-join
     `{path}orders` as o
+left semi join
+    $c as c
 on
     c.c_custkey = o.o_custkey
+where
+    CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
 );
 
 $join2 = (
 select
-    j1.c_mktsegment as c_mktsegment,
-    j1.o_orderdate as o_orderdate,
-    j1.o_shippriority as o_shippriority,
+    o.o_orderdate as o_orderdate,
+    o.o_shippriority as o_shippriority,
     l.l_orderkey as l_orderkey,
     l.l_discount as l_discount,
-    l.l_shipdate as l_shipdate,
     l.l_extendedprice as l_extendedprice
 from
-    $join1 as j1
-join
     `{path}lineitem` as l
+join
+    $o as o
 on
-    l.l_orderkey = j1.o_orderkey
+    l.l_orderkey = o.o_orderkey
+where
+    CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
 );
 
 select
@@ -40,10 +50,6 @@ select
     o_shippriority
 from
     $join2
-where
-    c_mktsegment = 'MACHINERY'
-    and CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
-    and CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
 group by
     l_orderkey,
     o_orderdate,
diff --git a/ydb/library/benchmarks/queries/tpch/yql/q3.sql b/ydb/library/benchmarks/queries/tpch/yql/q3.sql
index ec3ac17307..6caa989e8c 100644
--- a/ydb/library/benchmarks/queries/tpch/yql/q3.sql
+++ b/ydb/library/benchmarks/queries/tpch/yql/q3.sql
@@ -4,35 +4,45 @@
 -- TPC TPC-H Parameter Substitution (Version 2.17.2 build 0)
 -- using 1680793381 as a seed to the RNG
 
-$join1 = (
+$c = (
 select
-    c.c_mktsegment as c_mktsegment,
-    o.o_orderdate as o_orderdate,
-    o.o_shippriority as o_shippriority,
-    o.o_orderkey as o_orderkey
+    c_custkey
+from
+    {{customer}}
+where
+    c_mktsegment = 'MACHINERY'
+);
+
+$o = (
+select
+    o_orderdate,
+    o_shippriority,
+    o_orderkey
 from
-    {{customer}} as c
-join
     {{orders}} as o
+left semi join
+    $c as c
 on
     c.c_custkey = o.o_custkey
+where
+    CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
 );
 
 $join2 = (
 select
-    j1.c_mktsegment as c_mktsegment,
-    j1.o_orderdate as o_orderdate,
-    j1.o_shippriority as o_shippriority,
+    o.o_orderdate as o_orderdate,
+    o.o_shippriority as o_shippriority,
     l.l_orderkey as l_orderkey,
     l.l_discount as l_discount,
-    l.l_shipdate as l_shipdate,
     l.l_extendedprice as l_extendedprice
 from
-    $join1 as j1
-join
     {{lineitem}} as l
+join
+    $o as o
 on
-    l.l_orderkey = j1.o_orderkey
+    l.l_orderkey = o.o_orderkey
+where
+    CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
 );
 
 select
@@ -42,10 +52,6 @@ select
     o_shippriority
 from
     $join2
-where
-    c_mktsegment = 'MACHINERY'
-    and CAST(o_orderdate AS Timestamp) < Date('1995-03-08')
-    and CAST(l_shipdate AS Timestamp) > Date('1995-03-08')
 group by
     l_orderkey,
     o_orderdate,