adriangb opened a new pull request, #25346: URL: https://github.com/apache/datafusion/pull/25346
## Which issue does this PR close? - This PR closes no issue. It is related to https://github.com/apache/datafusion/issues/25341 and it prepares the A/B measurement for https://github.com/apache/datafusion/pull/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 https://github.com/apache/datafusion/pull/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 https://github.com/apache/datafusion/pull/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) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
