Skip to content

Commit 982fc22

Browse files
Omega359adriangb
authored andcommitted
Add sort tpch SQL benchmark (apache#22814)
## 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? sort_tpch sql benchmark ## Are these changes tested? Yes `BENCH_NAME=sort_tpch cargo bench --bench sql` `BENCH_NAME=sort_tpch BENCH_SIZE=10 cargo bench --bench sql` `BENCH_NAME=sort_tpch LIMIT=true cargo bench --bench sql` `BENCH_NAME=sort_tpch BENCH_SIZE=10 LIMIT=true cargo bench --bench sql` ## Are there any user-facing changes? No --------- Co-authored-by: Adrian Garcia Badaracco <[email protected]>
1 parent 9a2e99a commit 982fc22

12 files changed

Lines changed: 516 additions & 0 deletions

File tree

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q01
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q1: 1 sort key (type: INTEGER, cardinality: 7) + 1 payload column
39+
SELECT l_linenumber, l_partkey
40+
FROM lineitem
41+
ORDER BY l_linenumber
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q01.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q02
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q2: 1 sort key (type: BIGINT, cardinality: 1.5M) + 1 payload column
39+
SELECT l_orderkey, l_partkey
40+
FROM lineitem
41+
ORDER BY l_orderkey
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q02.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q03
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q3: 1 sort key (type: VARCHAR, cardinality: 4.5M) + 1 payload column
39+
SELECT l_comment, l_partkey
40+
FROM lineitem
41+
ORDER BY l_comment
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q03.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q04
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q4: 2 sort keys {(BIGINT, 1.5M), (INTEGER, 7)} + 1 payload column
39+
SELECT l_orderkey, l_linenumber, l_partkey
40+
FROM lineitem
41+
ORDER BY l_orderkey, l_linenumber
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q04.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q05
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q5: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + no payload column
39+
SELECT l_linenumber, l_suppkey, l_orderkey
40+
FROM lineitem
41+
ORDER BY l_linenumber, l_suppkey, l_orderkey
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q05.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q06
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q6: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + 1 payload column
39+
SELECT l_linenumber, l_suppkey, l_orderkey, l_partkey
40+
FROM lineitem
41+
ORDER BY l_linenumber, l_suppkey, l_orderkey
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q06.csv
Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q07
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q7: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + 12 all other columns
39+
SELECT l_linenumber,
40+
l_suppkey,
41+
l_orderkey,
42+
l_partkey,
43+
l_quantity,
44+
l_extendedprice,
45+
l_discount,
46+
l_tax,
47+
l_returnflag,
48+
l_linestatus,
49+
l_shipdate,
50+
l_commitdate,
51+
l_receiptdate,
52+
l_shipinstruct,
53+
l_shipmode
54+
FROM lineitem
55+
ORDER BY l_linenumber, l_suppkey, l_orderkey
56+
${LIMIT:-false|LIMIT 100| }
57+
58+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q07.csv
Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
echo Loading tpch items sorted: ${BENCH_SORTED:-false}
2+
3+
#
4+
# Sort queries with different characteristics:
5+
# - Sort key with fixed length or variable length (VARCHAR)
6+
# - Sort key with different cardinality
7+
# - Different number of sort keys
8+
# - Different number of payload columns (thin: 1 additional column other
9+
# than sort keys; wide: all columns except sort keys)
10+
#
11+
# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
12+
# scale factor 1.0, cardinality is counted from SF1 dataset)
13+
#
14+
# Key Columns:
15+
# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
16+
# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
17+
# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
18+
# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
19+
#
20+
# Payload Columns:
21+
# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
22+
# - Wide variant: all columns except for possible key columns (12 columns)
23+
24+
name Q08
25+
group sort_tpch
26+
subgroup sf${BENCH_SIZE:-1}
27+
28+
echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
29+
30+
load sql_benchmarks/sort_tpch/init/load.sql
31+
32+
assert I
33+
SELECT COUNT(*) > 0 from lineitem;
34+
----
35+
true
36+
37+
run
38+
-- Q8: 4 sort keys {(BIGINT, 1.5M), (BIGINT, 10k), (INTEGER, 7), (VARCHAR, 4.5M)} + no payload column
39+
SELECT l_orderkey, l_suppkey, l_linenumber, l_comment
40+
FROM lineitem
41+
ORDER BY l_orderkey, l_suppkey, l_linenumber, l_comment
42+
${LIMIT:-false|LIMIT 100| }
43+
44+
result sql_benchmarks/sort_tpch/results/sf${BENCH_SIZE:-1}/q08.csv

0 commit comments

Comments
 (0)