Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation (2109.05877v3)

Abstract: Cardinality estimation (CardEst) plays a significant role in generating high-quality query plans for a query optimizer in DBMS. In the last decade, an increasing number of advanced CardEst methods (especially ML-based) have been proposed with outstanding estimation accuracy and inference latency. However, there exists no study that systematically evaluates the quality of these methods and answer the fundamental problem: to what extent can these methods improve the performance of query optimizer in real-world settings, which is the ultimate goal of a CardEst method. In this paper, we comprehensively and systematically compare the effectiveness of CardEst methods in a real DBMS. We establish a new benchmark for CardEst, which contains a new complex real-world dataset STATS and a diverse query workload STATS-CEB. We integrate multiple most representative CardEst methods into an open-source database system PostgreSQL, and comprehensively evaluate their true effectiveness in improving query plan quality, and other important aspects affecting their applicability, ranging from inference latency, model size, and training time, to update efficiency and accuracy. We obtain a number of key findings for the CardEst methods, under different data and query settings. Furthermore, we find that the widely used estimation accuracy metric(Q-Error) cannot distinguish the importance of different sub-plan queries during query optimization and thus cannot truly reflect the query plan quality generated by CardEst methods. Therefore, we propose a new metric P-Error to evaluate the performance of CardEst methods, which overcomes the limitation of Q-Error and is able to reflect the overall end-to-end performance of CardEst methods. We have made all of the benchmark data and evaluation code publicly available at https://github.com/Nathaniel-Han/End-to-End-CardEst-Benchmark.

• The paper introduces a comprehensive benchmark with the STATS dataset and STATS-CEB workload to evaluate both traditional and ML-based estimation techniques.
• It demonstrates that ML-based methods significantly outperform traditional approaches in query planning accuracy and execution efficiency.
• The study proposes the P-Error metric to more accurately capture end-to-end performance, guiding future advancements in query optimization.

Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation

The paper under discussion provides a thorough examination of cardinality estimation techniques within database management systems (DBMS). Cardinality estimation (CardEst) serves as a critical function within query optimizers, determining the result size of sub-plans and guiding the selection of optimal execution strategies. The paper critiques existing CardEst methods, highlighting the absence of a comprehensive evaluation framework that assesses their real-world performance, especially in the context of query optimizer enhancement.

Key Contributions and Methodology

This research establishes a new benchmark designed specifically for evaluating CardEst methods. The benchmark comprises a new, complex dataset named STATS and a heterogeneous query workload, STATS-CEB. The methodology integrates various representative CardEst techniques, including traditional and ML-based methods, into the open-source DBMS PostgreSQL and evaluates their performance in terms of query planning time, execution time, model size, training duration, update efficiency, and accuracy.

The authors identify several shortcomings in current evaluation approaches, particularly the reliance on inadequate datasets and metrics like Q-Error, which do not accurately reflect the importance of sub-plan queries. In response, the paper introduces a novel metric, P-Error, designed to more effectively gauge the end-to-end performance of CardEst methods.

Significant Findings

The paper provides insightful observations regarding the performance of CardEst methods:

1. Performance Analysis: Traditional methods (e.g., histograms and sampling) generally performed poorly compared to ML-based data-driven methods, which displayed significant improvements in execution time and plan quality. Notable among these were Bayesian networks and probabilistic graphical models such as SPN and FSPN.
2. In-depth Evaluation: The STATS-CEB workload enabled a nuanced analysis of CardEst approaches under realistic conditions, revealing that methods leveraging sophisticated statistical models outperform DBMS baselines in both accuracy and efficiency.
3. Impact of Inference Latency: The paper delineates between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) workloads, emphasizing that inference latency is a crucial factor in OLTP scenarios but is less critical for OLAP workloads.
4. Practical Considerations: Cardinal aspects affecting the deployment of CardEst models, such as training cost, space requirements, and update efficiency, were scrutinized. Bayesian networks emerged as particularly advantageous, offering a good balance between model size and update efficiency.
5. Limits of Q-Error: The Q-Error metric, often used for evaluating estimation accuracy, was shown to inadequately capture the operational implications of different sub-plan queries on execution strategies. The new P-Error metric provides a more accurate assessment aligning with end-to-end performance.
6. Future Research Directions: The paper highlights potential areas for future work, including expanding the scope of ML-based CardEst methods to accommodate more complex query types and further enhancing models' ability to adjust between OLTP and OLAP settings.

Implications and Speculations

This paper has far-reaching implications in the domain of database systems. By providing a comprehensive benchmark and redefining performance metrics, it sets the stage for the development of more adaptable and efficient CardEst methodologies. These advancements will likely reduce the query execution time in DBMS, enhancing system throughput and offering a robust foundation for future research exploring the application of AI and ML techniques in database query optimization.

Furthermore, the insights gained from this evaluation could steer ongoing efforts to improve cost models and execution plans, leading to more resource-efficient data processing and analysis. This work challenges the community to refine learning algorithms and statistical models, ensuring they remain aligned with the operational realities and demands of modern databases.