File tree Expand file tree Collapse file tree
benchmarks/sql_benchmarks/hj Expand file tree Collapse file tree Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
Original file line number Diff line number Diff line change 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;
You can’t perform that action at this time.
0 commit comments