Skip to content

Commit 8b6917c

Browse files
authored
Add hj SQL benchmark (apache#22802)
## Which issue does this PR close? Part of apache#21706 ## Rationale for this change Continue work on sql benchmark migration. ## What changes are included in this PR? hj sql benchmark ## Are these changes tested? Yes `BENCH_NAME=hj cargo bench --bench sql` ## Are there any user-facing changes? No
1 parent 228a996 commit 8b6917c

16 files changed

Lines changed: 399 additions & 0 deletions
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
name Q01
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q1: Very Small Build Side (Dense)
15+
-- Build Side: nation (25 rows) | Probe Side: customer (1.5M rows)
16+
-- density: 1.0,
17+
-- prob_hit: 1.0,
18+
-- build_size: "25",
19+
-- probe_size: "1.5M",
20+
SELECT n_nationkey
21+
FROM nation
22+
JOIN customer ON c_nationkey = n_nationkey;
Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
name Q02
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q2: Very Small Build Side (Sparse, range < 1024)
15+
-- Build Side: nation (25 rows, range 961) | Probe Side: customer (1.5M rows)
16+
-- density: 0.026,
17+
-- prob_hit: 1.0,
18+
-- build_size: "25",
19+
-- probe_size: "1.5M",
20+
SELECT l.k
21+
FROM (SELECT c_nationkey * 40 as k
22+
FROM customer) l
23+
JOIN (SELECT n_nationkey * 40 as k
24+
FROM nation) s ON l.k = s.k;
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q03
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q3: 100% Density, 100% Hit rate
15+
-- density: 1.0,
16+
-- prob_hit: 1.0,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT s_suppkey
20+
FROM supplier
21+
JOIN lineitem ON s_suppkey = l_suppkey;
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
name Q04
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q4: 100% Density, 10% Hit rate
15+
-- density: 1.0,
16+
-- prob_hit: 0.1,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (
21+
SELECT CASE WHEN l_suppkey % 10 = 0 THEN l_suppkey ELSE l_suppkey + 1000000 END as k
22+
FROM lineitem
23+
) l
24+
JOIN (
25+
SELECT s_suppkey as k FROM supplier
26+
) s ON l.k = s.k;
Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
name Q05
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q5: 75% Density, 100% Hit rate
15+
-- density: 0.75,
16+
-- prob_hit: 1.0,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (SELECT l_suppkey * 4 / 3 as k
21+
FROM lineitem) l
22+
JOIN (SELECT s_suppkey * 4 / 3 as k
23+
FROM supplier) s ON l.k = s.k;
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q06
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q6: 75% Density, 10% Hit rate
15+
-- density: 0.75,
16+
-- prob_hit: 0.1,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (
21+
SELECT CASE
22+
WHEN l_suppkey % 10 = 0 THEN l_suppkey * 4 / 3
23+
WHEN l_suppkey % 10 < 9 THEN (l_suppkey * 4 / 3 / 4) * 4 + 3
24+
ELSE l_suppkey * 4 / 3 + 1000000
25+
END as k
26+
FROM lineitem
27+
) l
28+
JOIN (
29+
SELECT s_suppkey * 4 / 3 as k FROM supplier
30+
) s ON l.k = s.k;
Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
name Q07
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q7: 50% Density, 100% Hit rate
15+
-- density: 0.5,
16+
-- prob_hit: 1.0,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (SELECT l_suppkey * 2 as k
21+
FROM lineitem) l
22+
JOIN (SELECT s_suppkey * 2 as k
23+
FROM supplier) s ON l.k = s.k;
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q08
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q8: 50% Density, 10% Hit rate
15+
-- density: 0.5,
16+
-- prob_hit: 0.1,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (
21+
SELECT CASE
22+
WHEN l_suppkey % 10 = 0 THEN l_suppkey * 2
23+
WHEN l_suppkey % 10 < 9 THEN l_suppkey * 2 + 1
24+
ELSE l_suppkey * 2 + 1000000
25+
END as k
26+
FROM lineitem
27+
) l
28+
JOIN (
29+
SELECT s_suppkey * 2 as k FROM supplier
30+
) s ON l.k = s.k;
Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
name Q09
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q9: 20% Density, 100% Hit rate
15+
-- density: 0.2,
16+
-- prob_hit: 1.0,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (SELECT l_suppkey * 5 as k
21+
FROM lineitem) l
22+
JOIN (SELECT s_suppkey * 5 as k
23+
FROM supplier) s ON l.k = s.k;
Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
name Q10
2+
group hj
3+
4+
load sql_benchmarks/hj/init/load.sql
5+
6+
assert I
7+
SELECT count(*) > 0 FROM customer
8+
----
9+
true
10+
11+
expect_plan HashJoinExec
12+
13+
run
14+
-- Q10: 20% Density, 10% Hit rate
15+
-- density: 0.2,
16+
-- prob_hit: 0.1,
17+
-- build_size: "100K",
18+
-- probe_size: "60M",
19+
SELECT l.k
20+
FROM (
21+
SELECT CASE
22+
WHEN l_suppkey % 10 = 0 THEN l_suppkey * 5
23+
WHEN l_suppkey % 10 < 9 THEN l_suppkey * 5 + 1
24+
ELSE l_suppkey * 5 + 1000000
25+
END as k
26+
FROM lineitem
27+
) l
28+
JOIN (
29+
SELECT s_suppkey * 5 as k FROM supplier
30+
) s ON l.k = s.k;

0 commit comments

Comments
 (0)