Machine Learning-Based Inferential Statistics for Query Optimization: A Novel Approach

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.

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.

As the critical task in query optimization, cardinality estimation is fulfilled by learning the joint probability distribution of table attributes in most of the existing data-driven methods. However, these methods still suffer the efficiency bottleneck, since the exponential combinations of table joins lead to massive probability inferences. To address this issue, we propose the Bayesian Probability Forest (BPF) framework to reduce the calculation of probability inferences. By integrating mutual information and query frequency, we first propose a novel metric to learn the graphical structure of BPF, ensuring that the distance between high-frequency attributes and root node could be minimized. Then, we propose an efficient method for cardinality estimation in BPF. Specifically, the decomposition of cardinality estimation task is proposed to reduce the number of probability inferences, and elimination strategies are given to avoid the redundant calculations w.r.t. low-frequency attributes. Empirical investigations on real-world datasets demonstrate the superiority of our method over other cutting-edge competitors. Our codes are publicly available at https://github.com/CardEstGroup/bpf-ce.git.

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.

Data mining algorithms are often embedded in more complex systems, serving as the provider of data for internal decision making within these systems. In this paper we address an interesting problem of using data mining techniques for database query optimization. We introduce the concept of conditional cardinality patterns and design an algorithm to compute the required values for a given database schema. However applicable to any database system, our solution is best suited for data warehouse environments due to the special characteristics of both database schemata being used and queries being asked. We verify our proposal experimentally by running our algorithm against the state-of-the-art database query optimizer. The results of conducted experiments show that our algorithm outperforms traditional cost-based query optimizer with respect to the accuracy of cardinality estimation for a wide range of queries.KeywordsData WarehouseDatabase SchemaQuery OptimizationData Mining AlgorithmFact TableThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Cardinality estimation is the process to survey the quantity of tags in a RFID system. Generally, the cardinality is estimated by exchanging information between reader(s) and tags. To ensure the time efficiency and accuracy of estimation, numerous probability-based approaches have been proposed, most of which follow a similar way of minimizing the number of required time slots from tags to reader. The overall execution time of the estimator, however, is not necessarily minimized. The estimation accuracy of those approaches also largely depends on the repeated rounds, leading to a dilemma of choosing efficiency or accuracy. In this paper, we propose BFCE, a Bloom Filter based Cardinality Estimator, which only needs a constant number of time slots to meet desired estimation accuracy, regardless of the actual tag cardinality. The overall communication overhead is also significantly cut down, as the reader only needs to broadcast a constant number of messages for parameter setting. Results from extensive simulations under various tag IDs distributions shows that BFCE is accurate and highly efficient. In terms of the overall execution time, BFCE is 30 times faster than ZOE and 2 times faster than SRC in average, the two state-of-the-arts estimation approaches.

Query optimizers in RDBMSs search for execution plans expected to be optimal for given queries. They use parameter estimates, often inaccurate, and make assumptions that may not hold in practice. Consequently, they may select plans that are suboptimal at runtime, if estimates and assumptions are not valid. Therefore, they do not sufficiently support robust query optimization. Using ML to improve data systems has shown promising results for query optimization. Inspired by this, we propose Robust Query Optimizer, (Roq), a holistic framework based on a risk-aware learning approach. Roq includes a novel formalization of the notion of robustness in the context of query optimization and a principled approach for its quantification and measurement based on approximate probabilistic ML. It also includes novel strategies and algorithms for query plan evaluation and selection. Roq includes a novel learned cost model that is designed to predict the cost of query execution and the associated risks and performs query optimization accordingly. We demonstrate that Roq provides significant improvements in robust query optimization compared with the state-of-the-art.

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.

Streaming triangle counting is a critical issue in graph stream mining, with applications in dense subgraph discovery, web mining, anomaly detection, and more. Recent efforts have focused on estimating triangle counts in graph streams, primarily through sampling methods. However, because of limited memory resources for handling high speed streams, traditional sampling methods suffer from reduced sampling rate and thereby performance loss. In this paper, we propose a new compact data structure called uHLL to process edge streams by considering the tradeoff between estimation accuracy and memory efficiency. Furthermore, different from conventional triangle counting algorithms, we solve the estimation of union set cardinality for edge-local triangle count under both centralized and distributed framework, so as to efficiently estimate the global triangle count by a one-pass streaming algorithm. To the best of our knowledge, this is the first implementation of a distributed framework using a compact data structure for streaming triangle counting. We provide theoretical proof of unbiasedness and derive the variance of the union set and global triangle count. We compare our scheme with 11 algorithms, showing that under the same experimental setting, uHLL and distributed uHLL are at least <inline-formula><tex-math notation="LaTeX">$ 2.3$</tex-math></inline-formula> and <inline-formula><tex-math notation="LaTeX">$ 1.7$</tex-math></inline-formula> times more accurate than the state-of-the-art, respectively.