Efficient Cardinality Estimation with Query-induced Probability Inference

Cardinality estimation is critical for database management systems (DBMSs) to execute query optimization tasks, which can guide the query optimizer in choosing the best execution plan. However, traditional cardinality estimation methods cannot provide accurate estimates because they cannot accurately capture the correlation between multiple tables. Several recent studies have revealed that learning-based cardinality estimation methods can address the shortcomings of traditional methods and provide more accurate estimates. However, the learning-based cardinality estimation methods still have large errors when an SQL query involves multiple tables or is very complex. To address this problem, we propose a sampling-based tree long short-term memory (TreeLSTM) neural network to model queries. The proposed model addresses the weakness of traditional methods when no sampled tuples match the predicates and considers the join relationship between multiple tables and the conjunction and disjunction operations between predicates. We construct subexpressions as trees using operator types between predicates and improve the performance and accuracy of cardinality estimation by capturing the join-crossing correlations between tables and the order dependencies between predicates. In addition, we construct a new loss function to overcome the drawback that Q-error cannot distinguish between large and small cardinalities. Extensive experimental results from real-world datasets show that our proposed model improves the estimation quality and outperforms traditional cardinality estimation methods and the other compared deep learning methods in three evaluation metrics: Q-error, MAE, and SMAPE.

Cardinality estimation is a crucial component in query optimizers. After decades of research, employing autoregressive models for cardinality estimation has demonstrated remarkable accuracy. However, when queries involve attributes with large domain sizes, autoregressive model-based estimators struggle to accurately capture the data distribution, leading to poor performance. Furthermore, these models often exhibit significant errors when handling queries with low-selectivity predicates. To address these challenges, we propose a self-adaptive cardinality estimator named AdaCard. Initially, we employ a self-adaptive smoothing factor selection strategy to variably adjust the original data, thereby mitigating the impact of large domain sizes. Secondly, to correct errors stemming from Monte Carlo sampling, we utilize resampling to refine the handling of low-selectivity predicates, thereby improving accuracy. Through evaluation using four real-world benchmarks, we compared AdaCard with mainstream baselines. The final results show that our estimator has the lowest tail estimation error and improves accuracy by nearly 10$\times $ over the second-best method, with similar latency and model size.

The ML-based inferential statistics framework presents a novel solution for database query optimization that addresses critical challenges in statistics maintenance and cardinality estimation. By combining Bayesian learning and reinforcement learning modules, the framework enables continuous adaptation to changing data patterns while minimizing computational overhead. The solution offers improved query performance through better plan selection, reduced resource consumption, and enhanced accuracy in cardinality estimation. The framework's dynamic histogram redistribution mechanism ensures optimal statistics maintenance in high-throughput environments, making it particularly effective for enterprise-scale databases with rapidly evolving data distributions.

Query optimizer is at the heart of the database systems. Cost-based optimizer studied in this paper is adopted in almost all current database systems. A cost-based optimizer introduces a plan enumeration algorithm to find a (sub)plan, and then uses a cost model to obtain the cost of that plan, and selects the plan with the lowest cost. In the cost model, cardinality, the number of tuples through an operator, plays a crucial role. Due to the inaccuracy in cardinality estimation, errors in cost model, and the huge plan space, the optimizer cannot find the optimal execution plan for a complex query in a reasonable time. In this paper, we first deeply study the causes behind the limitations above. Next, we review the techniques used to improve the quality of the three key components in the cost-based optimizer, cardinality estimation, cost model, and plan enumeration. We also provide our insights on the future directions for each of the above aspects.

