In modern relational databases, cardinality estimation (CE) plays a critical role. Simply put, cardinality estimation predicts how many intermediate results a database query will return. This prediction significantly influences the query optimizer's choice of execution plan, such as determining join order, whether to use indexes, and selecting the best join method. If the cardinality estimate is inaccurate, the execution plan may be greatly compromised, leading to slow query speeds and severely impacting overall database performance.
However, existing cardinality estimation methods have several limitations. Traditional CE techniques rely on simplified assumptions, often failing to accurately predict the cardinality of complex queries, especially those involving multiple tables and conditions. Although learning-based CE models can provide better accuracy, their application is limited by long training times, the need for large datasets, and the lack of systematic benchmark evaluations.
To fill this gap, Google's research team has introduced CardBench, a new benchmark testing framework. CardBench includes over 20 real-world databases and thousands of queries, far surpassing previous benchmarks. This allows researchers to systematically evaluate and compare different learning-based CE models under various conditions. The benchmark supports three main settings: instance-based models, zero-shot models, and fine-tuned models, suitable for different training needs.
The design of CardBench also includes a series of tools to calculate necessary data statistics, generate realistic SQL queries, and create annotated query graphs for training CE models.
The benchmark provides two sets of training data: one for single-table queries with multiple filter condition predicates and another for binary join queries involving two tables. The benchmark includes 9,125 single-table queries and 8,454 binary join queries, ensuring a robust and challenging environment for model evaluation. The training data labels from Google BigQuery required 7 CPU years of query execution time, highlighting the significant computational investment in creating this benchmark. By providing these datasets and tools, CardBench lowers the barrier for researchers developing and testing new CE models.
In performance evaluations using CardBench, fine-tuned models have particularly stood out. While zero-shot models struggle to improve accuracy when applied to unseen datasets, especially in complex queries involving joins, fine-tuned models can achieve comparable accuracy to instance-based methods with much less training data. For example, a fine-tuned Graph Neural Network (GNN) model achieved a median q-error of 1.32 and a 95th percentile q-error of 120 in binary join queries, significantly outperforming zero-shot models. The results indicate that even with 500 queries, fine-tuning a pre-trained model can significantly improve its performance, making them suitable for practical applications where training data may be limited.
The introduction of CardBench brings new hope to the field of learning-based cardinality estimation, enabling researchers to more effectively evaluate and improve models, thereby advancing this important area.
Paper link: https://arxiv.org/abs/2408.16170
Key points:
- 📊 CardBench is a new benchmark testing framework that includes 20 real databases and thousands of queries, supporting systematic evaluation of learning-based cardinality estimation models.
- 🛠️ The benchmark provides tools for calculating data statistics, generating SQL queries, and creating query graphs, lowering the development barrier for researchers.
- 🚀 Fine-tuned models have performed exceptionally well in performance evaluations, achieving similar accuracy to traditional models with much less training data, demonstrating potential for practical applications.