Skip to content

Commit 249b599

Browse files
adriangbclaude
andauthored
test: benchmarks and SLT tests for push-down TopK through join (apache#22760)
## Which issue does this PR close? - Relates to apache#11900 ## Rationale for this change This splits the test and benchmark scaffolding out of apache#21621 so the `PushDownTopKThroughJoin` optimizer rule itself can be reviewed in isolation, with a small, focused diff. The benchmark and SLT files here do not depend on the rule. They are committed first so that: 1. The benchmark can measure the rule's effect against a baseline that does not register it. 2. The follow-up rule PR's diff shows exactly which plans change, since the EXPLAIN plans here capture the current (pre-rule) behavior. ## What changes are included in this PR? - A `push_down_topk` benchmark (`dfbench push-down-topk`) that runs `ORDER BY <cols> LIMIT N` queries over outer joins against TPC-H `customer`/`orders`/`nation`, plus its query files under `benchmarks/queries/push_down_topk/`. - `push_down_topk_through_join.slt` covering the scenarios the rule handles: preserved-side sort keys, ineligible join types (inner/full/semi/anti), `ON`-clause filters, projection and `SubqueryAlias` resolution, existing child sorts, ties, multi-level joins, `OFFSET`, and volatile expressions. The EXPLAIN plans assert current behavior (TopK not yet pushed through the join). The follow-up PR that adds the rule updates those plans in place; the query-result checks hold regardless of whether the rule is enabled. The new optimizer rule, the `push_down_limit.rs` changes, and the `optimizer_rule_reference.md` update from apache#21621 are intentionally left for the follow-up PR. ## Are these changes tested? Yes — this PR is the tests. `push_down_topk_through_join.slt` passes against `main`, and the benchmark binary compiles and runs. ## Are there any user-facing changes? No. No API changes; only new benchmark and test files plus benchmark CLI wiring. --------- Co-authored-by: Claude Opus 4.8 (1M context) <[email protected]>
1 parent b7761dc commit 249b599

10 files changed

Lines changed: 1268 additions & 0 deletions

File tree

‎benchmarks/bench.sh‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -99,6 +99,7 @@ tpcds: TPCDS inspired benchmark on Scale Factor (SF) 1 (~1GB),
9999
sort_tpch: Benchmark of sorting speed for end-to-end sort queries on TPC-H dataset (SF=1)
100100
sort_tpch10: Benchmark of sorting speed for end-to-end sort queries on TPC-H dataset (SF=10)
101101
topk_tpch: Benchmark of top-k (sorting with limit) queries on TPC-H dataset (SF=1)
102+
push_down_topk: Benchmark of ORDER BY ... LIMIT over outer joins on TPC-H dataset (SF=1) — exercises pushing TopK through a join
102103
external_aggr: External aggregation benchmark on TPC-H dataset (SF=1)
103104
wide_schema: Small-projection queries on a wide synthetic dataset (1024 cols × 256 files) — measures per-file metadata overhead
104105
(runs both 'wide' and 'narrow' subgroups: narrow is an internal baseline; the wide-vs-narrow ratio is the signal)
@@ -341,6 +342,10 @@ main() {
341342
# same data as for tpch
342343
data_tpch "1" "parquet"
343344
;;
345+
push_down_topk)
346+
# same data as for tpch
347+
data_tpch "1" "parquet"
348+
;;
344349
nlj)
345350
# nlj uses range() function, no data generation needed
346351
echo "NLJ benchmark does not require data generation"
@@ -561,6 +566,9 @@ main() {
561566
topk_tpch)
562567
run_topk_tpch
563568
;;
569+
push_down_topk)
570+
run_push_down_topk
571+
;;
564572
nlj)
565573
run_nlj
566574
;;
@@ -778,6 +786,20 @@ run_wide_schema() {
778786
bash -c "$SQL_CARGO_COMMAND"
779787
}
780788

