Join Order Benchmark API

Tags reference python-api custom-benchmark

Complete Python API reference for BenchBox’s public Join Order Benchmark implementation.

Overview

The public joinorder benchmark uses the canonical IMDb 2013 dataset and query set from the Join Order Benchmark (JOB) paper, “How Good Are Query Optimizers, Really?” by Leis et al. It is intended for cardinality estimation and join-order optimization testing on real-world correlated data.

Current contract:

  • scale_factor must be 1.0. Other values raise ValueError.

  • Data comes from the versioned joinorder-imdb-2013-v1 Parquet package.

  • The package contains 21 IMDb-derived tables and 74,190,187 rows.

  • The first run downloads and verifies the archive, then verifies table hashes and row counts from benchbox/core/joinorder/data_manifest.toml.

  • JoinOrderQueryManager exposes all 113 canonical JOB SQL queries.

  • The old scalable synthetic generator is now the internal joinorder_synthetic benchmark for loader and schema smoke tests.

Quick Start

CLI:

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

Python:

from benchbox import JoinOrder

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

print(len(data_files))
print(query_1a)

First-run data is cached under benchmark_runs/datagen/joinorder_sf1/ by default. If BENCHBOX_OUTPUT_DIR is set, the same relative path is resolved under that root.

JoinOrder Class

class JoinOrder(scale_factor=1.0, output_dir=None, **kwargs)[source]

Bases: BaseBenchmark

Canonical IMDb 2013 Join Order Benchmark implementation.

This class provides access to the canonical JOB data package and complex join queries for cardinality estimation and join-order optimization testing.

Reference: Viktor Leis et al. “How Good Are Query Optimizers, Really?”

__init__(scale_factor=1.0, output_dir=None, **kwargs)[source]

Initialize a Join Order Benchmark instance.

Parameters:

  • scale_factor (float) – Canonical JoinOrder accepts only 1.0.

  • output_dir (str | Path | None) – Directory for verified canonical Parquet files.

  • **kwargs (Any) – Additional implementation-specific options

generate_data()[source]

Ensure canonical JoinOrder data is downloaded and verified.

Returns:

A list of paths to the generated data files

Return type:

list[Path]

get_queries()[source]

Get all Join Order Benchmark queries.

Returns:

A dictionary mapping query IDs to query strings

Return type:

dict[str, str]

get_query(query_id, *, params=None)[source]

Get a specific Join Order Benchmark query.

Parameters:

  • query_id (int | str) – The ID of the query to retrieve

  • params (dict[str, Any] | None) – Optional parameters to customize the query

Returns:

The query string

Raises:

ValueError – If the query_id is invalid

Return type:

str

get_schema(dialect='sqlite')[source]

Get the Join Order Benchmark schema DDL.

Parameters:

dialect (str) – Target SQL dialect

Returns:

DDL statements for creating all tables

Return type:

str

get_create_tables_sql(dialect='standard', tuning_config=None)[source]

Get SQL to create all Join Order Benchmark tables.

Parameters:

  • dialect (str) – SQL dialect to use

  • tuning_config (Any) – Unified tuning configuration for constraint settings

Returns:

SQL script for creating all tables

Return type:

str

DATA_SOURCE_BENCHMARK: ClassVar[str | None] = None

Lower-case identifier of the benchmark whose datagen output this class reuses (e.g. "tpch"), or None when it generates its own data. Declared at class level so the orchestrator can resolve the shared datagen root BEFORE construction and inject it into __init__, instead of constructing first and mutating output_dir afterward.

api_surface = 'beta-public'
apply_verbosity(settings)

Apply verbosity settings to the mixin consumer.

property benchmark_name: str

Get the human-readable benchmark name.

create_enhanced_benchmark_result(platform, query_results, execution_metadata=None, phases=None, resource_utilization=None, performance_characteristics=None, duration_seconds=None, **kwargs)

Create a BenchmarkResults object with standardized fields.

create_minimal_benchmark_result(*, validation_status, validation_details=None, duration_seconds=0.0, platform='unknown', execution_metadata=None, system_profile=None, phases=None, **overrides)

Create a minimal BenchmarkResults instance for error and interrupt paths.

