[Paper Review] FactorJoin: A New Cardinality Estimation Framework for Join Queries

Zi‐Niu Wu, Parimarjan Negi+3 more|arXiv (Cornell University)|Dec 11, 2022

Data Management and Algorithms4 citations

TL;DR

FactorJoin is a novel cardinality estimation framework that combines single-table learned distributions with factor graph inference to estimate join query selectivity efficiently and accurately. It achieves 40x lower latency, 100x smaller model size, and 100x faster training than prior learned methods, while matching or exceeding state-of-the-art accuracy on real-world benchmarks.

ABSTRACT

Cardinality estimation is one of the most fundamental and challenging problems in query optimization. Neither classical nor learning-based methods yield satisfactory performance when estimating the cardinality of the join queries. They either rely on simplified assumptions leading to ineffective cardinality estimates or build large models to understand the data distributions, leading to long planning times and a lack of generalizability across queries. In this paper, we propose a new framework FactorJoin for estimating join queries. FactorJoin combines the idea behind the classical join-histogram method to efficiently handle joins with the learning-based methods to accurately capture attribute correlation. Specifically, FactorJoin scans every table in a DB and builds single-table conditional distributions during an offline preparation phase. When a join query comes, FactorJoin translates it into a factor graph model over the learned distributions to effectively and efficiently estimate its cardinality. Unlike existing learning-based methods, FactorJoin does not need to de-normalize joins upfront or require executed query workloads to train the model. Since it only relies on single-table statistics, FactorJoin has small space overhead and is extremely easy to train and maintain. In our evaluation, FactorJoin can produce more effective estimates than the previous state-of-the-art learning-based methods, with 40x less estimation latency, 100x smaller model size, and 100x faster training speed at comparable or better accuracy. In addition, FactorJoin can estimate 10,000 sub-plan queries within one second to optimize the query plan, which is very close to the traditional cardinality estimators in commercial DBMS.

Motivation & Objective

• Address the limitations of classical cardinality estimators that assume attribute independence and uniform join-key distributions.
• Overcome the inefficiency and scalability issues of existing learning-based methods that require de-normalization or large-scale training on joined data.
• Develop a practical, deployable framework that maintains high accuracy while minimizing space and time overhead for query optimization.
• Enable real-time estimation of tens of thousands of query plans per second to support dynamic query optimization in production systems.

Proposed method

• Build single-table conditional distributions (e.g., via Bayesian networks or deep generative models) during an offline preparation phase to capture attribute correlations and join-key distributions.
• Represent each join query as a factor graph over the learned single-table distributions, transforming cardinality estimation into a probabilistic inference problem.
• Use a bound-based bin selection algorithm (GBSA) to efficiently discretize join keys and reduce computational complexity.
• Incorporate conditional distributions that model how filter predicates affect join-key distributions, improving accuracy without de-normalization.
• Decompose complex multi-join queries into tractable inference tasks using factor graph message passing, avoiding full materialization of joined relations.
• Support plug-in integration with any single-table cardinality estimator, making the framework modular and extensible.

Experimental results

Research questions

• RQ1 Can a framework that combines single-table learned statistics with factor graph inference achieve higher accuracy than classical or end-to-end learned methods for join cardinality estimation?
• RQ2 To what extent can avoiding de-normalization and full-table materialization reduce model size and training time while preserving estimation quality?
• RQ3 How effective is the integration of conditional distributions (e.g., P(join_key | filter)) in capturing attribute correlations without assuming independence?
• RQ4 Can the framework scale to estimate 10,000 sub-plans per second, enabling real-time query optimization in practice?
• RQ5 Does the framework outperform state-of-the-art learned and classical methods in terms of accuracy, latency, model size, and training speed across diverse real-world workloads?

Key findings

• FactorJoin reduces estimation latency by 40x compared to the previous state-of-the-art learned methods, achieving sub-second estimation of 10,000 sub-plans.
• The model size of FactorJoin is 100x smaller than existing learned methods, enabling efficient deployment in production systems.
• Training speed is accelerated by 100x compared to prior learned methods, due to the absence of de-normalization and joint distribution learning.
• With both bound and conditional optimizations, FactorJoin achieves a 45.9% improvement in end-to-end query optimization time over JoinHist, the prior SOTA.
• FactorJoin outperforms Postgres and all existing SOTA methods in query execution time, demonstrating robustness across diverse workloads and hyperparameter settings.
• The GBSA bin selection algorithm effectively generates high-quality plans, and the framework remains stable and accurate even when using different single-table estimators.