Cardinality estimation plays a vital role in query optimizer, the key factors challenge its accuracy are join-crossing correlations between different attributes. Traditional estimation techniques provide poor estimation quality for complex queries with many joins for the lack of correlations. Recently, much work has shown that machine learning based methods overcome the challenge to a certain extent. However, the existing learning-based approaches have two major downsides. First, lack of explicit utilization of the effective and explainable feature: conjunctive predicates between different attributes. Second, the dynamic information associated with the dataset status is not used to encode the static query structure. In this paper, we propose GACE, a cardinality estimator based on Graph Attention Network (GAT) to capture join-crossing correlations between attributes from join graphs constructed on conjunctive predicates. GACE leverages the reliable features Base Table Selectivity and Join Selectivity obtained through traditional techniques to enrich the encoding of query structure and track the status alterations of dataset. Taking the regularity of join graphs into account, a GAT model with batch training supporting is implemented to sufficiently decrease the overhead of training. The results of our empirical evaluation on real-world dataset (IMDb) demonstrate that GACE achieves improvement in quality of cardinality estimation, especially for more joins.

Accurate cardinality estimation is critically important to high-quality query optimization. It is well known that conventional cardinality estimation based on histograms or similar statistics may produce extremely poor estimates in a variety of situations, for example, queries with complex predicates, correlation among columns, or predicates containing user-defined functions. In this paper, we propose a new, general cardinality estimation technique that combines random sampling and materialized view technology to produce accurate estimates even in these situations. As a major innovation, we exploit feedback information from query execution and process control techniques to assure that estimates remain statistically valid when the underlying data changes. Experimental results based on a prototype implementation in Microsoft SQL Server demonstrate the practicality of the approach and illustrate the dramatic effects improved cardinality estimates may have.

Cardinality estimation is an important component of query optimization. Its accuracy and efficiency directly decide effect of query optimization. Traditional cardinality estimation strategy is based on original table or sample to collect statistics, then inferring cardinality by collected statistics. It will be low-efficiency when handling big data; Statistics exist update latency and are gotten by inferring, which can not guarantee correctness; Some strategies can get the actual cardinality by executing some subqueries, but they do not keep the result, leading to low efficiency of fetching statistics. Against these problems, this paper proposes a novel cardinality estimation strategy, called cardinality estimation based on query result(CEQR). For keeping correctness of cardinality, CEQR directly gets statistics from query results, which is not related with data size; we build a cardinality table to store the statistics of basic tables and middle results under specific predicates. Cardinality table can provide cardinality services for subsequent queries, and we build a suit of rules to maintain cardinality table; To improve the efficiency of fetching statistics, we introduce the source aware strategy, which hashes cardinality item to appropriate cache. This paper gives the adaptability and deviation analytic of CEQR, and proves that CEQR is more efficient than traditional cardinality estimation strategy by experiments.

Cardinality estimation is widely believed to be one of the most important causes of poor query plans. Prior studies evaluate the impact of cardinality estimation on plan quality on a set of Select-Project-Join queries on PostgreSQL DBMS. Our empirical study broadens the scope of prior studies in significant ways. First, we include complex SQL queries containing group-by, aggregation, outer joins and sub-queries from real-world workloads and industry benchmarks. We evaluate on both row-oriented and column-oriented physical designs. Our empirical study uses Microsoft SQL Server, an industry-strength DBMS with a state-of-the-art query optimizer that is equipped with techniques to optimize such complex queries. Second, we analyze the sensitivity of plan quality to cardinality errors in two ways by: (a) varying the subset of query sub-expressions for which accurate cardinalities are used, and (b) introducing progressively larger cardinality errors. Third, query processing techniques such as bitmap filtering and adaptive join have the potential to mitigate the impact of cardinality estimation errors by reducing the latency of bad plans. We evaluate the importance of accurate cardinalities in the presence of these techniques.

Research on query optimization has focused almost exclusively on reducing query execution time, while important qualities such as consistency and predictability have largely been ignored, even though most database users consider these qualities to be at least as important as raw performance. In this paper, we explore how the query optimization process can be made more robust, focusing on the important subproblem of cardinality estimation. The robust cardinality estimation technique that we propose allows for a user- or application-specified trade-off between performance and predictability, and it captures multi-dimensional correlations while remaining space- and time-efficient.

