Advances and Challenges in Machine Learning-Based Cardinality Estimation for Database Query Optimization

School of Software Engineering, Sichuan University, Chengdu, China

2022141461143@stu.scu.edu.cn

Abstract

Accurate cardinality estimation is critical for optimizing database queries, yet traditional methods often fail to provide reliable predictions in the face of complex queries, skewed data distributions, and high-dimensional schemas. As data volumes and query complexity grow, more robust and adaptive estimation techniques have become essential for maintaining efficient query performance. This paper surveys recent advancements in machine learning-based cardinality estimation methods, categorizing them into three main types: query-driven, data-driven, and hybrid models. Each approach is analyzed in terms of its model architecture, training strategies, and predictive performance. Notable techniques include PostCENN’s integration into PostgreSQL, FACE’s use of normalizing flows, and UAE’s autoregressive learning across data and query distributions. Comparative evaluations highlight how these models address specific challenges in cardinality estimation. The results show that while machine learning methods significantly reduce estimation errors, they also share limitations such as sensitivity to workload drift, scalability challenges with large schemas, and poor generalization in long-tail query regions. To address these, this paper proposed future directions including Bayesian updating, sparse factor graph modeling, and active query synthesis. These innovations hold promise for building more accurate, scalable, and adaptive estimators that can enhance database system performance under diverse and dynamic workloads.