Skip to content

Commit 183941b

Browse files
adriangbclaude
authored andcommitted
bench: add spill_views SQL benchmarks for spilling StringView/BinaryView columns (apache#25625)
## Which issue does this PR close? - Refers to apache#23564. - Precursor for apache#23565. This PR must merge first, so the benchmark bot can compare `main` and that PR with `run benchmark spill_views`. ## Rationale for this change apache#23565 changes how spilled `StringView` and `BinaryView` columns are compacted. No current benchmark shows the effect: - The `sort_tpch` string columns are either high-cardinality (`l_comment`) or have a dictionary buffer that is too small to be compacted (`l_shipinstruct`). - Most suites spill only when the environment sets a low memory limit. ## What changes are included in this PR? A new SQL benchmark suite `spill_views` (in `benchmarks/sql_benchmarks/spill_views/`) and a `bench.sh` entry for it. There is no change to the spill code. Each query reads 1M rows from a Parquet file that the suite writes with `COPY`. The data must come from Parquet, because then the views of a dictionary-encoded column point into one shared buffer. Each query sets its own memory limit and `target_partitions = 4`, so it spills in any environment. | Query | Subgroup | What it does | Memory limit | |---|---|---|---| | `q01_sort_string_1_distinct` | `repeated` | `ORDER BY` a shuffled key, `StringView` payload with 1 distinct value (64 bytes) | 40M | | `q02_sort_string_1000_distinct` | `repeated` | Same, with 1000 distinct values | 40M | | `q03_sort_binary_1000_distinct` | `repeated` | Same as q02, with a `BinaryView` payload | 40M | | `q04_sort_string_all_distinct` | `distinct` | Same, with all-distinct values (compaction cannot remove data) | 96M | | `q05_group_by_string_all_distinct` | `distinct` | `GROUP BY` an all-distinct string key (compaction cannot remove data) | 96M | The asserts check the row count, the number of distinct values, that the column is read as a view type, and that the memory limit and `target_partitions` settings are applied. ## What is the testing strategy for this PR? I ran the suite with `benchmark_runner` and with `./bench.sh run spill_views` (the `cargo bench --bench sql` path that the bot uses). All asserts pass and each query takes less than 150 ms per iteration. I also ran the same queries with `EXPLAIN ANALYZE`, on `main` and with this commit on top of apache#23565. All queries spill on both sides. Spilled bytes are from the sort or final aggregation operator. Times are the median of 40 iterations of `benchmark_runner` on a laptop (Apple M4 Pro, local SSD), with runs of both builds alternated: | Query | Spilled (`main`) | Spilled (PR) | Time (`main`) | Time (PR) | |---|---|---|---|---| | q01 sort, 1 distinct | 84.2 MB | 23.2 MB | 63.5 ms | 57.0 ms | | q02 sort, 1000 distinct | 84.2 MB | 30.6 MB | 57.0 ms | 65.3 ms | | q03 sort, 1000 distinct binary | 84.2 MB | 30.6 MB | 54.0 ms | 65.9 ms | | q04 sort, all distinct | 84.2 MB | 84.2 MB | 71.0 ms | 95.7 ms | | q05 group by, all distinct | 84.2 MB | 84.2 MB | 75.2 ms | 77.7 ms | The machine was under load, so the times are only approximate. On a local SSD, spill I/O is cheap. The benchmark bot will give better numbers. ## Are there any user-facing changes? No. This PR only adds a benchmark. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-authored-by: Claude Opus 5 <[email protected]>
1 parent 85966e9 commit 183941b

21 files changed

Lines changed: 230 additions & 0 deletions

‎benchmarks/bench.sh‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -112,6 +112,10 @@ parquet_row_filter_skip: Per-RG fully-matched RowFilter skip on Parquet (apache/
112112
range filter + pushdown, so most row groups are fully matched and the per-row RowFilter is skipped on them
113113
(subgroups via BENCH_SUBGROUP: skip = clustered key so the skip fires, control = scrambled key so it never fires)
114114
(data generated inline by the suite's load SQL; knobs: PRED_ROWS, RG_SIZE)
115+
spill_views: Sort and GROUP BY queries that spill StringView/BinaryView columns read from Parquet
116+
(https://github.com/apache/datafusion/issues/23564); each query sets its own memory limit
117+
(subgroups via BENCH_SUBGROUP: repeated = low-cardinality payload, distinct = all-distinct strings)
118+
(data generated inline by the suite's load SQL)
115119
null_aware_join: Null-aware (NOT IN) hash join micro-benchmarks: uncorrelated, non-equality-correlated and equality-correlated
116120
NOT IN across NULL fractions, to measure the per-pair join-filter work the correlated cases do
117121
(data generated inline by the suite's load SQL from range(); knobs: NAJ_ROWS, NAJ_LARGE_ROWS)
@@ -279,6 +283,10 @@ main() {
279283
# Data is generated inline by the suite's load SQL (COPY).
280284
echo "parquet_row_filter_skip: no external data to generate"
281285
;;
286+
spill_views)
287+
# Data is generated inline by the suite's load SQL (COPY).
288+
echo "spill_views: no external data to generate"
289+
;;
282290
null_aware_join)
283291
# Data is generated inline by the suite's load SQL from range().
284292
echo "null_aware_join: no external data to generate"
@@ -535,6 +543,9 @@ main() {
535543
parquet_row_filter_skip)
536544
run_parquet_row_filter_skip
537545
;;
546+
spill_views)
547+
run_spill_views
548+
;;
538549
null_aware_join)
539550
run_null_aware_join
540551
;;
@@ -998,6 +1009,20 @@ run_projection_subquery() {
9981009
bash -c "$SQL_CARGO_COMMAND"
9991010
}
10001011

1012+
# Runs the spill_views suite: sort and GROUP BY queries that spill StringView
1013+
# and BinaryView columns read from Parquet
1014+
# (https://github.com/apache/datafusion/issues/23564). The load SQL COPYs 1M-row
1015+
# Parquet files inline, so there is no data step. Each query sets its own memory
1016+
# limit and target_partitions, so it spills regardless of the environment.
1017+
# BENCH_SUBGROUP run one subgroup (repeated, distinct)
1018+
run_spill_views() {
1019+
echo "Running spill_views benchmark (subgroup=${BENCH_SUBGROUP:-all})..."
1020+
debug_run env BENCH_NAME=spill_views \
1021+
${BENCH_SUBGROUP:+BENCH_SUBGROUP="${BENCH_SUBGROUP}"} \
1022+
${QUERY:+BENCH_QUERY="${QUERY}"} \
1023+
bash -c "$SQL_CARGO_COMMAND"
1024+
}
1025+
10011026
# Runs the tpch in memory (needs tpch parquet data)
10021027
run_tpch_mem() {
10031028
SCALE_FACTOR=$1

‎benchmarks/sql_benchmarks/README.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@ in the community:
4747
| `wide_schema` | Small-projection queries on a wide (1024-col, 256-file) synthetic dataset; runs `wide` + `narrow` subgroups for comparison |
4848
| `predicate_eval` | Conjunctive (AND) filter-evaluation micro-benchmarks; each subgroup is a different predicate pattern, to test how an adaptive predicate-ordering system behaves across them ([#11262](https://github.com/apache/datafusion/issues/11262)). Subgroups (`--subgroup`): `costsel`, `cost`, `selectivity`, `cardinality`, `width`, `scale`, `neutral`, `correlation`, `drift`, `nulls`. The suite sets no engine config of its own, so by default it measures DataFusion's built-in left-deep `AND` short-circuit; point it at a system under test by exporting that system's own DataFusion setting (the harness builds its `SessionConfig` with `SessionConfig::from_env`). Every query is a `count(*)`, and the counts are checked in under `predicate_eval/results/`, so `--result-mode validate` also checks that a reordering under test still returns the same rows; the checked-in counts were persisted at the suite defaults (`PRED_ROWS=1000000`, `PRED_FILL=30`), so validation assumes those (the `scale` and `width` subgroups pin their own values per query and validate at any setting). |
4949
| `parquet_row_filter_skip` | Micro-benchmark for the per-row-group fully-matched RowFilter skip on Parquet scans ([#23696](https://github.com/apache/datafusion/issues/23696)). Subgroups (`--subgroup`): `skip` (clustered key, most row groups fully matched by statistics so the per-row filter is skipped), `control` (scrambled key, no row group is ever fully matched). Size the data with `PRED_ROWS` and the row-group size with `RG_SIZE`. |
50+
| `spill_views` | Sort and `GROUP BY` queries that spill `StringView` and `BinaryView` columns read from Parquet ([#23564](https://github.com/apache/datafusion/issues/23564)). Each query sets its own memory limit and `target_partitions = 4`, so it spills in any environment. Subgroups (`--subgroup`): `repeated` (sort with a low-cardinality payload that shares one dictionary buffer, where spill compaction can remove data), `distinct` (sort and `GROUP BY` on all-distinct strings, where compaction cannot remove data). |
5051
| `projection_subquery` | `IN`, `NOT IN` and `EXISTS` subqueries that sit in the `SELECT` list instead of a filter ([#25341](https://github.com/apache/datafusion/issues/25341)). Every query projects the boolean subquery result and aggregates it, so the measured cost is the decorrelation plan and not the size of the output. `q07` correlates on `<` instead of `=`, so it keeps the nested-loop plan and is the control that must not change. Size the two inline tables with `PSQ_ROWS` (default `30000`). The counts are checked in under `projection_subquery/results/`, so `--result-mode validate` also proves that a change to the decorrelation still returns the same rows; the checked-in counts were persisted at the default `PSQ_ROWS`, so validation assumes that value. |
5152

5253
# Running Benchmarks
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
subgroup distinct
2+
3+
template sql_benchmarks/spill_views/spill_views.benchmark.template
4+
QPAD=04
5+
DATASET=distinct
6+
NAME=q04_sort_string_all_distinct
7+
SPILL_LIMIT=96M
8+
VIEW_TYPE=Utf8View
9+
DISTINCT_S=1000000
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
subgroup distinct
2+
3+
template sql_benchmarks/spill_views/spill_views.benchmark.template
4+
QPAD=05
5+
DATASET=distinct
6+
NAME=q05_group_by_string_all_distinct
7+
SPILL_LIMIT=96M
8+
VIEW_TYPE=Utf8View
9+
DISTINCT_S=1000000
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
subgroup repeated
2+
3+
template sql_benchmarks/spill_views/spill_views.benchmark.template
4+
QPAD=01
5+
DATASET=low_card_1
6+
NAME=q01_sort_string_1_distinct
7+
SPILL_LIMIT=40M
8+
VIEW_TYPE=Utf8View
9+
DISTINCT_S=1
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
subgroup repeated
2+
3+
template sql_benchmarks/spill_views/spill_views.benchmark.template
4+
QPAD=02
5+
DATASET=low_card_1000
6+
NAME=q02_sort_string_1000_distinct
7+
SPILL_LIMIT=40M
8+
VIEW_TYPE=Utf8View
9+
DISTINCT_S=1000
Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
subgroup repeated
2+
3+
template sql_benchmarks/spill_views/spill_views.benchmark.template
4+
QPAD=03
5+
DATASET=low_card_1000_binary
6+
NAME=q03_sort_binary_1000_distinct
7+
SPILL_LIMIT=40M
8+
VIEW_TYPE=BinaryView
9+
DISTINCT_S=1000
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP TABLE IF EXISTS t;
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- Session settings for the spill_views suite. `init` runs after `load`, so
2+
-- the COPY in the load script runs without the memory limit.
3+
set datafusion.catalog.information_schema = true;
4+
set datafusion.execution.target_partitions = 4;
5+
set datafusion.runtime.memory_limit = '${SPILL_LIMIT}';
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
-- All-distinct string values. The Parquet dictionary falls back to plain
2+
-- encoding, and there is no repeated data for spill compaction to remove.
3+
--
4+
-- 1M rows. `id` holds distinct values in shuffled order (7919 is
5+
-- invertible modulo the prime 1000003), so ORDER BY id must really sort.
6+
-- `s` is a 64-byte payload.
7+
COPY (
8+
SELECT
9+
(value * 7919) % 1000003 AS id,
10+
'payload-' || lpad(CAST(value AS VARCHAR), 56, '0') AS s
11+
FROM generate_series(1, 1000000)
12+
)
13+
TO 'sql_benchmarks/spill_views/scratch/distinct.parquet'
14+
STORED AS PARQUET;
15+
16+
CREATE EXTERNAL TABLE t
17+
STORED AS PARQUET
18+
LOCATION 'sql_benchmarks/spill_views/scratch/distinct.parquet';

0 commit comments

Comments
 (0)