Google Git Google Git
Sign in
apache/datafusion/refs/heads/codex/stack-demo-setup/./benchmarks/sql_benchmarks/README.md
blob: 55a1cc5898a911ffe1918304c8006741d55d2879 [file] [view]
<!---
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
# SQL Benchmarks
This directory contains a collection of benchmarks each driven by a simple '.benchmark' text file and sql queries
that exercise the DataFusion execution engine against a variety of benchmark suites. The sql benchmark framework
is intentionally simple so that benchmarks and queries can be added or modified without touching the core
engine or requiring recompilation.
The sql benchmarks are organized in sub‑directories that correspond to the benchmark suites that are commonly used
in the community:
| Benchmark Suite | Description |
|-----------------------|--------------------------------------------------------------------|
| `asof_join` | ASOF join benchmarks across size, ordering, grouping, match direction, and payload width |
| `clickbench` | ClickBench benchmark |
| `clickbench extended` | 12 additional, more complex queries against the Clickbench dataset |
| `clickbench_sorted` | ClickBench benchmark using a pre-sorted hits file. |
| `h2o` | The `h2o` benchmark |
| `hj` | Hash join benchmark |
| `imdb` | IMDb benchmark |
| `nlj` | Nested‑loop join benchmark |
| `null_aware_join` | Null-aware (`NOT IN`) hash join micro-benchmarks. Q01-Q03 are uncorrelated `NOT IN` across NULL fractions and are linear in the table size (`NAJ_LARGE_ROWS`, default `1000000`). Q04-Q08 are correlated, so the correlation predicate stays behind as a join filter that the join applies per candidate (build row × probe row) pair while deciding which outer rows are UNKNOWN; without an equality correlation there are no scope keys to narrow those pairs, so their cost grows with the square of `NAJ_ROWS` (default `10000`). Q08 adds an equality correlation, which turns those pairs into a hash lookup. All tables are built inline from `range()`, so there is no data step. |
| `push_down_topk` | `ORDER BY ... LIMIT` over outer joins (TPC-H data); exercises pushing a TopK through a join |
| `pwmj` | Piecewise merge join: classic `INNER`/`LEFT`/`RIGHT`/`FULL` joins over `lhs.key < rhs.key`. Subgroups (`--subgroup`) are match regimes: `no_match`, `null_heavy`, `half_match`, `selective`, `all_match`. Generated with `range()`, no data needed. |
| `smj` | Sort‑merge join benchmark |
| [`sort_pushdown`](sort_pushdown/README.md) | SQL counterparts of the five sort pushdown variants, using existing Parquet data and query files. |
| `sort tpch` | Sorting benchmarks against the TPC-H lineitem table |
| `taxi` | NYC taxi dataset benchmark |
| `tpcds` | TPC‑DS queries |
| `tpch` | TPC‑H queries |
| `wide_schema` | Small-projection queries on a wide (1024-col, 256-file) synthetic dataset; runs `wide` + `narrow` subgroups for comparison |
| `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). |
| `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`. |
| `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). Set the memory limit of each subgroup with `SPILL_VIEWS_LIMIT_REPEATED` (default `40M`) and `SPILL_VIEWS_LIMIT_DISTINCT` (default `96M`), for example to let both sides of a comparison finish when one of them runs out of memory at the default. The queries must still spill, so check a raised limit with `EXPLAIN ANALYZE` and keep it well below the working set: `q05` stops spilling at about `192M`. Give a whole number of K, M or G units, because the run asserts the limit that the memory pool reports back. |
| `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 the correlation stays a residual filter of the mark join. 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. |
# Running Benchmarks
Use `benchmark_runner` to run SQL benchmarks. It reads each suite's `.suite`
file and exposes the suite's configuration as command-line options. Use the
`bench.sh` shell script one level above this directory to download or generate
required data files.
```shell
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- tpch
```
## SQL benchmark runner
The `benchmark_runner` binary discovers suites from this directory and exposes
suite-specific options alongside the common benchmark options. The suite name
must come before all options.
```bash
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- --list
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- tpch --help
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- tpch --query 15 --format csv
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- clickbench --partitioning partitioned --dry-run
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- tpch --query 1 --result-mode persist
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- tpch --query 1 --result-mode validate
```
Use `--path PATH` or `-p PATH` to override `DATA_DIR` for a suite that declares
that path replacement. Suites without a `DATA_DIR` replacement reject the
option. Suite-specific values follow this precedence: command-line option,
environment variable, then the default in the suite metadata.
`--dry-run` prints the resolved suite, filters, run mode, common options,
suite-specific values, and path replacements as JSON. It reports source metadata
for suite options and path replacements. It validates the command but does not
load benchmark definitions, create a session, read datasets, execute SQL, or
write benchmark results.
Use `--result-mode persist` to save query results or `--result-mode validate` to
compare them with saved results. The default, `--result-mode none`, does neither.
For compatibility with direct Criterion runs, the runner also reads
`BENCH_PERSIST_RESULTS` and `BENCH_VALIDATE`. Persistence takes precedence when
both variables are `true`. An explicit `--result-mode` overrides both variables.
### Suite metadata
Each discoverable suite has one TOML metadata file named
`<suite>/<suite>.suite`. The runner accepts these top-level fields:
| Field | Required | Description |
|-------|----------|-------------|
| `description` | Yes | Non-empty text shown by `--list` and suite help. |
| `query_pattern` | No | Relative benchmark filename pattern. It must contain exactly one `{QUERY_ID}` or `{QUERY_ID_PADDED}` placeholder and defaults to `q{QUERY_ID_PADDED}.benchmark`. |
| `path_replacements` | No | Map of replacement names to paths. Relative paths resolve from the suite directory. `DATA_DIR` enables `--path/-p`. |
| `options` | No | Array of suite-specific option tables described below. |
| `examples` | No | Array of `command` and `description` pairs appended to suite help. Both values must contain text. |
Each `[[options]]` table has these fields:
| Field | Required | Description |
|-------|----------|-------------|
| `name` | Yes | Long option name without `--`; use lowercase ASCII letters, digits, and hyphens. |
| `short` | No | One ASCII letter or digit without `-`. |
| `env` | Yes | Environment variable that supplies the option value. |
| `default` | Yes | Value used when neither the command line nor the environment supplies one. |
| `values` | No | Accepted values. Omit the field to allow any value. Include `"..."` to allow the listed values plus any other value. Without `"..."`, the list is closed. |
| `help` | Yes | Non-empty text shown in suite help. |
Option names, short names, and environment keys must be unique within a suite.
An option environment key cannot also appear in `path_replacements`. Suite
options cannot reuse the runner's global names: `help` (`-h`), `query` (`-q`),
`subgroup`, `iterations` (`-i`), `partitions` (`-n`), `batch-size` (`-s`),
`mem-pool-type`, `memory-limit`, `sort-spill-reservation-bytes`, `debug` (`-d`),
`simulate-latency`, `criterion`, `list`, `output` (`-o`), `save-baseline`,
`path` (`-p`), `result-mode`, or `dry-run`.
# Benchmark configuration
`benchmark_runner` is the preferred interface for configuring and running SQL
benchmarks. Run `<suite> --help` to see the common and suite-specific options:
```shell
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- h2o --help
```
The runner maps suite options to the environment variables below for
compatibility with benchmark files and direct Criterion runs. Direct
`cargo bench --bench sql` invocations cannot accept custom arguments, so they
still use environment variables.
The SQL benchmarking tool uses the following environment variables:
| Environment Variable | Description |
|-----------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| BENCH_NAME | The name of the benchmark suite to run. For example 'imdb'. This should correspond to a directory name in the `sql_benchmarks` directory. |
| BENCH_SUBGROUP | The subgroup with the benchmark suite to run. For example 'window' to run the window subgroup of the h2o benchmark. |
| BENCH_QUERY | A query number to run. |
| BENCH_PERSIST_RESULTS | true/false to persist benchmark results. Results will be persisted in csv format so be cognizant of the size of the results. |
| BENCH_VALIDATE | true/false to validate benchmark results against persisted results or result_query's. If both `BENCH_PERSIST_RESULTS` and `BENCH_VALIDATE` are true, persist mode runs and validation is skipped. |
| BENCH_RESULTS_FILE | Criterion runs only: write a results JSON in the `dfbench` `-o` format to this path. Each query records its peak memory pool reservation (`pool_peak_bytes`, only under a memory limit, including one set with `SET datafusion.runtime.memory_limit`) and no timings, which stay with Criterion. `bench.sh` sets it to `results/<name>/criterion/<file>.json`. |
| DATA_DIR | Root directory for benchmark data loaded by SQL benchmark files. When unset, uses `data` (relative to the benchmarks/ directory). |
| SIMULATE_LATENCY | Simulate object store latency to mimic remote storage (e.g. S3). Adds random latency in the range 20-200ms to each object store operation. |
| MEM_POOL_TYPE | The memory pool type to use, should be one of "fair" or "greedy". |
| MEMORY_LIMIT | Memory limit (e.g. '100M', '1.5G'). If not specified, run all pre-defined memory limits for given query if there's any, otherwise run with no memory limit. |
Example: run the H2O window benchmarks on the small CSV data files:
```shell
cargo run -p datafusion-benchmarks --release --bin benchmark_runner -- h2o --subgroup window --size small --format csv
```
Some benchmarks use custom environment variables as outlined below:
| Name | Description | Default value |
|------------------------------|--------------------------------------------------------------------------------------------------------------------------|---------------|
| BENCH_SIZE | Used in the tpch, sort-tpch and tpcds benchmarks. The size corresponds to the scale factor. | `1` |
| TPCH_FILE_TYPE | Used in the tpch benchmark to specify which file type to query against. The valid options are `csv`, `parquet` and `mem` | `parquet` |
| H2O_FILE_TYPE | Used in the h2o benchmark to specify which file type to query against. The valid options are `csv` and `parquet` | `csv` |
| CLICKBENCH_TYPE | The type of partitioning for the clickbench benchmark. Valid options are `single` and `partitioned` | `single` |
| H2O_BENCH_SIZE | Used in the h2o benchmark. The valid options are `small`, `medium` and `big` | `small` |
| PREFER_HASH_JOIN | Control datafusion's config option `datafusion.optimizer.prefer_hash_join` | true |
| HASH_JOIN_BUFFERING_CAPACITY | Control datafusion's config option `datafusion.execution.hash_join_buffering_capacity` | 0 |
| BENCH_SORTED | Used in the sort_tpch benchmark to indicate whether the lineitem table should be sorted. | false |
| SORTED_BY | Used in the clickbench_sorted benchmark to indicate the column to sort by. | `EventTime` |
| SORTED_ORDER | Used in the clickbench_sorted benchmark to indicate the sort order of the column. | `ASC` |
| PRED_ROWS | Used in the predicate_eval benchmark to size the synthetic table (the `scale` subgroup overrides this per query), and in the parquet_row_filter_skip benchmark to size the generated Parquet datasets (default `10000000` there). | `1000000` |
| PRED_FILL | Used in the predicate_eval benchmark as the string-column width knob (filler chars per marker). | `30` |
| RG_SIZE | Used in the parquet_row_filter_skip benchmark as the Parquet `max_row_group_size` for the generated datasets. | `1000000` |
| PSQ_ROWS | Used in the projection_subquery benchmark to size the two inline tables. | `30000` |
| SPILL_VIEWS_LIMIT_REPEATED | Used in the spill_views benchmark as the memory limit of the `repeated` subgroup. | `40M` |
| SPILL_VIEWS_LIMIT_DISTINCT | Used in the spill_views benchmark as the memory limit of the `distinct` subgroup. | `96M` |
## How it works
The runner executes SQL benchmarks with its basic runner by default. Pass
`--criterion` to gather statistics with
[Criterion](https://docs.rs/criterion/latest/criterion/).
Each individual benchmark is represented by a `<name>.benchmark` file that contains a number of directives instructing
the tool on how to load data, run initializations, run assertions, run the benchmark, optionally persist and
validate results, and finally run any cleanup if required.
Benchmark files support replacement variables in two forms:
* string substitution with an optional default: \${ENV_VAR} and
\${ENV_VAR:-default}.
* if / else based on whether a replacement value is true or not
(\${ENV_VAR:-default|true value|false value}). In this form only the value `true` (case-insensitive) selects the
true branch; any other supplied value selects the false branch. If the value is absent, the parser uses `default` to
select the branch.
Comments in files are supported with lines starting with # or --.
Many if not most of the benchmarks are set up using templates to reduce duplication across the .benchmark files. For
example here is one of the benchmark files for the h2o benchmark suite:
```
subgroup groupby
template sql_benchmarks/h2o/h2o.benchmark.template
QUERY_NUMBER=1
QUERY_NUMBER_PADDED=01
```
The template directive above defines the subgroup the benchmark is part of, sets two variables (`QUERY_NUMBER` and
`QUERY_NUMBER_PADDED`) and points to a file containing more directives that are shared across the benchmark suite.
```
load sql_benchmarks/h2o/init/load_${BENCH_SUBGROUP:-groupby}_${BENCH_SIZE:-small}_${BENCH_FILE_TYPE:-csv}.sql
name Q${QUERY_NUMBER_PADDED}
group h2o
run sql_benchmarks/h2o/queries/${BENCH_SUBGROUP:-groupby}/q${QUERY_NUMBER_PADDED}.sql
result sql_benchmarks/h2o/results/${BENCH_SUBGROUP:-groupby}/${BENCH_SIZE:-small}/q${QUERY_NUMBER_PADDED}.csv
```
The above showcases the use of defaults for variables: `${NAME:-default}`
# Directives
<table>
<tr><th>Directive</th><th>Description</th></tr>
<tr>
<td>name</td>
<td>
The name of the benchmark. This will be used as part of the display name used by criterion.<br/><br/>Example:<br/>
<blockquote>name Q${QUERY_NUMBER_PADDED}</blockquote>
The `name` directive also makes the value available to benchmark-file replacements as `BENCH_NAME`. This value is
separate from the suite name passed to `benchmark_runner`.
</td>
</tr>
<tr>
<td>group</td>
<td>
The group name of the benchmark used for grouping benchmarks together.<br/><br/>Example:<br/>
<blockquote>group imdb</blockquote>
</td>
</tr>
<tr>
<td>subgroup</td>
<td>
The sub group name of the benchmark used for filtering to a specific sub group.<br/><br/>Example:<br/>
<blockquote>subgroup window</blockquote>
</td>
</tr>
<tr>
<td>load</td>
<td>
The load directive called during initialization of the benchmark. If a path to a file is provided on the same
line as the load directive that path will be parsed and any sql statements in that file will be executed during
initialization. If no path is specified the next line is required to be the sql statement to execute. <br/> <br/> The
load directive (including any following sql statement) must be followed by a blank line. <br/><br/>Example:<br/>
<blockquote>load sql_benchmarks/h2o/init/load_${BENCH_SUBGROUP:-groupby}_${BENCH_SIZE:-small}_${BENCH_FILE_TYPE:-csv}.sql</blockquote>
or
<blockquote>
load<br/>
CREATE TABLE test AS (SELECT value as key FROM range(1000000) ORDER BY value);
</blockquote>
</td>
</tr>
<tr>
<td>init</td>
<td>
The init directive is called after the load directive prior to benchmark execution. If a path to a file is
provided on the same line as the init directive that path will be parsed and any sql statements in that file will be
executed during the benchmark initialization. If no path is specified the next line is required to be the sql statement
to execute.<br/><br/> The init directive (including any following sql statement) must be followed by a blank
line.<br/><br/>Example:<br/>
<blockquote>
init<br/>
set datafusion.execution.parquet.binary_as_string = true;
</blockquote>
</td>
</tr>
<tr>
<td>run</td>
<td>
The run directive called during execution of the benchmark. If a path to a file is provided on the same line as
the run directive that path will be parsed and any sql statements in that file will be executed during the benchmark
run. If no path is specified the next line is required to be the sql statement to execute. <br/><br/> Multiple
statements are allowed within a single run directive, however a benchmark file may contain only one run directive. When
when persisting or validating results, only the last `SELECT` or `WITH` statement from that run directive will be used
for comparison. <br/><br/> The run directive (including any following sql statement) must be
followed by a blank line.<br/><br/>Example:<br/>
<blockquote>run sql_benchmarks/imdb/queries/${QUERY_NUMBER_PADDED}.sql</blockquote>
</td>
</tr>
<tr>
<td>cleanup</td>
<td>
The cleanup directive is called after all other directives and can be used to cleanup after the benchmark -
e.g. to drop tables. If a path to a file is provided on the same line as the cleanup directive that path will be parsed
and any sql statements in that file will be executed during cleanup. If no path is specified the next line is
required to be the sql statement to execute. <br/> <br/> The cleanup directive (including any following sql statement)
must be followed by a blank line. <br/><br/>Example:<br/>
<blockquote>
cleanup<br/>
DROP TABLE test;
</blockquote>
</td>
</tr>
<tr>
<td>expect_plan</td>
<td>
The expect_plan directive will check the physical plan for the string provided on the same line. This
can be used to validate that a particular join was used. The plan is rendered as <code>EXPLAIN</code>
displays it, and the check runs once per benchmark, not once per iteration, so it adds no cost to the
measured region. <br/> <br/> Example:<br/>
<blockquote>expect_plan NestedLoopJoinExec</blockquote>
</td>
</tr>
<tr>
<td>assert</td>
<td>
The assert directive is run between the init and run directives and can be used to validate system state correctness
prior to running the benchmark sql. The format is
<blockquote>
assert II<br/>
SELECT name, value = 3 FROM information_schema.df_settings WHERE name IN ('datafusion.execution.target_partitions', 'datafusion.execution.planning_concurrency');<br/>
----<br/>
datafusion.execution.planning_concurrency true<br/>
datafusion.execution.target_partitions true<br/>
</blockquote>
The number of I's corresponds to the number of columns in the result. The expected results can be either tab delimited
or pipe delimited.
</td>
</tr>
<tr>
<td>result_query</td>
<td>
The result_query directive is run during the verify phase and can be used to verify a different set of results than any
that might come from queries executed from the `run` directive. The format is the same as the `assert` directive
above.<br/><br/>Example:
<blockquote>
result_query III<br/>
SELECT COUNT(DISTINCT id2), SUM(r4), COUNT(*) FROM answer;<br/>
----<br/>
123 345 45
</blockquote>
Note that the results of the run query are not automatically stored into a table in datafusion. If you want to
verify a result from queries executed from the `run` directive those queries will have to be saved to a table directly
using `CREATE TABLE AS (..)` or similar.
</td>
</tr>
<tr>
<td>result</td>
<td>
The result directive declares the expected result file used during verification. A path to a file is required on the
same line as the result directive. The file is parsed only during verification, and must be a pipe-delimited CSV file
with a header row. During verification, these expected rows are compared with the rows produced by the last saved
`SELECT` or `WITH` statement from the `run` directive. <br/><br/>Example:<br/>
<blockquote>
result sql_benchmarks/imdb/results/${QUERY_NUMBER_PADDED}.csv
</blockquote>
</td>
</tr>
<tr>
<td>template</td>
<td>
The template directive allows for inclusion of another file in a benchmark file. A path to a file is
required on the same line as the template directive which will be parsed as a benchmark file. Parameters can be passed
to the template file using the format `KEY=value`, one per line after the template directive followed by a blank line.
<br/><br/>Example:<br/>
<blockquote>
template sql_benchmarks/smj/smj.benchmark.template<br/>
QUERY_NUMBER=1<br/>
QUERY_NUMBER_PADDED=01
</blockquote>
</td>
</tr>
<tr>
<td>include</td>
<td>The include directive is similar to the template directive except that it does not support parameters.</td>
</tr>
<tr>
<td>echo</td>
<td>
The echo directive allows for echoing a string to stdout during the execution of the benchmark and may be useful for
debugging.<br/><br/>Example:<br/>
<blockquote>
echo The value for batch size is ${BATCH_SIZE:-8192}
</blockquote>
</td>
</tr>
</table>
# Extending an existing benchmark suite
If you want to add a new query:
* Create a new qXX.sql in the corresponding queries folder of the benchmark.
* Add a new qXX.benchmark that references the appropriate template (clickbench.benchmark.template,
h2o.benchmark.template,
etc.).
* (Optional) Add a new entry to the suite’s load script if the data set is different.
* (Optional) Manually create a result csv to be compared against benchmark results during verification.
# Adding a new benchmark suite
* Create a new directory named for the new benchmark suite.
* Within there create a `<name>.benchmark` for each individual benchmark.
* Populate the benchmark with directives as described above. Use the other benchmarks as examples for standardization.
* No rust files need to be updated to run the new benchmark suite.