You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "Omega359 (via GitHub)" <gi...@apache.org> on 2026/06/08 01:56:53 UTC
[PR] Add sort tpch SQL benchmark [datafusion]
Omega359 opened a new pull request, #22814:
URL: https://github.com/apache/datafusion/pull/22814
## Which issue does this PR close?
Part of #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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3381426426
##########
benchmarks/sql_benchmarks/sort_tpch/init/load.sql:
##########
@@ -0,0 +1,3 @@
+CREATE EXTERNAL TABLE lineitem_raw STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/lineitem/lineitem.1.parquet';
+
+CREATE TABLE lineitem as (SELECT * FROM lineitem_raw ${BENCH_SORTED:-false|order by l_orderkey asc| });
Review Comment:
Would need a space before the order by I think
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3380668941
##########
benchmarks/sql_benchmarks/sort_tpch/init/load.sql:
##########
@@ -0,0 +1,3 @@
+CREATE EXTERNAL TABLE lineitem_raw STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/lineitem/lineitem.1.parquet';
+
+CREATE TABLE lineitem as (SELECT * FROM lineitem_raw ${BENCH_SORTED:-false|order by l_orderkey asc| });
Review Comment:
```suggestion
CREATE TABLE lineitem as (SELECT * FROM lineitem_raw${BENCH_SORTED:-false|order by l_orderkey asc | });
```
Would this work and avoid a double space? No big deal either way.
##########
benchmarks/sql_benchmarks/sort_tpch/benchmarks/q05.benchmark:
##########
@@ -0,0 +1,44 @@
+echo Loading tpch items sorted: ${BENCH_SORTED:-false}
+
+#
+# Sort queries with different characteristics:
+# - Sort key with fixed length or variable length (VARCHAR)
+# - Sort key with different cardinality
+# - Different number of sort keys
+# - Different number of payload columns (thin: 1 additional column other
+# than sort keys; wide: all columns except sort keys)
+#
+# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
+# scale factor 1.0, cardinality is counted from SF1 dataset)
+#
+# Key Columns:
+# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
+# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
+# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
+# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
+#
+# Payload Columns:
+# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
+# - Wide variant: all columns except for possible key columns (12 columns)
+
+name Q05
+group sort_tpch
+subgroup sf${BENCH_SIZE:-1}
+
+echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
+
+load sql_benchmarks/sort_tpch/init/load.sql
+
+assert I
+SELECT COUNT(*) > 0 from lineitem;
+----
+true
+
+run
+-- Q5: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + no payload column
+SELECT l_linenumber, l_suppkey, l_orderkey
+FROM lineitem
+ORDER BY l_linenumber, l_suppkey, l_orderkey
+${LIMIT:-false|LIMIT 100| }
Review Comment:
Do we need to document these env vars?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3381603145
##########
benchmarks/sql_benchmarks/sort_tpch/benchmarks/q05.benchmark:
##########
@@ -0,0 +1,44 @@
+echo Loading tpch items sorted: ${BENCH_SORTED:-false}
+
+#
+# Sort queries with different characteristics:
+# - Sort key with fixed length or variable length (VARCHAR)
+# - Sort key with different cardinality
+# - Different number of sort keys
+# - Different number of payload columns (thin: 1 additional column other
+# than sort keys; wide: all columns except sort keys)
+#
+# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
+# scale factor 1.0, cardinality is counted from SF1 dataset)
+#
+# Key Columns:
+# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
+# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
+# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
+# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
+#
+# Payload Columns:
+# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
+# - Wide variant: all columns except for possible key columns (12 columns)
+
+name Q05
+group sort_tpch
+subgroup sf${BENCH_SIZE:-1}
+
+echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
+
+load sql_benchmarks/sort_tpch/init/load.sql
+
+assert I
+SELECT COUNT(*) > 0 from lineitem;
+----
+true
+
+run
+-- Q5: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + no payload column
+SELECT l_linenumber, l_suppkey, l_orderkey
+FROM lineitem
+ORDER BY l_linenumber, l_suppkey, l_orderkey
+${LIMIT:-false|LIMIT 100| }
Review Comment:
There are some that are shared (`LIMIT` makes sense as shared) and some that are per benchmark. I have seen them documented, but I'm not sure if we're documenting which global ones apply to which benchmarks (i.e. what options each bench supports). As in: without looking at the actual benchmark code I wouldn't know that this benchmark accepts / respects `LIMIT`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3381432189
##########
benchmarks/sql_benchmarks/sort_tpch/benchmarks/q05.benchmark:
##########
@@ -0,0 +1,44 @@
+echo Loading tpch items sorted: ${BENCH_SORTED:-false}
+
+#
+# Sort queries with different characteristics:
+# - Sort key with fixed length or variable length (VARCHAR)
+# - Sort key with different cardinality
+# - Different number of sort keys
+# - Different number of payload columns (thin: 1 additional column other
+# than sort keys; wide: all columns except sort keys)
+#
+# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
+# scale factor 1.0, cardinality is counted from SF1 dataset)
+#
+# Key Columns:
+# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
+# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
+# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
+# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
+#
+# Payload Columns:
+# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
+# - Wide variant: all columns except for possible key columns (12 columns)
+
+name Q05
+group sort_tpch
+subgroup sf${BENCH_SIZE:-1}
+
+echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
+
+load sql_benchmarks/sort_tpch/init/load.sql
+
+assert I
+SELECT COUNT(*) > 0 from lineitem;
+----
+true
+
+run
+-- Q5: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + no payload column
+SELECT l_linenumber, l_suppkey, l_orderkey
+FROM lineitem
+ORDER BY l_linenumber, l_suppkey, l_orderkey
+${LIMIT:-false|LIMIT 100| }
Review Comment:
I think they're documented in the sql_benchmarks/README.md but I'd have to check
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3380668941
##########
benchmarks/sql_benchmarks/sort_tpch/init/load.sql:
##########
@@ -0,0 +1,3 @@
+CREATE EXTERNAL TABLE lineitem_raw STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/lineitem/lineitem.1.parquet';
+
+CREATE TABLE lineitem as (SELECT * FROM lineitem_raw ${BENCH_SORTED:-false|order by l_orderkey asc| });
Review Comment:
```suggestion
CREATE TABLE lineitem as (SELECT * FROM lineitem_raw${BENCH_SORTED:-false| order by l_orderkey asc| });
```
Would this work and avoid a double space? No big deal either way.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3381607363
##########
benchmarks/sql_benchmarks/sort_tpch/init/load.sql:
##########
@@ -0,0 +1,3 @@
+CREATE EXTERNAL TABLE lineitem_raw STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/lineitem/lineitem.1.parquet';
+
+CREATE TABLE lineitem as (SELECT * FROM lineitem_raw ${BENCH_SORTED:-false|order by l_orderkey asc| });
Review Comment:
Yep fixed. Feel free to commit or ignore. But LGTM!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "Omega359 (via GitHub)" <gi...@apache.org>.
Omega359 commented on code in PR #22814:
URL: https://github.com/apache/datafusion/pull/22814#discussion_r3382065648
##########
benchmarks/sql_benchmarks/sort_tpch/benchmarks/q05.benchmark:
##########
@@ -0,0 +1,44 @@
+echo Loading tpch items sorted: ${BENCH_SORTED:-false}
+
+#
+# Sort queries with different characteristics:
+# - Sort key with fixed length or variable length (VARCHAR)
+# - Sort key with different cardinality
+# - Different number of sort keys
+# - Different number of payload columns (thin: 1 additional column other
+# than sort keys; wide: all columns except sort keys)
+#
+# DataSet is `lineitem` table in TPCH dataset (16 columns, 6M rows for
+# scale factor 1.0, cardinality is counted from SF1 dataset)
+#
+# Key Columns:
+# - Column `l_linenumber`, type: `INTEGER`, cardinality: 7
+# - Column `l_suppkey`, type: `BIGINT`, cardinality: 10k
+# - Column `l_orderkey`, type: `BIGINT`, cardinality: 1.5M
+# - Column `l_comment`, type: `VARCHAR`, cardinality: 4.5M (len is ~26 chars)
+#
+# Payload Columns:
+# - Thin variant: `l_partkey` column with `BIGINT` type (1 column)
+# - Wide variant: all columns except for possible key columns (12 columns)
+
+name Q05
+group sort_tpch
+subgroup sf${BENCH_SIZE:-1}
+
+echo Loading sort_tpch sf ${BENCH_SIZE:-1} data
+
+load sql_benchmarks/sort_tpch/init/load.sql
+
+assert I
+SELECT COUNT(*) > 0 from lineitem;
+----
+true
+
+run
+-- Q5: 3 sort keys {(INTEGER, 7), (BIGINT, 10k), (BIGINT, 1.5M)} + no payload column
+SELECT l_linenumber, l_suppkey, l_orderkey
+FROM lineitem
+ORDER BY l_linenumber, l_suppkey, l_orderkey
+${LIMIT:-false|LIMIT 100| }
Review Comment:
This likely should be addressed as part of https://github.com/apache/datafusion/issues/21935
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] Add sort tpch SQL benchmark [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #22814:
URL: https://github.com/apache/datafusion/pull/22814
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org