Skip to content

join-order.md

Latest commit

 

History

History
179 lines (134 loc) · 6.73 KB

join-order.md

File metadata and controls

179 lines (134 loc) · 6.73 KB
Raw

Join Order Benchmark

CLI name: joinorder - use benchbox run --benchmark joinorder --scale 1

Query templates: every Join Order Benchmark query

BenchBox's public Join Order Benchmark implementation uses the canonical IMDb 2013 dataset used by the JOB paper, "How Good Are Query Optimizers, Really?" by Leis et al. The benchmark is designed to expose cardinality-estimation and join-order planning behavior across many-table SQL queries.

Data Source

joinorder downloads and verifies the BenchBox Parquet package for joinorder-imdb-2013-v1. The package is derived from the Harvard Dataverse imdb_pg11 archive, DOI 10.7910/DVN/2QYZBT, restored into PostgreSQL and converted to 21 Parquet tables for repeatable benchmark execution.

The source pg_dump matches the Dataverse-published MD5, and the restored source has 21 tables and 74,190,187 rows. The Postgres-to-Parquet conversion is checked for row counts, null/empty-string preservation, integer ranges, and UTF-8 samples.

Rebuilding the archive from source does not reproduce it byte for byte; rebuilt Parquet files can differ in their hashes while holding the same rows. Rebuilds are compared by logical table content instead. The downloaded published archive is verified by its pinned archive_sha256 and per-table Parquet file hashes.

Licensing status is separate from integrity status. The Dataverse record declares the deposit as CC0 1.0, but IMDb's current dataset terms and the JOB paper frame the underlying IMDb data as non-commercial. BenchBox keeps the re-hosted Parquet archive as the default fast path but does not treat it as cleared for broad commercial redistribution.

The benchmark accepts only --scale 1. There is no public small, comparable JOB workload. The old uniformly-random data generator has been renamed to the internal joinorder_synthetic benchmark for loader and schema smoke tests; it is not a substitute for JOB cardinality testing.

First Run

uv run -- benchbox run --platform duckdb --benchmark joinorder --scale 1

On first use BenchBox downloads the compressed archive, verifies the archive hash, extracts the 21 Parquet files, and verifies table-level hashes and row counts from benchbox/core/joinorder/data_manifest.toml.

The manifest hash fields have separate roles:

  • archive_sha256: sha256 of the downloadable .tar.zst, consumed by the downloader before extraction.
  • data_archive_hash: aggregate sha256 over sorted per-table Parquet hashes, used to identify the extracted canonical Parquet file set in result metadata.
  • manifest_hash: sha256 of the stable manifest content with transport-only fields excluded, consumed by manifest parsing before runtime use.

Subsequent runs reuse the verified data under:

<benchmark_runs>/datagen/joinorder_sf1/

<benchmark_runs> is the resolved output root: ../benchmark_runs alongside the checkout by default, or BENCHBOX_OUTPUT_DIR when set. Air-gapped environments can pre-populate that directory with the 21 Parquet files; BenchBox still verifies the manifest before running.

BYO Data Path

The default joinorder path intentionally remains the verified BenchBox-hosted Parquet archive. Environments that require stricter separation from BenchBox's re-hosted archive can instead provide their own canonical Parquet files. Place the 21 files named in benchbox/core/joinorder/data_manifest.toml under:

../benchmark_runs/datagen/joinorder_sf1/

or, when BENCHBOX_OUTPUT_DIR is set:

$BENCHBOX_OUTPUT_DIR/datagen/joinorder_sf1/

On startup BenchBox checks every manifest-owned table file first. If all files are present and their sha256 values match the manifest, the benchmark uses that pre-populated directory and does not download the hosted archive. If any file is missing, BenchBox falls back to the default hosted archive. If a file is present but has the wrong sha256, the run fails before downloading so stale or corrupt BYO data is visible.

Query Set

JoinOrderQueryManager exposes all 113 canonical JOB SQL queries:

from benchbox.core.joinorder.queries import JoinOrderQueryManager

queries = JoinOrderQueryManager()
assert queries.get_query_count() == 113
query_1a = queries.get_query("1a")

The SQL text is imported from the Greg Rahn JOB query corpus pinned by the canonical build. The benchmark translates queries to the engine dialect via SQLGlot when a dialect is requested; platform adapters request translation automatically, so adapter runs execute translated SQL rather than the canonical text.

Python API

from benchbox import JoinOrder

benchmark = JoinOrder(scale_factor=1.0)
data_files = benchmark.generate_data()
schema_sql = benchmark.get_create_tables_sql(dialect="duckdb")
query_sql = benchmark.get_query("1a")

Passing any scale other than 1.0 raises a clear error pointing to joinorder_synthetic for synthetic smoke-test data.

DataFrame Mode

The DataFrame registry exposes all 113 canonical query IDs. The original 13 queries keep their hand-written expression-family and pandas-family translations; the rest use a restricted translator for the canonical JOB SQL shape: comma-join tables, WHERE predicates, equality joins, and top-level MIN(...) projections.

DataFrame joinorder still has a different benchmark interpretation from SQL joinorder: SQL mode stresses optimizer join ordering, while DataFrame mode executes an explicit join sequence and primarily measures multi-join execution through the DataFrame APIs.

License And Attribution

Dataset provenance and redistribution notes live in:

benchbox/core/joinorder/DATA-LICENSE.md

IMDb attribution:

Information courtesy of IMDb (https://www.imdb.com). Used with permission.

Use this dataset for research, database systems evaluation, and query optimizer benchmarking. It is not intended for republication as a general-purpose movie database, and BenchBox does not treat the current converted archive as BenchBox-cleared for broad commercial redistribution. To avoid the re-hosted archive, use the BYO data path.

References

Implementation Files

benchbox/core/joinorder/
|-- benchmark.py          # canonical IMDb 2013 benchmark driver
|-- data_manifest.toml    # archive, table hash, schema, row-count manifest
|-- DATA-LICENSE.md       # dataset provenance and redistribution notes
|-- queries.py            # 113 canonical JOB SQL queries
|-- dataframe_queries.py  # 113 DataFrame query implementations
`-- schema.py             # 21-table JOB schema