property csv_delimiter: str | None

CSV delimiter. Delegates to _impl if present.

format_results(benchmark_result)

Format benchmark results for display.

Parameters:

benchmark_result (dict[str, Any]) – Result dictionary from run_benchmark()

Returns:

Formatted string representation of the results

Return type:

str

get_csv_loading_config(table_name)

Get CSV loading configuration. Delegates to _impl if present.

get_data_source_benchmark()

Return the canonical source benchmark when data is shared.

Benchmarks that reuse data generated by another benchmark (for example, Primitives reusing TPC-H datasets) should set the DATA_SOURCE_BENCHMARK class attribute (preferred — it lets the orchestrator resolve the shared root before construction) or override this method. Benchmarks that produce their own data return None (default).

Delegates to _impl if present and _impl provides this method.

log_debug_info(context='Debug')

Log comprehensive debug information including version details.

log_error_with_debug_info(error, context='Error')

Log an error with comprehensive debug information.

log_notice(message)

Log a default-visible operational notice while respecting quiet mode.

log_operation_complete(operation, duration=None, details='')
log_operation_start(operation, details='')
log_verbose(message)

Log only when verbose mode is enabled.

log_version_warning()

Log version consistency warnings if any exist.

log_very_verbose(message)

Log only when very-verbose mode is enabled.

property logger: Logger

Return the logger configured for the verbosity mixin consumer.

property output_dir: Any

Return the resolved output directory handler.

quiet: bool = False
run_benchmark(connection, query_ids=None, fetch_results=False, setup_database=True)

Run the complete benchmark suite.

Parameters:

  • connection (DatabaseConnection) – Database connection to execute queries on

  • query_ids (list[int | str] | None) – Optional list of specific query IDs to run (defaults to all)

  • fetch_results (bool) – Whether to fetch and return query results

  • setup_database (bool) – Whether to set up the database first

Returns:

  • benchmark_name: Name of the benchmark

  • total_execution_time: Total time for all queries

  • total_queries: Number of queries executed

  • successful_queries: Number of queries that succeeded

  • failed_queries: Number of queries that failed

  • query_results: List of individual query results

  • setup_time: Time taken for database setup (if performed)

Return type:

Dictionary containing

Raises:

Exception – If benchmark execution fails

run_query(query_id, connection, params=None, fetch_results=False)

Execute single query and return timing and results.

Parameters:

  • query_id (int | str) – ID of the query to execute

  • connection (DatabaseConnection) – Database connection to execute query on

  • params (dict[str, Any] | None) – Optional parameters for query customization

  • fetch_results (bool) – Whether to fetch and return query results

Returns:

  • query_id: Executed query ID

  • execution_time: Time taken to execute query in seconds

  • query_text: Executed query text

  • results: Query results if fetch_results=True, otherwise None

  • row_count: Number of rows returned (if results fetched)

Return type:

Dictionary containing

Raises:

  • ValueError – If query_id is invalid

  • Exception – If query execution fails

run_with_platform(platform_adapter, **run_config)

Run complete benchmark using platform-specific optimizations.

This method provides a unified interface for running benchmarks using database platform adapters that handle connection management, data loading optimizations, and query execution.

This is the standard method that all benchmarks should support for integration with the CLI and other orchestration tools.

Parameters:

  • platform_adapter – Platform adapter instance (e.g., DuckDBAdapter)

  • **run_config – Configuration options: - categories: List of query categories to run (if benchmark supports) - query_subset: List of specific query IDs to run - connection: Connection configuration - benchmark_type: Type hint for optimizations (‘olap’, ‘oltp’, etc.)

Returns:

BenchmarkResults object with execution results

Example

from benchbox.platforms import DuckDBAdapter

benchmark = SomeBenchmark(scale_factor=0.1) adapter = DuckDBAdapter() results = benchmark.run_with_platform(adapter)

run_with_platform_api_surface = 'beta-public'
setup_database(connection)

Set up database with schema and data.

Creates necessary database schema and loads benchmark data into the database.

Parameters:

connection (DatabaseConnection) – Database connection to set up

Raises:

  • ValueError – If data generation fails

  • Exception – If database setup fails

