xBound: Join Size Lower Bounds

TL;DR

The paper tackles the pervasive problem of cardinality underestimation in query optimization by introducing xBound, the first framework for provable join-size lower bounds. It leverages reverse dot-product inequalities on positive degree-sequence norms, combined with lower bounds on the number of joining keys and enhanced via norm stitching and partitioning, to produce hard lower bounds on multi-way joins on the same key. Empirically, xBound detects lower-bound violations in current optimizers and improves one-sided estimation errors, correcting up to 36.1% of underestimates on real workloads like Fabric DW, and reducing median Q-errors on several benchmarks when integrated with existing systems. These results demonstrate a practical path toward more reliable query planning and resource provisioning by bounding the risk of underestimation, with clear directions for extending the approach to broader query types and statistics.

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.

Figures (7)

• Figure 1: Overview of cardinality bound violations across all join subexpressions in JOBlight, when estimating with DuckDB 1.4, PostgreSQL 18, and Fabric Data Warehouse.
• Figure 2: Obtaining a lower bound on the join size, assuming all keys join, by multiplying cross-wise the values of the (non-decreasing) degree sequences. This follows from the rearrangement inequality, Ineq. \ref{['ineq:rearr-ineq']}.
• Figure 3: xBound at runtime: Given a multi-way join on the same key, it (a) lower-bounds the number of joining keys (= $m$), (b) uses $m$ as the prefix length for evaluating degree-sequence norms on the base tables, and (c) takes the best bound from the generalized reverse Hölder's inequality, min-degree bound, and $m$. The output represents a hard lower bound on the query size.
• Figure 4: Norm stitching: Deriving $\ell_2$ lower bounds for non-power-of-two prefixes by extending the previous power-of-two prefix using its $\ell_\infty$ value.
• Figure 5: Average estimation time of systems and estimators for varying join sizes on all subexpressions of JOBlight.

Theorems & Definitions (2)

• lemma 1: Prefixes of Degree Sequences; two-way join
• lemma 2: Prefixes of Degree Sequences; multi-way join