Skip to content

Commit 1d1161f

Browse files
Add Join Order Benchmark (JOB) to vortex-bench (#10484)
## Summary Adds the [Join Order Benchmark](https://www.vldb.org/pvldb/vol9/p204-leis.pdf) (Leis et al., VLDB 2015) as a new `job` suite: 113 join-heavy queries over the real 21-table IMDB dataset. It complements TPC-H/TPC-DS with a join-ordering and string-predicate workload over correlated real-world data.
1 parent 5455409 commit 1d1161f

125 files changed

Lines changed: 4251 additions & 32 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎.agents/skills/bench-performance/SKILL.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,7 @@ Do not wait for a deep code read before showing benchmark comparisons or first s
3939

4040
2. Identify the exact benchmark target:
4141

42-
- suite: `tpch`, `tpcds`, `clickbench`, `fineweb`, `gh-archive`, `polarsignals`,
42+
- suite: `tpch`, `tpcds`, `clickbench`, `fineweb`, `gh-archive`, `job`, `polarsignals`,
4343
`public-bi`, or `statpopgen`;
4444
- query number(s), for example `-q <query>`;
4545
- engine/format target(s), for example `datafusion:vortex` versus `datafusion:parquet`;

‎bench-orchestrator/README.md‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@ vx-bench run <benchmark> [options]
4141

4242
**Arguments:**
4343

44-
- `benchmark`: Benchmark suite to run (`appian`, `tpch`, `tpcds`, `clickbench`, `fineweb`, `gh-archive`, `polarsignals`, `public-bi`, `statpopgen`)
44+
- `benchmark`: Benchmark suite to run (`appian`, `tpch`, `tpcds`, `clickbench`, `fineweb`, `gh-archive`, `job`, `polarsignals`, `public-bi`, `statpopgen`)
4545

4646
**Options:**
4747

@@ -79,7 +79,7 @@ to keep benchmark coverage in Python instead of copying large JSON matrices betw
7979
```bash
8080
vx-bench matrix # list available presets
8181
vx-bench matrix develop # emit compact JSON
82-
vx-bench matrix pr-all # emit the combined focused PR coverage
82+
vx-bench matrix pr-all # emit the union of pr, pr-compact and pr-full
8383
vx-bench matrix pr-full # emit full pull-request coverage
8484
vx-bench matrix nightly --pretty
8585
```
@@ -161,7 +161,8 @@ vx-bench clean --older-than "30 days" [options]
161161
CI benchmark coverage lives in `bench_orchestrator/ci_matrix/catalog.py`. This is the only file to
162162
edit when changing what runs in the `develop`, `pr`, `pr-compact`, `pr-all`, `pr-full`, or
163163
`nightly` presets. The remaining modules in `ci_matrix` model, validate, and render that catalog as
164-
the JSON expected by GitHub Actions.
164+
the JSON expected by GitHub Actions. `pr-all` is not declared per benchmark: it is derived as the
165+
union of each benchmark's `pr`, `pr-compact`, and `pr-full` coverage.
165166

166167
When adding coverage, update the benchmark declarations and the expected preset membership in
167168
`tests/test_matrix.py`.

‎bench-orchestrator/bench_orchestrator/ci_matrix/catalog.py‎

Lines changed: 23 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -7,15 +7,18 @@
77
validate, and render these declarations.
88
"""
99

10+
from dataclasses import replace
11+
1012
from ..config import Benchmark, Format
1113
from .model import BenchmarkCase, Catalog, Coverage, Storage
14+
from .render import merge_coverage
1215
from .targets import df, duck
1316

1417
PRESETS = {
1518
"develop": "Every regular SQL benchmark at full target coverage.",
1619
"pr": "The quicker pull-request SQL benchmark matrix (action/bench-sql).",
1720
"pr-compact": "Pull-request SQL benchmarks for Vortex Compact plus Parquet controls.",
18-
"pr-all": "The PR and PR Compact benchmark matrices plus extended-only default targets (action/bench-all).",
21+
"pr-all": "The union of the pr, pr-compact and pr-full matrices (action/bench-all).",
1922
"pr-full": "Every regular SQL benchmark at full PR target coverage (action/bench-sql-extended).",
2023
"nightly": "Large-scale SF=100 TPC-H on NVMe and S3 at default targets.",
2124
}
@@ -71,6 +74,7 @@
7174
STANDARD_WITH_DUCKDB = Coverage(STANDARD_WITH_DUCKDB_TARGETS)
7275
DATAFUSION_VORTEX = Coverage(DATAFUSION_VORTEX_TARGETS)
7376
COMPACT = Coverage(COMPACT_TARGETS)
77+
JOB_DUCKDB = Coverage(duck(Format.PARQUET, Format.VORTEX, Format.DUCKDB))
7478

7579
# Concrete benchmark cases
7680

@@ -82,7 +86,6 @@
8286
runs={
8387
"pr": DEFAULT,
8488
"pr-compact": COMPACT,
85-
"pr-all": STANDARD,
8689
"pr-full": DEFAULT_WITH_DUCKDB_PR_FULL,
8790
"develop": FULL_LOCAL,
8891
},
@@ -93,7 +96,6 @@
9396
name="Clickbench Sorted on NVME",
9497
runs={
9598
"pr-compact": COMPACT,
96-
"pr-all": STANDARD,
9799
"pr-full": DEFAULT_WITH_DUCKDB_PR_FULL,
98100
"develop": FULL_LOCAL,
99101
},
@@ -107,7 +109,6 @@
107109
runs={
108110
"pr": DEFAULT,
109111
"pr-compact": COMPACT,
110-
"pr-all": STANDARD,
111112
"pr-full": FULL_PR,
112113
"develop": FULL_LOCAL,
113114
},
@@ -124,7 +125,6 @@
124125
runs={
125126
"pr": DEFAULT,
126127
"pr-compact": COMPACT,
127-
"pr-all": STANDARD,
128128
"pr-full": STANDARD,
129129
"develop": STANDARD,
130130
},
@@ -138,7 +138,6 @@
138138
runs={
139139
"pr": DEFAULT,
140140
"pr-compact": COMPACT,
141-
"pr-all": STANDARD,
142141
"pr-full": FULL_PR,
143142
"develop": FULL_LOCAL,
144143
},
@@ -154,7 +153,6 @@
154153
remote_key="tpch/10.0",
155154
runs={
156155
"pr-compact": COMPACT,
157-
"pr-all": COMPACT,
158156
"pr-full": STANDARD,
159157
"develop": STANDARD,
160158
},
@@ -184,7 +182,6 @@
184182
runs={
185183
"pr": DEFAULT,
186184
"pr-compact": COMPACT,
187-
"pr-all": STANDARD,
188185
"pr-full": STANDARD_WITH_DUCKDB,
189186
"develop": STANDARD_WITH_DUCKDB,
190187
},
@@ -197,7 +194,6 @@
197194
local_dir="vortex-bench/data/statpopgen",
198195
runs={
199196
"pr-compact": COMPACT,
200-
"pr-all": STANDARD,
201197
"pr-full": STANDARD,
202198
"develop": STANDARD,
203199
},
@@ -210,7 +206,6 @@
210206
runs={
211207
"pr": DEFAULT,
212208
"pr-compact": COMPACT,
213-
"pr-all": STANDARD,
214209
"pr-full": STANDARD,
215210
"develop": STANDARD,
216211
},
@@ -225,7 +220,6 @@
225220
remote_key="fineweb",
226221
runs={
227222
"pr-compact": COMPACT,
228-
"pr-all": STANDARD,
229223
"pr-full": STANDARD,
230224
"develop": STANDARD,
231225
},
@@ -237,7 +231,6 @@
237231
scale_factor=1,
238232
runs={
239233
"pr": DATAFUSION_VORTEX,
240-
"pr-all": DATAFUSION_VORTEX,
241234
"pr-full": DATAFUSION_VORTEX,
242235
"develop": DATAFUSION_VORTEX,
243236
},
@@ -249,11 +242,17 @@
249242
iterations=10,
250243
runs={
251244
"pr-compact": COMPACT,
252-
"pr-all": COMPACT,
253245
"pr-full": DEFAULT_WITH_DUCKDB_PR_FULL,
254246
"develop": STANDARD_WITH_DUCKDB,
255247
},
256248
),
249+
BenchmarkCase(
250+
id="job-nvme",
251+
benchmark=Benchmark.JOB,
252+
name="JOB on NVME",
253+
# DuckDB only: DataFusion spends minutes per pass on the 17x queries' join orders.
254+
runs={"pr-full": JOB_DUCKDB},
255+
),
257256
BenchmarkCase(
258257
id="vortex-queries",
259258
benchmark=Benchmark.VORTEX_QUERIES,
@@ -266,4 +265,14 @@
266265
),
267266
)
268267

269-
CATALOG = Catalog(presets=PRESETS, benchmarks=BENCHMARKS)
268+
PR_ALL_SOURCES = ("pr", "pr-compact", "pr-full")
269+
270+
271+
def _with_pr_all(benchmark: BenchmarkCase) -> BenchmarkCase:
272+
sources = [benchmark.runs[preset] for preset in PR_ALL_SOURCES if preset in benchmark.runs]
273+
if not sources:
274+
return benchmark
275+
return replace(benchmark, runs={**benchmark.runs, "pr-all": merge_coverage(sources)})
276+
277+
278+
CATALOG = Catalog(presets=PRESETS, benchmarks=tuple(map(_with_pr_all, BENCHMARKS)))

‎bench-orchestrator/bench_orchestrator/ci_matrix/render.py‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@
55

66
from ..config import Format
77
from .model import BenchmarkCase, Catalog, Coverage
8+
from .targets import TargetSet
89
from .validate import validate_catalog
910

1011
_NOT_GENERATED = frozenset({Format.LANCE})
@@ -26,6 +27,16 @@ def _data_formats(coverage: Coverage) -> list[Format]:
2627
return [fmt for fmt in _FORMAT_ORDER if fmt in present and fmt not in _NOT_GENERATED]
2728

2829

30+
def merge_coverage(coverages: list[Coverage]) -> Coverage:
31+
"""Union several coverages: every target and every generated data format."""
32+
targets = TargetSet()
33+
formats: set[Format] = set()
34+
for coverage in coverages:
35+
targets = targets | coverage.targets
36+
formats.update(_data_formats(coverage))
37+
return Coverage(targets, data_formats=tuple(fmt for fmt in _FORMAT_ORDER if fmt in formats))
38+
39+
2940
def _matrix_entry(benchmark: BenchmarkCase, coverage: Coverage) -> dict[str, object]:
3041
entry: dict[str, object] = {
3142
"id": benchmark.id,

‎bench-orchestrator/bench_orchestrator/config.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,7 @@ class Benchmark(Enum):
5858
CLICKBENCH_SORTED = "clickbench-sorted"
5959
FINEWEB = "fineweb"
6060
GHARCHIVE = "gh-archive"
61+
JOB = "job"
6162
POLARSIGNALS = "polarsignals"
6263
PUBLIC_BI = "public-bi"
6364
STATPOPGEN = "statpopgen"

‎bench-orchestrator/tests/test_matrix.py‎

Lines changed: 8 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@
3636
COMPACT_IDS = tuple(
3737
benchmark_id for benchmark_id in REGULAR_IDS if benchmark_id not in {"polarsignals", "vortex-queries"}
3838
)
39-
PR_ALL_IDS = tuple(benchmark_id for benchmark_id in REGULAR_IDS if benchmark_id != "vortex-queries")
39+
PR_FULL_IDS = (*REGULAR_IDS[:-1], "job-nvme", "vortex-queries")
4040
EXPECTED_IDS = {
4141
"develop": REGULAR_IDS,
4242
"pr": tuple(
@@ -53,8 +53,8 @@
5353
}
5454
),
5555
"pr-compact": COMPACT_IDS,
56-
"pr-all": PR_ALL_IDS,
57-
"pr-full": REGULAR_IDS,
56+
"pr-all": PR_FULL_IDS,
57+
"pr-full": PR_FULL_IDS,
5858
"nightly": ("tpch-nvme", "tpch-s3"),
5959
}
6060

@@ -102,16 +102,15 @@ def test_pr_target_selection() -> None:
102102
assert set(cast("list[str]", entry["data_formats"])) == {"parquet", "vortex-compact"}
103103

104104

105-
def test_pr_all_covers_focused_presets() -> None:
106-
pr = {entry["id"]: entry for entry in _entries("pr")}
107-
pr_compact = {entry["id"]: entry for entry in _entries("pr-compact")}
105+
def test_pr_all_is_union_of_pr_compact_and_full() -> None:
106+
sources = [{entry["id"]: entry for entry in _entries(preset)} for preset in ("pr", "pr-compact", "pr-full")]
108107
pr_all = {entry["id"]: entry for entry in _entries("pr-all")}
109108

110-
assert set(pr_all) == set(pr) | set(pr_compact)
109+
assert set(pr_all) == set().union(*sources)
111110
for benchmark_id, entry in pr_all.items():
112-
for preset in (pr, pr_compact):
111+
assert _targets(entry) == set().union(*(_targets(p[benchmark_id]) for p in sources if benchmark_id in p))
112+
for preset in sources:
113113
if source := preset.get(benchmark_id):
114-
assert _targets(source) <= _targets(entry)
115114
assert set(cast("list[str]", source["data_formats"])) <= set(cast("list[str]", entry["data_formats"]))
116115

117116

‎docs/developer-guide/benchmarking.md‎

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -264,16 +264,15 @@ Benchmarks run automatically on all commits to `develop` and can be run on-deman
264264
- **GPU compression** -- `action/bench-gpu-compress` runs the allow-listed Vortex decompression
265265
cases on a GPU runner.
266266
- **SQL** -- `action/bench-sql` runs the `pr` preset, a quick core subset of the SQL benchmarks
267-
that excludes `vortex-compact`, Clickbench Sorted, Appian, statpopgen, FineWeb on S3, TPC-H SF=10
268-
on S3, and Vortex queries.
267+
that excludes `vortex-compact`, Clickbench Sorted, Appian, JOB, statpopgen, FineWeb on S3,
268+
TPC-H SF=10 on S3, and Vortex queries.
269269
- **SQL Extended** -- `action/bench-sql-extended` runs the `pr-full` preset: every regular SQL
270270
benchmark, including `vortex-compact` and DuckDB-format targets.
271271
- **SQL Compact** -- `action/bench-sql-compact` runs the `pr-compact` preset, which benchmarks
272272
`vortex-compact` plus Parquet control rows used to distinguish code changes from runner drift.
273273
- **All CPU benchmarks** -- `action/bench-all` runs random access, compression, string encoding,
274-
and the `pr-all` SQL preset, which combines the `pr` and `pr-compact` coverage, plus the
275-
default targets of Clickbench Sorted, statpopgen, and FineWeb on S3, without repeating shared
276-
jobs. Do not combine it with other benchmark labels;
274+
and the `pr-all` SQL preset, the union of the `pr`, `pr-compact`, and `pr-full` coverage, without
275+
repeating shared jobs. Do not combine it with other benchmark labels;
277276
GPU compression is the only exception.
278277

279278
All CI benchmarks run on dedicated instances with the `release_debug` profile and

‎vortex-bench/sql/job/01a.sql‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
SELECT MIN(mc.note) AS production_note,
2+
MIN(t.title) AS movie_title,
3+
MIN(t.production_year) AS movie_year
4+
FROM company_type AS ct,
5+
info_type AS it,
6+
movie_companies AS mc,
7+
movie_info_idx AS mi_idx,
8+
title AS t
9+
WHERE ct.kind = 'production companies'
10+
AND it.info = 'top 250 rank'
11+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
12+
AND (mc.note LIKE '%(co-production)%'
13+
OR mc.note LIKE '%(presents)%')
14+
AND ct.id = mc.company_type_id
15+
AND t.id = mc.movie_id
16+
AND t.id = mi_idx.movie_id
17+
AND mc.movie_id = mi_idx.movie_id
18+
AND it.id = mi_idx.info_type_id;
19+

‎vortex-bench/sql/job/01b.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
SELECT MIN(mc.note) AS production_note,
2+
MIN(t.title) AS movie_title,
3+
MIN(t.production_year) AS movie_year
4+
FROM company_type AS ct,
5+
info_type AS it,
6+
movie_companies AS mc,
7+
movie_info_idx AS mi_idx,
8+
title AS t
9+
WHERE ct.kind = 'production companies'
10+
AND it.info = 'bottom 10 rank'
11+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
12+
AND t.production_year BETWEEN 2005 AND 2010
13+
AND ct.id = mc.company_type_id
14+
AND t.id = mc.movie_id
15+
AND t.id = mi_idx.movie_id
16+
AND mc.movie_id = mi_idx.movie_id
17+
AND it.id = mi_idx.info_type_id;
18+

‎vortex-bench/sql/job/01c.sql‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
SELECT MIN(mc.note) AS production_note,
2+
MIN(t.title) AS movie_title,
3+
MIN(t.production_year) AS movie_year
4+
FROM company_type AS ct,
5+
info_type AS it,
6+
movie_companies AS mc,
7+
movie_info_idx AS mi_idx,
8+
title AS t
9+
WHERE ct.kind = 'production companies'
10+
AND it.info = 'top 250 rank'
11+
AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
12+
AND (mc.note LIKE '%(co-production)%')
13+
AND t.production_year >2010
14+
AND ct.id = mc.company_type_id
15+
AND t.id = mc.movie_id
16+
AND t.id = mi_idx.movie_id
17+
AND mc.movie_id = mi_idx.movie_id
18+
AND it.id = mi_idx.info_type_id;
19+

0 commit comments

Comments
 (0)