You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 5a2000a
Browse filesBrowse the repository at this point in the historyBrowse files
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]>
NOT IN across NULL fractions, to measure the per-pair join-filter work the correlated cases do
117
117
(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)
118
122
119
123
# ClickBench Benchmarks
120
124
clickbench_1: ClickBench queries against a single parquet file
@@ -279,6 +283,10 @@ main() {
279
283
# Data is generated inline by the suite's load SQL from range().
280
284
echo"null_aware_join: no external data to generate"
281
285
;;
286
+
projection_subquery)
287
+
# Data is generated inline by the suite's load SQL.
288
+
echo"projection_subquery: no external data to generate"
289
+
;;
282
290
asof_join)
283
291
data_asof_join
284
292
;;
@@ -530,6 +538,9 @@ main() {
530
538
null_aware_join)
531
539
run_null_aware_join
532
540
;;
541
+
projection_subquery)
542
+
run_projection_subquery
543
+
;;
533
544
asof_join)
534
545
run_asof_join
535
546
;;
@@ -969,6 +980,24 @@ run_null_aware_join() {
969
980
bash -c "$SQL_CARGO_COMMAND"
970
981
}
971
982
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
Copy file name to clipboardExpand all lines: benchmarks/sql_benchmarks/README.md
+2Lines changed: 2 additions & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -47,6 +47,7 @@ in the community:
47
47
|`wide_schema`| Small-projection queries on a wide (1024-col, 256-file) synthetic dataset; runs `wide` + `narrow` subgroups for comparison |
48
48
| `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). |
49
49
|`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. |
50
51
51
52
# Running Benchmarks
52
53
@@ -174,6 +175,7 @@ Some benchmarks use custom environment variables as outlined below:
174
175
| 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`|
175
176
| PRED_FILL | Used in the predicate_eval benchmark as the string-column width knob (filler chars per marker). |`30`|
176
177
| 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`|
0 commit comments