789+
# Runs the push_down_topk benchmark (ORDER BY ... LIMIT over outer joins).
790+
# Reuses the TPC-H parquet data, so it needs `./bench.sh data tpch` (or
791+
# `data push_down_topk`) first.
792+
run_push_down_topk() {
793+
echo "Running push_down_topk benchmark..."
794+
795+
debug_run env BENCH_NAME=push_down_topk \
796+
BENCH_SIZE="1" \
797+
DATA_DIR="${DATA_DIR}" \
798+
SIMULATE_LATENCY="${SIMULATE_LATENCY}" \
799+
${QUERY:+BENCH_QUERY="${QUERY}"} \
800+
bash -c "$SQL_CARGO_COMMAND"
801+
}
802+
781803
# Runs the tpch in memory (needs tpch parquet data)
782804
run_tpch_mem() {
783805
SCALE_FACTOR=$1

‎benchmarks/sql_benchmarks/README.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ in the community:
3636
| `hj` | Hash join benchmark |
3737
| `imdb` | IMDb benchmark |
3838
| `nlj` | Nested‑loop join benchmark |
39+
| `push_down_topk` | `ORDER BY ... LIMIT` over outer joins (TPC-H data); exercises pushing a TopK through a join |
3940
| `smj` | Sort‑merge join benchmark |
4041
| `sort tpch` | Sorting benchmarks against the TPC-H lineitem table |
4142
| `taxi` | NYC taxi dataset benchmark |
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
-- LEFT JOIN, ORDER BY a column from the preserved (left) side, small LIMIT.
2+
-- Canonical push_down_topk_through_join case: the TopK can be duplicated
3+
-- below the join over the customer scan so only the top 10 rows (by
4+
-- c_acctbal) are joined against orders.
5+
6+
name Q01
7+
group push_down_topk
8+
9+
load sql_benchmarks/push_down_topk/init/load.sql
10+
11+
assert I
12+
SELECT COUNT(*) > 0 from customer;
13+
----
14+
true
15+
16+
run
17+
SELECT c_custkey, c_acctbal
18+
FROM customer LEFT JOIN orders ON c_custkey = o_custkey
19+
ORDER BY c_acctbal
20+
LIMIT 10;
21+
22+
cleanup sql_benchmarks/push_down_topk/init/cleanup.sql
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
-- RIGHT JOIN, ORDER BY a column from the preserved (right) side.
2+
-- Symmetric to Q01: the TopK is pushed below the join over the orders
3+
-- scan (the right/preserved side).
4+
5+
name Q02
6+
group push_down_topk
7+
8+
load sql_benchmarks/push_down_topk/init/load.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 from orders;
12+
----
13+
true
14+
15+
run
16+
SELECT o_orderkey, o_totalprice
17+
FROM customer RIGHT JOIN orders ON c_custkey = o_custkey
18+
ORDER BY o_totalprice
19+
LIMIT 10;
20+
21+
cleanup sql_benchmarks/push_down_topk/init/cleanup.sql
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
-- LEFT JOIN, multi-column ORDER BY (both columns from the preserved side).
2+
-- All sort exprs must come from the preserved side for the rule to fire;
3+
-- this checks that multi-column sorts are still pushed.
4+
5+
name Q03
6+
group push_down_topk
7+
8+
load sql_benchmarks/push_down_topk/init/load.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 from customer;
12+
----
13+
true
14+
15+
run
16+
SELECT c_custkey, c_acctbal, c_nationkey
17+
FROM customer LEFT JOIN orders ON c_custkey = o_custkey
18+
ORDER BY c_acctbal, c_nationkey
19+
LIMIT 100;
20+
21+
cleanup sql_benchmarks/push_down_topk/init/cleanup.sql
Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
-- CROSS JOIN, ORDER BY a column from one side.
2+
-- Cross joins preserve every row from both sides; the rule pushes the
3+
-- TopK below the join over the side referenced by ORDER BY.
4+
5+
name Q04
6+
group push_down_topk
7+
8+
load sql_benchmarks/push_down_topk/init/load.sql
9+
10+
assert I
11+
SELECT COUNT(*) > 0 from customer;
12+
----
13+
true
14+
15+
run
16+
SELECT c_custkey, c_acctbal
17+
FROM customer CROSS JOIN nation
18+
ORDER BY c_acctbal
19+
LIMIT 10;
20+
21+
cleanup sql_benchmarks/push_down_topk/init/cleanup.sql
Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
-- Negative case: ORDER BY references the probe (non-preserved) side.
2+
-- The rule MUST NOT fire here -- orders is the right side of a LEFT JOIN
3+
-- so it isn't preserved (rows can be NULL when there's no match), and
4+
-- pushing a TopK onto orders would change semantics. Included so the
5+
-- bench captures the no-pushdown path alongside the positive cases.
6+
7+
name Q05
8+
group push_down_topk
9+
10+
load sql_benchmarks/push_down_topk/init/load.sql
11+
12+
assert I
13+
SELECT COUNT(*) > 0 from orders;
14+
----
15+
true
16+
17+
run
18+
SELECT c_custkey, o_totalprice
19+
FROM customer LEFT JOIN orders ON c_custkey = o_custkey
20+
ORDER BY o_totalprice
21+
LIMIT 10;
22+
23+
cleanup sql_benchmarks/push_down_topk/init/cleanup.sql
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
DROP TABLE IF EXISTS customer;
2+
3+
DROP TABLE IF EXISTS orders;
4+
5+
DROP TABLE IF EXISTS nation;
Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
CREATE EXTERNAL TABLE customer STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/customer/customer.1.parquet';
2+
3+
CREATE EXTERNAL TABLE orders STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/orders/orders.1.parquet';
4+
5+
CREATE EXTERNAL TABLE nation STORED AS PARQUET LOCATION '${DATA_DIR:-data}/tpch_sf${BENCH_SIZE:-1}/nation/nation.1.parquet';

0 commit comments

Comments
 (0)