Deco is a comprehensive system for answering declarative queries posed over stored relational data together with data obtained on-demand from the crowd. In this paper we describe Deco's cost-based query optimizer, building on Deco's data model, query language, and query execution engine presented earlier. Deco's objective in query optimization is to find the best query plan to answer a query, in terms of estimated monetary cost. Deco's query semantics and plan execution strategies require several fundamental changes to traditional query optimization. Novel techniques incorporated into Deco's query optimizer include a cost model distinguishing between "free" existing data versus paid new data, a cardinality estimation algorithm coping with changes to the database state during query execution, and a plan enumeration algorithm maximizing reuse of common subplans in a setting that makes reuse challenging. We experimentally evaluate Deco's query optimizer, focusing on the accuracy of cost estimation and the efficiency of plan enumeration.

This paper evaluates various parameter estimation methods for the Wald distribution using simulations and real-world datasets. Simulation results confirm that increased sample sizes improve estimates, reducing bias and Root Mean-Squared Error (RMSE). The Maximum Likelihood Estimator (MLE) is generally the most robust method for large samples but unstable and biased for smaller ones, particularly in estimating λ. The Maximum product of spacing estimation (MPS) method performs well asymptotically, with bias and RMSE decreasing as sample size increases. Least Squares (LS) and Weighted Least Squares (WLS) are suitable alternatives to MLE for small-to-moderate samples, showing similar estimates and reduced bias with larger samples. The Cramer-von Mises Estimator (CvM) displayed the worst efficiency due to high RMSE. Bayesian Estimators (BE) showed greater bias and lower efficiency than their frequentist counterparts, especially for λ, with performance strongly dependent on prior selection. Application to real datasets (HIV/AIDS mortality, COVID-19 death rates, and industrial gauge measurements) demonstrates the Wald distribution’s feasibility in diverse health data analysis and reliability studies. The study concludes that MLE and MPSE are efficient estimation methods, suggesting the Wald distribution is a strong candidate for applied parameter estimation. Future work could focus on improving Bayesian methods via better prior selection

An effective, accurate algorithm for cardinality estimation of queries on ontology models of data is presented. The algorithm relies on the decomposition of queries into query pattern paths, where each path produces a set of values for each variable within the result form of the query. In order to estimate the total number of result set parameters for each path, a set of statistics is compiled on the properties of the ontology. Experimental analysis has shown that the algorithm produces estimates with high accuracy and with high correlation to actual values. Thus, this algorithm can be used as the cornerstone of an effective optimization strategy for queries on diverse, heterogeneous data sources modeled as ontologies.

Query optimizers depend heavily on statistics representing column distributions to create good query plans. In many cases, though, statistics are outdated or nonexistent, and the process of refreshing statistics is very expensive, especially for ad hoc workloads on ever bigger data. This results in suboptimal plans that severely hurt performance. The core of the problem is the fixed decision on the type of physical operators that comprise a query plan. This paper makes a case for continuous adaptation and morphing of physical operators throughout their lifetime, by adjusting their behavior in accordance with the observed statistical properties of the data at run time. We demonstrate the benefits of the new paradigm by designing and implementing an adaptive access path operator called Smooth Scan, which morphs continuously within the space of index access and full table scan. Smooth Scan behaves similarly to an index scan for low selectivity; if selectivity increases, however, Smooth Scan progressively morphs its behavior toward a sequential scan. As a result, a system with Smooth Scan requires no optimization decisions on the access paths up front. Additionally, by depending only on the result distribution and eschewing statistics and cardinality estimates altogether, Smooth Scan ensures repeatable execution across multiple query invocations. Smooth Scan implemented in PostgreSQL demonstrates robust, near-optimal performance on micro-benchmarks and real-life workloads, while being statistics oblivious at the same time.

An efficient method for estimating the total number of neurons in rat brain cortex

Estimating Tie Strength in Multi-Relation Online Social Networks