property tables: dict

Table-to-path mappings.

Delegates to _impl.tables when a wrapper benchmark holds an implementation object; otherwise returns the instance’s own _tables dict (empty dict when unset).

translate_query(query_id, dialect)

Translate a query to a specific SQL dialect.

Parameters:

  • query_id (int | str) – The ID of the query to translate

  • dialect (str) – The target SQL dialect

Returns:

The translated query string

Raises:

  • ValueError – If the query_id is invalid

  • ImportError – If sqlglot is not installed

  • ValueError – If the dialect is not supported

Return type:

str

validate_loaded_data(connection, *, benchmark_name=None)

Validate post-load database state for this benchmark.

validate_manifest(*, manifest_path=None, benchmark_name=None)

Validate generated manifest for this benchmark.

validate_preflight(*, output_dir=None, benchmark_name=None)

Run preflight validation for this benchmark.

verbose: bool = False
verbose_enabled: bool = False
verbose_level: int = 0
property verbosity_settings: VerbositySettings

Return the current verbosity settings.

very_verbose: bool = False
scale_factor: float

Constructor

JoinOrder(
    scale_factor: float = 1.0,
    output_dir: Optional[Union[str, Path]] = None,
    **kwargs,
)

Parameters:

  • scale_factor: Fixed at 1.0 for canonical JOB data.

  • output_dir: Directory for verified Parquet files. Defaults to the BenchBox datagen cache path.

  • queries_dir: Optional directory with custom *.sql query files. If omitted, BenchBox uses the embedded 113 canonical JOB queries.

  • verbose: Enable progress output.

  • parallel: Accepted for interface compatibility. Canonical data is downloaded and verified rather than generated in parallel.

  • force_regenerate: Remove manifest-owned cached files and refetch.

Data Methods

generate_data() -> list[Path]

Ensures the canonical Parquet data package is present and verified.

Returns a list of 21 Parquet file paths in manifest order.

from benchbox import JoinOrder

benchmark = JoinOrder(scale_factor=1.0)
data_files = benchmark.generate_data()

assert len(data_files) == 21
assert all(path.suffix == ".parquet" for path in data_files)

Schema Methods

get_create_tables_sql(dialect="standard", tuning_config=None) -> str

Returns DDL for the 21-table JOB schema in the requested SQL dialect.

benchmark = JoinOrder(scale_factor=1.0)
ddl = benchmark.get_create_tables_sql(dialect="duckdb")

get_schema(dialect="sqlite") -> str

Convenience wrapper that returns schema DDL for the requested dialect.

benchmark = JoinOrder(scale_factor=1.0)
sqlite_ddl = benchmark.get_schema(dialect="sqlite")

Query Methods

get_query(query_id, *, params=None) -> str

Returns a static JOB query by ID, such as "1a" or "33c". params is not supported because JOB queries are fixed.

benchmark = JoinOrder(scale_factor=1.0)
query = benchmark.get_query("1a")

get_queries() -> dict[str, str]

Returns all embedded canonical JOB queries.

benchmark = JoinOrder(scale_factor=1.0)
queries = benchmark.get_queries()

assert len(queries) == 113
assert "1a" in queries
assert "33c" in queries

JoinOrderQueryManager

Use JoinOrderQueryManager directly when you only need the SQL catalog.

from benchbox.core.joinorder.queries import JoinOrderQueryManager

manager = JoinOrderQueryManager()
assert manager.get_query_count() == 113
query_ids = manager.get_query_ids()
query_1a = manager.get_query("1a")

The manager also supports an optional query directory:

manager = JoinOrderQueryManager("/path/to/job/queries")
custom_queries = manager.get_all_queries()

Data Provenance

BenchBox’s joinorder-imdb-2013-v1 package is derived from the Harvard Dataverse imdb_pg11 archive, DOI 10.7910/DVN/2QYZBT. The source represents the May 2013 IMDb list-file snapshot parsed with IMDbPY into the 21-table relational schema used by the JOB paper, restored into PostgreSQL, and converted to Parquet for repeatable BenchBox execution.

Dataset provenance and redistribution notes live in benchbox/core/joinorder/DATA-LICENSE.md.

References