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:

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.

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.

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:

Each [[options]] table has these fields:

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:

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:

Example: run the H2O window benchmarks on the small CSV data files:

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:

How it works

The runner executes SQL benchmarks with its basic runner by default. Pass --criterion to gather statistics with 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

The name of the benchmark. This will be used as part of the display name used by criterion.

Example:

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.

The group name of the benchmark used for grouping benchmarks together.

Example:

The sub group name of the benchmark used for filtering to a specific sub group.

Example:

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.

The load directive (including any following sql statement) must be followed by a blank line.

Example:

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.

The init directive (including any following sql statement) must be followed by a blank line.

Example:

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.

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.

The run directive (including any following sql statement) must be followed by a blank line.

Example:

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.

The cleanup directive (including any following sql statement) must be followed by a blank line.

Example:

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 EXPLAIN displays it, and the check runs once per benchmark, not once per iteration, so it adds no cost to the measured region.

Example:

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

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.

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.

Example:

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.

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.

Example:

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.

Example:

The echo directive allows for echoing a string to stdout during the execution of the benchmark and may be useful for debugging.

Example:

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.