[Paper Review] Cardinality Estimation in DBMS: A Comprehensive Benchmark Evaluation

Yuxing Han, Zi‐Niu Wu+12 more|arXiv (Cornell University)|Sep 13, 2021

Advanced Database Systems and Queries57 references17 citations

TL;DR

This paper introduces a comprehensive benchmark for cardinality estimation (CardEst) in DBMS, evaluating 12 state-of-the-art methods in PostgreSQL using a real-world dataset (STATS) and diverse workload (STATS-CEB). It proposes a new metric, P-Error, which better correlates with end-to-end query performance than traditional Q-Error, revealing that model accuracy and inference efficiency jointly determine optimizer improvement, and that data-driven ML methods outperform query-driven ones in practice.

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.

Motivation & Objective

• To address the lack of systematic, end-to-end evaluation of CardEst methods in real DBMS workloads.
• To identify shortcomings in existing evaluation practices, particularly over-reliance on algorithm-level metrics like Q-Error.
• To establish a new benchmark with realistic data and query workloads reflecting complex, real-world DBMS scenarios.
• To propose and validate a new metric, P-Error, that better reflects the actual impact of CardEst on query execution time.
• To provide actionable insights for researchers on designing more effective, practical, and scalable CardEst methods.

Proposed method

• Built a new real-world benchmark dataset (STATS) with high skewness, correlations, and complex schema from actual business data.
• Designed a diverse query workload (STATS-CEB) covering multi-table joins, various join types, and selective predicates.
• Integrated 12 representative CardEst methods—including traditional, query-driven, and data-driven ML models—into open-source PostgreSQL.
• Proposed P-Error as a new evaluation metric that aggregates sub-plan errors based on their impact on plan cost, reflecting end-to-end optimizer performance.
• Conducted end-to-end evaluation measuring query execution time, inference latency, model size, training time, and update efficiency.
• Used correlation analysis to compare P-Error and Q-Error with actual query execution time, validating P-Error's superiority.

Experimental results

Research questions

• RQ1 To what extent do advanced CardEst methods improve query optimizer performance in real-world DBMS settings?
• RQ2 How well do traditional algorithm-level metrics like Q-Error reflect actual end-to-end query performance?
• RQ3 Which CardEst methods offer the best trade-off between estimation accuracy, inference speed, and update efficiency in dynamic DBMS?
• RQ4 How do different sub-plan estimation errors affect final query plan quality and execution time?
• RQ5 Can a new metric like P-Error better predict real-world query performance than Q-Error?

Key findings

• The P-Error metric shows a strong correlation (0.810–0.838 at 50th–90th percentiles) with actual query execution time, while Q-Error shows near-zero correlation (0.036–0.037), proving P-Error is a better predictor of real performance.
• Data-driven ML-based CardEst methods like FLAT, DeepDB, and BayesCard significantly improve end-to-end query performance by balancing model expressiveness and inference efficiency.
• Query-driven ML-based methods show little to no improvement over PostgreSQL’s baseline, due to poor scalability and high inference cost.
• Traditional methods and some data-driven models (e.g., PessEst, BayesCard) support fast updates and are practical for dynamic, real-time DBMS workloads.
• Model performance degrades significantly on multi-table join queries with more than 4–5 tables, especially for large-scale data-driven models.
• Optimizing for Q-Error does not guarantee better query plans, as errors in high-cardinality sub-plans have a disproportionate impact on plan quality.