Skip to content

Commit 5a2000a

Browse files
adriangbclaude
andauthored
bench: add projection_subquery SQL benchmark suite (apache#25346)
## Which issue does this PR close? - This PR closes no issue. It is related to apache#25341 and it prepares the A/B measurement for apache#25338. ## Rationale for this change An `IN`, `NOT IN` or `EXISTS` subquery in the `SELECT` list is much slower than the same subquery in a `WHERE` clause. A projected `IN` must return `NULL`, and not `false`, when there is no match and the inner side holds a `NULL`. The decorrelation therefore turns one projected `IN` into more than one mark join. A mark join with no hashable join predicate runs as a nested-loop join, so the cost grows with the outer row count times the inner row count. There is no benchmark suite that measures this shape today. The benchmark bot compares a pull request against its merge base. It can only report a change if the suite is present on both sides. The suite must therefore land on `main` first, before the fix is measured with it. ## What changes are included in this PR? - A new declarative SQL benchmark suite, `projection_subquery`, with seven queries. Each query is one shape: - `q01_in_bare`: bare uncorrelated `IN`. - `q02_in_coalesce`: the same `IN` wrapped in `COALESCE`, which is the common way to use the result. - `q03_in_correlated_eq`: `IN` correlated on an equality. - `q04_in_two_columns`: two independent `IN` subqueries in one `SELECT` list. - `q05_not_in_bare`: `NOT IN`. - `q06_exists_correlated`: correlated `EXISTS`, which has no `NULL` result and needs only one mark join. - `q07_in_correlated_residual`: `IN` correlated on `<`. There is no equality to hash on, so this query keeps the nested-loop plan. It is the control: its time must not change when the hashable shapes get faster. - Two small integer tables built inline by the suite load SQL, so the suite needs no data step. Every 97th inner key is `NULL`, which keeps three-valued logic in play. Set `PSQ_ROWS` to change the row count in each table. The default is 30000. - An aggregate over the projected boolean in every query, so the result is two numbers and the measured cost is the plan and not the size of the output. - Checked-in result files, so `--result-mode validate` also proves that a change to the decorrelation still returns the same rows. The counts hold for the default `PSQ_ROWS`. - `bench.sh` wiring: `./benchmarks/bench.sh data projection_subquery` reports that there is no external data, and `./benchmarks/bench.sh run projection_subquery` runs the suite. - Documentation in `benchmarks/README.md` and in the suite table of `benchmarks/sql_benchmarks/README.md`. ## What is the testing strategy for this PR? This PR adds no product code, so there are no unit tests. It was verified by running the suite. `./benchmarks/bench.sh data projection_subquery` prints the no-data message. `./benchmarks/bench.sh run projection_subquery` builds `cargo bench --bench sql` and completes with a time for each of the seven queries. `--result-mode validate` passes on `main` and on the branch of apache#25338, which shows that both sides return the same rows. Median of five iterations at the default `PSQ_ROWS` of 30000, on one machine: | Query | main | With apache#25338 | | --- | --- | --- | | `q01_in_bare` | 341 ms | 0.8 ms | | `q02_in_coalesce` | 690 ms | 1.1 ms | | `q03_in_correlated_eq` | 2.6 ms | 0.9 ms | | `q04_in_two_columns` | 527 ms | 1.2 ms | | `q05_not_in_bare` | 349 ms | 0.6 ms | | `q06_exists_correlated` | 0.6 ms | 0.4 ms | | `q07_in_correlated_residual` (control) | 66 ms | 63 ms | The control query is unchanged, as expected, because it has no equality to hash on. ## Are there any user-facing changes? No. This PR adds benchmarks only. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-authored-by: Claude Fable 5.1 <[email protected]>
1 parent 0e292dc commit 5a2000a

28 files changed

Lines changed: 237 additions & 0 deletions

‎benchmarks/README.md‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1009,6 +1009,32 @@ Several queries are included to test sort merge joins under various workloads.
10091009

10101010
./bench.sh run smj
10111011
```
1012+
1013+
## Projection Subquery
1014+
1015+
This benchmark measures `IN`, `NOT IN` and `EXISTS` subqueries that sit in the `SELECT` list instead of a filter
1016+
(see <https://github.com/apache/datafusion/issues/25341>).
1017+
1018+
A projected `IN` must return `NULL`, and not `false`, when there is no match and the inner side holds a `NULL`.
1019+
The decorrelation therefore turns one projected `IN` into more than one mark join.
1020+
If a mark join has no hashable join predicate, it runs as a nested-loop join, and the cost grows with the outer row count times the inner row count.
1021+
1022+
Every query projects the boolean subquery result and aggregates it, so the measured cost is the plan and not the size of the output.
1023+
Query `q07` correlates on `<` instead of `=`, so it stays on the nested-loop path.
1024+
It is the control: its time must not change when the hashable shapes get faster.
1025+
1026+
The two tables are built inline by the suite's load SQL, so there is no data step.
1027+
Set `PSQ_ROWS` to change the row count in each table.
1028+
The default is 30,000, which keeps the nested-loop plans at a few hundred milliseconds.
1029+
The checked-in result files hold the counts for that default, so `--result-mode validate` needs it.
1030+
1031+
### Example Run
1032+
1033+
```bash
1034+
# No need to generate data: the suite's load SQL builds the two tables inline
1035+
1036+
./bench.sh run projection_subquery
1037+
```
10121038
## Cancellation
10131039

10141040
Test performance of cancelling queries.

‎benchmarks/bench.sh‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -115,6 +115,10 @@ parquet_row_filter_skip: Per-RG fully-matched RowFilter skip on Parquet (apache/
115115
null_aware_join: Null-aware (NOT IN) hash join micro-benchmarks: uncorrelated, non-equality-correlated and equality-correlated
116116
NOT IN across NULL fractions, to measure the per-pair join-filter work the correlated cases do
117117
(data generated inline by the suite's load SQL from range(); knobs: NAJ_ROWS, NAJ_LARGE_ROWS)
118+
projection_subquery: IN / NOT IN / EXISTS subqueries in the SELECT list (see https://github.com/apache/datafusion/issues/25341); each query projects the
119+
boolean subquery result and aggregates it, so the cost is the decorrelation plan and not the output size
120+
(q07 correlates on '<' instead of '=', so it keeps the nested-loop plan and acts as the control)
121+
(data generated inline by the suite's load SQL; knob: PSQ_ROWS)
118122
119123
# ClickBench Benchmarks
120124
clickbench_1: ClickBench queries against a single parquet file
@@ -279,6 +283,10 @@ main() {
279283
# Data is generated inline by the suite's load SQL from range().
280284
echo "null_aware_join: no external data to generate"
281285
;;
286+
projection_subquery)
287+
# Data is generated inline by the suite's load SQL.
288+
echo "projection_subquery: no external data to generate"
289+
;;
282290
asof_join)
283291
data_asof_join
284292
;;
@@ -530,6 +538,9 @@ main() {
530538
null_aware_join)
531539
run_null_aware_join
532540
;;
541+
projection_subquery)
542+
run_projection_subquery
543+
;;
533544
asof_join)
534545
run_asof_join
535546
;;
@@ -969,6 +980,24 @@ run_null_aware_join() {
969980
bash -c "$SQL_CARGO_COMMAND"
970981
}
971982

983+
# Runs the projection_subquery suite: IN / NOT IN / EXISTS subqueries that sit
984+
# in the SELECT list instead of a filter (see
985+
# https://github.com/apache/datafusion/issues/25341). The load SQL builds the
986+
# two tables inline, so there is no data step. Each query projects the boolean
987+
# subquery result and aggregates it, so the measured cost is the decorrelation
988+
# plan and not the size of the output. Query 07 correlates on '<' instead of
989+
# '=', so it keeps the nested-loop plan and acts as the control.
990+
# Knob (string-substituted into the load SQL, not engine config):
991+
# PSQ_ROWS rows in each of the two tables (default 30_000; the checked-in
992+
# result files hold the counts for that value)
993+
run_projection_subquery() {
994+
echo "Running projection_subquery benchmark (rows=${PSQ_ROWS:-30000})..."
995+
debug_run env BENCH_NAME=projection_subquery \
996+
PSQ_ROWS="${PSQ_ROWS:-30000}" \
997+
${QUERY:+BENCH_QUERY="${QUERY}"} \
998+
bash -c "$SQL_CARGO_COMMAND"
999+
}
1000+
9721001
# Runs the tpch in memory (needs tpch parquet data)
9731002
run_tpch_mem() {
9741003
SCALE_FACTOR=$1

‎benchmarks/sql_benchmarks/README.md‎

Lines changed: 2 additions & 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+
| `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. |
5051

5152
# Running Benchmarks
5253

@@ -174,6 +175,7 @@ Some benchmarks use custom environment variables as outlined below:
174175
| 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` |
175176
| PRED_FILL | Used in the predicate_eval benchmark as the string-column width knob (filler chars per marker). | `30` |
176177
| RG_SIZE | Used in the parquet_row_filter_skip benchmark as the Parquet `max_row_group_size` for the generated datasets. | `1000000` |
178+
| PSQ_ROWS | Used in the projection_subquery benchmark to size the two inline tables. | `30000` |
177179

178180
## How it works
179181

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=01
3+
NAME=q01_in_bare
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=02
3+
NAME=q02_in_coalesce
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=03
3+
NAME=q03_in_correlated_eq
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=04
3+
NAME=q04_in_two_columns
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=05
3+
NAME=q05_not_in_bare
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=06
3+
NAME=q06_exists_correlated
Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
template sql_benchmarks/projection_subquery/projection_subquery.benchmark.template
2+
QPAD=07
3+
NAME=q07_in_correlated_residual

0 commit comments

Comments
 (0)