Skip to content

Commit 9e8dd76

Browse files
authored
bench(hj): Add missing Q16–Q23 to benchmarks (#23257)
## Which issue does this PR close? Adds benchmark query for #23237 Useful to test changes in #23209 ## Rationale for this change Q16–Q22 existed in `hj.rs` but were missing `.benchmark` files, so they were invisible to `bench.sh` and the CI benchmark bot. Q23 is a new query reproducing a skewed high-fanout pattern where a single partition does nearly all the join work -- almost all probe rows carry the same key. Statistics are disabled to force `Partitioned` mode. ## What changes are included in this PR? - `benchmarks/src/hj.rs`: adds Q23 - `benchmarks/sql_benchmarks/hj/benchmarks/q16–q23.benchmark`: sql harness files for Q16–Q23 so they run via `bench.sh` and the CI benchmark bot ## Are these changes tested? These are benchmark additions, so no tests are needed. ## Are there any user-facing changes? No
1 parent 15d676a commit 9e8dd76

11 files changed

Lines changed: 255 additions & 0 deletions

File tree

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q16
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM customer
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q16: RightSemi, Small build (25 rows), 100% Hit rate
17+
-- Build Side: nation (25 rows) | Probe Side: customer (1.5M rows)
18+
SELECT c.k
19+
FROM (SELECT CAST(n_nationkey AS INT) as k FROM nation) n
20+
RIGHT SEMI JOIN (SELECT CAST(c_nationkey AS INT) as k FROM customer) c
21+
ON n.k = c.k;
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q17
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q17: RightSemi, Medium build (100K rows), 100% Hit rate
17+
-- Build Side: supplier (100K rows) | Probe Side: lineitem (60M rows)
18+
SELECT l.k
19+
FROM (SELECT CAST(s_suppkey AS INT) as k FROM supplier) s
20+
RIGHT SEMI JOIN (SELECT CAST(l_suppkey AS INT) as k FROM lineitem) l
21+
ON s.k = l.k;
Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
name Q18
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q18: RightSemi, Medium build (100K rows), 10% Hit rate
17+
-- Build Side: supplier (100K rows) | Probe Side: lineitem (60M rows)
18+
SELECT l.k
19+
FROM (SELECT CAST(s_suppkey AS INT) as k FROM supplier) s
20+
RIGHT SEMI JOIN (
21+
SELECT CAST(CASE WHEN l_suppkey % 10 = 0 THEN l_suppkey ELSE l_suppkey + 1000000 END AS INT) as k
22+
FROM lineitem
23+
) l
24+
ON s.k = l.k;
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q19
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM customer
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q19: RightAnti, Small build (25 rows), 100% Hit rate (no output)
17+
-- Build Side: nation (25 rows) | Probe Side: customer (1.5M rows)
18+
SELECT c.k
19+
FROM (SELECT CAST(n_nationkey AS INT) as k FROM nation) n
20+
RIGHT ANTI JOIN (SELECT CAST(c_nationkey AS INT) as k FROM customer) c
21+
ON n.k = c.k;
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
name Q20
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q20: RightAnti, Medium build (100K rows), 100% Hit rate (no output)
17+
-- Build Side: supplier (100K rows) | Probe Side: lineitem (60M rows)
18+
SELECT l.k
19+
FROM (SELECT CAST(s_suppkey AS INT) as k FROM supplier) s
20+
RIGHT ANTI JOIN (SELECT CAST(l_suppkey AS INT) as k FROM lineitem) l
21+
ON s.k = l.k;
Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
name Q21
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q21: RightAnti, Medium build (100K rows), 10% Hit rate (90% output)
17+
-- Build Side: supplier (100K rows) | Probe Side: lineitem (60M rows)
18+
SELECT l.k
19+
FROM (SELECT CAST(s_suppkey AS INT) as k FROM supplier) s
20+
RIGHT ANTI JOIN (
21+
SELECT CAST(CASE WHEN l_suppkey % 10 = 0 THEN l_suppkey ELSE l_suppkey + 1000000 END AS INT) as k
22+
FROM lineitem
23+
) l
24+
ON s.k = l.k;
Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
name Q22
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q22: RightSemi, Medium build (100K rows), ~1% Hit rate, fanout ~100
17+
-- Build Side: supplier (100K rows) collapsed onto 1K distinct keys
18+
-- Probe Side: lineitem (60M rows)
19+
SELECT l.k
20+
FROM (
21+
SELECT CAST(((s_suppkey - 1) % 1000) + 1 AS INT) as k
22+
FROM supplier
23+
) s
24+
RIGHT SEMI JOIN (
25+
SELECT CAST(l_suppkey AS INT) as k
26+
FROM lineitem
27+
) l
28+
ON s.k = l.k;
Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
name Q23
2+
group hj
3+
4+
init sql_benchmarks/hj/init/set_config_no_stats.sql
5+
6+
load sql_benchmarks/hj/init/load.sql
7+
8+
assert I
9+
SELECT count(*) > 0 FROM lineitem
10+
----
11+
true
12+
13+
expect_plan HashJoinExec
14+
15+
run
16+
-- Q23: high-fanout string-key inner join.
17+
-- Build ~32K rows / ~415 distinct keys (fanout ~78), probe ~2.3M rows
18+
-- (all carrying the dominant key), output ~176M pairs. Long keys (~28 chars)
19+
-- make per-pair key comparison expensive; count(*) isolates the match path.
20+
-- Thresholds zeroed to force Partitioned mode (simulates absent row-count stats).
21+
SELECT count(*)
22+
FROM (
23+
SELECT 'high_fanout_string_join_key_' || CAST((s_suppkey % 415) + 1 AS VARCHAR) as k
24+
FROM supplier
25+
WHERE s_suppkey <= 32340
26+
) s
27+
JOIN (
28+
SELECT 'high_fanout_string_join_key_1' as k
29+
FROM lineitem
30+
WHERE l_orderkey % 265 = 0
31+
) l ON s.k = l.k;
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
set datafusion.optimizer.join_reordering = false;
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
set datafusion.optimizer.join_reordering = false;
2+
set datafusion.optimizer.hash_join_single_partition_threshold = 0;
3+
set datafusion.optimizer.hash_join_single_partition_threshold_rows = 0;

0 commit comments

Comments
 (0)