Cardinality estimation in DBMS

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 DBMS PostgreSQL, and comprehensively evaluate their true effectiveness in improving query plan quality, and other important aspects affecting their applicability. We obtain a number of key findings 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 generated query plan quality. 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. It could serve as a better optimization objective for future CardEst methods.

QardEst: Using Quantum Machine Learning for Cardinality Estimation of Join Queries

“…The cardinality estimation problem for database queries has recently been tackled using mainly classical machine learning algorithms [20]. While the results of these methods are more accurate than traditional non-machine learning-based approaches, they still suffer from high training and inference costs while learning multivariate correlations [15,48], and can mitigate these "side effects" only when using single-table statistics as an input [51]. This is still an unsolved problem, and therefore, it is uncertain if such models will prevail in real databases, especially in dynamic environments [48].…”

“…Learned cardinalities estimators (LCEs) are exactly attempting to mitigate these issues with the help of machine learning methods. Although deep learning seems to give significant improvement compared to classical methods on datasets with more complicated data distributions and join schemas, it still requires hundreds of millions of learnable parameters that are often hard to tune [15].…”

Representative Routes Discovery from Massive Trajectories

“…a query set can be cast as the multi-query-dataset cardinality estimation (MCE) problem. So, we also review existing work on single-query-dataset cardinality estimation (SCE), which can be categorized as querydriven methods and data-driven methods [21,30]. Query-driven methods [17,22,31,41,46,47,49,52,53] deploy discriminative models trained on a set of historic queries to predict the cardinality for a single query.…”

“…Data-driven methods [24,37,39,51,54,56,57,60] deploy generative models trained on data without using query workloads. In general, query-driven methods are inflexible, especially when representative queries are unavailable, and data-driven methods can achieve better performance than query-driven methods [21]. However, existing approaches to the SCE problem cannot address the MCE problem as they only estimate the cardinality of a single query over a single dataset while we estimate the cardinality for a query set over a set of datasets.…”

“…We call this CE problem Multi-Query-Dataset Cardinality Estimation (MCE). The MCE problem differs from the existing CE problem [21,24,30,39,56,60], which estimates the cardinality using a single query and a single dataset. To better distinguish these two problems, we call the latter Single-Query-Dataset Cardinality Estimation (SCE).…”