xBound: Join Size Lower Bounds

2026-01-19

AI Summary

This work addresses the persistent problem of severe underestimation of join result cardinalities in database query optimizers, which often leads to insufficient resource allocation and suboptimal execution plans. We propose the first provable framework for deriving lower bounds on join sizes, leveraging formal methods to compute theoretically sound lower bounds and thereby filling a critical gap in pessimistic cardinality estimation. The approach has been integrated into DuckDB, PostgreSQL, and Microsoft Fabric Data Warehouse. Evaluation on the JOBlight benchmark shows that it corrects 17.5% and 8.7% of underestimated subexpressions in DuckDB and PostgreSQL, respectively, while in Microsoft’s enterprise workloads, it resolves 36.1% of underestimations in Fabric Data Warehouse, significantly enhancing optimizer robustness.

Technology Category

• Reasoning under Uncertainty: Stochastic Optimization
• Search and Optimization: Distributed Search
• Constraint Satisfaction and Optimization: Satisfiability Modulo Theories

Application Category

• Security and Privacy: Large-scale security measurements
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems
• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs

Abstract

Cloud database vendors invest substantial resources into their query optimizers, and for good reason. Cardinality estimation, a cornerstone of the optimizer, is critical for the selection of efficient query plans, as well as downstream tasks such as resource allocation and query scheduling. Yet, as many practitioners and researchers have noted, it is also the optimizer's Achilles heel. Prior studies on a number of industrial-strength databases show substantial cardinality estimation errors on all tested systems, with a far greater tendency to underestimate than to overestimate. Unfortunately, cardinality underestimation is more problematic than overestimation, as it misleads the optimizer to choose plans designed for small data, leading to underprovisioned CPU and memory. While previous work on pessimistic cardinality estimation has proposed provable join size upper bounds, such methods can only correct overestimation, leaving the more harmful problem of underestimation unaddressed. To fill this critical gap, we introduce xBound, the very first framework for deriving provable join size lower bounds. xBound successfully reduces underestimation in real systems: On the JOBlight benchmark, it corrects 17.5% of subexpression underestimates in DuckDB and 8.7% in PostgreSQL, while on a Microsoft enterprise workload, it fixes 36.1% of Fabric Data Warehouse's underestimates, demonstrating a significant step towards solving this long-standing problem.

Problem

• cardinality estimation
• join size lower bounds
• query optimization
• underestimation

Innovation

• cardinality estimation
• join size lower bounds
• query optimization
• underestimation correction
• xBound