JOB-Complex: A Challenging Benchmark for Traditional & Learned Query Optimization

2025-07-10

AI Summary

Existing query optimizer benchmarks (e.g., JOB) severely overestimate optimizer performance due to their lack of realistic workload characteristics—particularly string-join predicates, complex multi-predicate filters, and nested subqueries. To address this gap, we propose JOB-Complex, the first benchmark explicitly designed to reflect real-world complexity. It comprises 30 SQL queries derived from production workloads and nearly 6,000 human-annotated execution plans. Its key contribution lies in the systematic inclusion of realistic challenges—string-based joins, compound predicate filtering, and deeply nested subqueries—and its support for fine-grained evaluation of both cost estimation accuracy and plan selection quality. Experimental results demonstrate that state-of-the-art traditional and learned optimizers incur up to 11× higher execution time than the optimal plan on JOB-Complex, exposing fundamental limitations in current techniques under realistic complexity. JOB-Complex thus establishes a more rigorous and trustworthy evaluation standard for query optimization research.

Technology Category

• Search and Optimization: Evaluation and Analysis
• Knowledge Representation and Reasoning: Computational Complexity of Reasoning
• Reasoning under Uncertainty: Stochastic Optimization

Application Category

• Economics, Online Markets and Human Computation: Data quality aspects of human-annotated datasets
• Search and Retrieval-Augmented AI: Web evaluation methodologies and metrics
• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs

Abstract

Query optimization is a fundamental task in database systems that is crucial to providing high performance. To evaluate learned and traditional optimizer's performance, several benchmarks, such as the widely used JOB benchmark, are used. However, in this paper, we argue that existing benchmarks are inherently limited, as they do not reflect many real-world properties of query optimization, thus overstating the performance of both traditional and learned optimizers. In fact, simple but realistic properties, such as joins over string columns or complex filter predicates, can drastically reduce the performance of existing query optimizers. Thus, we introduce JOB-Complex, a new benchmark designed to challenge traditional and learned query optimizers by reflecting real-world complexity. Overall, JOB-Complex contains 30 SQL queries and comes together with a plan-selection benchmark containing nearly 6000 execution plans, making it a valuable resource to evaluate the performance of query optimizers and cost models in real-world scenarios. In our evaluation, we show that traditional and learned cost models struggle to achieve high performance on JOB-Complex, providing a runtime of up to 11x slower compared to the optimal plans.

Problem

• Existing benchmarks lack real-world query optimization complexity.
• JOB-Complex challenges optimizers with realistic string joins and filters.
• Traditional and learned optimizers perform poorly on JOB-Complex benchmarks.

Innovation

• Introduces JOB-Complex benchmark for query optimizers
• Includes 30 SQL queries and 6000 execution plans
• Reflects real-world complexity in query optimization