An Adaptive Model Cardinality Estimation Method

Abstract

The present invention relates to an adaptive model cardinality estimation method, comprising: obtaining a data set, adding noise to the data set based on an adaptive smoothing factor selection strategy, constructing a deep autoregressive model, inputting the data set with added noise into the deep autoregressive model to obtain a joint probability distribution; obtaining a plurality of predicates, sampling the predicates involved in the query in sequence according to the joint probability distribution to obtain sampled data, adjusting the selection rate of the deep autoregressive model through the sampled data, and multiplying the adjusted selection rate by the cardinality to obtain an estimation result. The present invention can achieve a more accurate estimation result.

Description

Technical Field

[0001] The present invention relates to the technical field of cardinality estimation in database query optimization, and particularly to an adaptive model cardinality estimation method. Background Art

[0002] Query optimizers are an indispensable component in modern database management systems (DBMSs). They transform queries into executable plans with optimal execution performance. They are crucial not only for relational databases but also for modern analytical engines such as Spark and Presto. Among these, cardinality estimation is a key component in query optimization, aiming to accurately, quickly, and efficiently estimate the number of tuples that satisfy a query without actually executing the SQL query and with a small amount of memory occupied. In addition, it is also often used in approximate query processing.

[0003] Unfortunately, despite many cardinality estimation methods having been proposed in the past few decades, cardinality estimation remains a well-known difficult problem. DBMSs may still produce large estimation errors for complex queries on large datasets and datasets with strong correlations.

[0004] At a high level, existing cardinality estimation work can be roughly divided into three categories: query-driven, data-driven, and hybrid-driven. The existing idea is to learn a regression model, which usually uses supervised learning to map a query to a function that predicts the cardinality. However, this method depends on training queries and has limited generalization ability to query changes and data changes. This method "naturally" assumes that the workloads from the training workloads are "similar" to the workload queries actually used by users. If you want to make up for this shortcoming, you need to let the model learn thousands of workloads of the real dataset to cover most queries. However, obviously, this cost is expensive. In addition, another fatal flaw of query-driven is robustness. If the data changes, all previous training data may become invalid. In contrast, existing data-driven methods can learn the distribution of the original data in the relational table without query workload, rather than training on typical queries, and then use this distribution to infer the cardinality. They do not need to know the query workload in advance and have much better robustness than query-driven. They can generalize to unseen queries. Even if the data changes, the previously trained data is still valid, such as Figure 1 .

[0005] Data-driven methods are divided into supervised learning and unsupervised methods. The main difference between the two is whether, in addition to the data set, a batch of queries and their corresponding cardinalities are also input into the model. The method of supervised data, in addition to sampling a part of the data in the sample as training data, also adds some queries and corresponding cardinality labels. Such methods generally include the following categories: 1) Use the gradient descent algorithm to adjust the bandwidth H and learn the distribution of the data. Since the effect of KDE is greatly affected by the bandwidth H, the performance is not good. 2) Use the Uniform Mixture model to calculate the area of the intersection region between the rectangle of the query and the rectangle of the data to answer the cardinality estimation problem. This method makes the assumption of uniform distribution within the bucket, so the error is relatively large in most cases. 3) In order to avoid training a model for each data set, a large number of data sets are used to train the model in advance. This method has strong generalization ability, but the accuracy is average.

[0006] Unsupervised Data Methods: Unsupervised data methods only require a dataset to learn a model. Such methods mainly include the following categories: 1) Learning the joint probability distribution by using the sum-product-network as a density estimator, and assuming conditional independence between SPNs. By learning multiple SPNs, each SPN is located on a subset of the relevant table. Due to the assumption, the error is relatively large in the tail case. Based on FS PN, the SPN is improved by adaptively decomposing the attribute dependence level to obtain PT(A) respectively. Similar to SPN, the FSPN structure and query probability can be recursively obtained in a top-down and bottom-up manner respectively, achieving better accuracy compared to DeepDB. 2) Methods based on the AR model. First, use the AR model as a point density estimator to convert the cardinality estimation problem into a joint probability distribution problem, achieving SOTA performance at that time. Combining the smoothing technique with the AR model on a single table based on Naru obtained better performance. Using the Fanout-scaling technique to extend the model to multiple tables, and using column decomposition to reduce the model size and significantly improve the accuracy of the benchmark test. However, for queries involving a very large domain size, the selectivity estimation of the AR model will become inaccurate because the number of samples cannot be too large in practice. Although Neurocard uses column decomposition to greatly reduce the model size, the sample space actually does not decrease. Subsequently, by integrating the Gaussian mixture model and the autoregressive model, the distribution of continuous attributes is fitted by multiple Gaussian mixture models, significantly reducing the size of the attribute domain. At the same time, the progressive sampling algorithm is modified, significantly improving the accuracy of the benchmark test. But it cannot handle queries with a low selectivity rate well, resulting in a relatively large tail error. 3) Methods based on Normalizing Flow. Use the normalizing flow model to learn the continuous joint distribution of relational data. In addition, an anti-quantization method is designed to enhance the continuity of the data, thereby improving the accuracy of real-world datasets. However, it should be noted that due to the need for multiple sampling iterations, this method requires a large amount of time and computing resources.

[0007] Hybrid-driven: In addition, there is a third category of cardinality estimation methods, namely the hybrid-driven method. The hybrid-driven method uses data as unsupervised information and the query workload as supervised information to learn the joint data distribution. It is the first model to use the query workload as supervised information and data as unsupervised information to learn the joint data distribution.

[0008] Data-driven cardinality estimation methods are generally superior to query-driven cardinality estimation methods, but data-driven methods also have some limitations. Traditional data-driven methods include histograms, sampling, and sketches. These methods also have their own drawbacks. They usually make independence and consistency assumptions that do not hold for real-world datasets. In addition, real-world datasets are often high-dimensional and contain large-domain continuous attributes. For these reasons, traditional methods cannot efficiently and accurately complete the cardinality estimation task. Although some existing data-driven methods have weakened the independence and consistency assumptions made by traditional methods, they still retain some independence or partial uniformity assumptions, so there are also some disadvantages. For example, the method based on sum-product networks assumes different degrees of independence between columns and recursively partitions rows and columns to learn the distribution on this basis. However, due to the remaining assumptions, the accuracy is relatively low.

[0009] In recent years, deep autoregressive cardinality estimators have been regarded as one of the state-of-the-art cardinality estimators. It is a data-driven method that can learn the distribution of joint data without independence assumptions and capture dependencies by decomposing the joint distribution into conditional probability distributions. Nowadays, the accuracy of existing advanced cardinality estimators for metrics such as median error and average error reflecting the estimation level is already satisfactory, but the tail error, especially the maximum error, is still not satisfactory. This is mainly attributed to two points. (1) There are columns with large-domain size continuous attributes in the data table, and these columns are very sparse relative to the entire data domain. It is difficult for the deep autoregressive model to learn the actual distribution of these attributes when learning the data distribution. Therefore, for columns with large-domain size continuous attributes, how to make the autoregressive model more easily capture the distribution of these attributes is the first challenge. (2) The deep autoregressive model can accurately handle point queries. Only one forward pass on the point (x1,..., x n ) is required to obtain the conditional sequence [Pb(X1 = x1)];...; [Pb(X n = x n |x1 = x1;...; x n-1 = x n-1)], and then multiply. However, for range queries, traversing and counting are very expensive because in the worst case, the entire table needs to be traversed. Subsequently, progressive sampling was proposed, which determines the sampling order and number according to the probability distribution of attributes. This method largely concentrates the samples in high-quality sub-regions and alleviates this problem to a certain extent. However, for some predicates with low selectivity, since no qualified points are sampled, the selectivity of a certain predicate may approach 0. Due to the autoregressive characteristic, the selectivity of the current predicate depends on the selectivity of the previous predicate, and the previous errors will gradually propagate backward, which may lead to a large gap between the estimated result and the actual result of the entire query. The original SOTA models Neurocard and IAM also showed large errors in the tail error for this reason. Therefore, for range queries, how to handle predicates with low selectivity and reduce the impact of error propagation on the results is the second challenge. Summary of the Invention

[0010] The object of the present invention is to provide an adaptive model cardinality estimation method to solve the problems existing in the above-mentioned prior art and achieve a more accurate estimation result.

[0011] To achieve the above object, the present invention provides the following solutions:

[0012] An adaptive model cardinality estimation method, comprising:

[0013] Obtain a data set, add noise to the data set based on an adaptive smoothing factor selection strategy, construct a deep autoregressive model, input the data set with added noise into the deep autoregressive model, and obtain a joint probability distribution;

[0014] Obtain a number of predicates, sample the predicates involved in the query in sequence according to the joint probability distribution to obtain sampling data, adjust the selectivity of the deep autoregressive model through the sampling data, and multiply the adjusted selectivity by the cardinality to obtain an estimated result.

[0015] Optionally, after obtaining the data set, it includes:

[0016] Scan the attribute column of the data set, obtain the domain values of the attribute column, assign the domain values to an integer value sequence to complete position encoding, and perform input encoding on the data set after position encoding to obtain the encoded data set.

[0017] Optionally, the method of adding noise to the data set based on an adaptive smoothing factor selection strategy is:

[0018]

[0019] where p oriis the original data distribution, p ad is the adaptive smoothing distribution introduced in this embodiment, p new is the new distribution, and x is

[0020] Optionally, the method for obtaining the adaptive smoothing distribution is:

[0021] p ad (t) = αp ad (t - 1)+(1 - α)p ori (t)

[0022] where p ad (t) is the distribution at the current time step, and α is the smoothing factor adaptively selected.

[0023] Optionally, the method for obtaining the joint probability distribution is:

[0024] P(x1, x2, x3,..., x n ) = M θ (x ad : x cp )

[0025] where P is, x n is, M θ is, x ad is, x cp is

[0026] Optionally, after constructing the deep autoregressive model, it includes:

[0027] Perform gradient denoising on the output result of the deep autoregressive model, and train the deep autoregressive model in combination with the maximum likelihood loss to obtain the trained deep autoregressive model.

[0028] Optionally, adjusting the selection rate of the deep autoregressive model includes:

[0029] S1. Set the first target threshold, input the sampling result into the trained deep autoregressive model to obtain the data range;

[0030] S2. Perform Monte Carlo integration on the data range to obtain the first error, perform error judgment on the first error. If the first error is less than the first target threshold, adjust the selection rate of the trained deep autoregressive model to obtain the total selection rate;

[0031] S3. Set the second target threshold, judge the product of the total selection rate and the base number and the target threshold. If the product is greater than the second target threshold, output the adjusted selection rate. If the product is less than the second target threshold, resample the predicate and increase the sample.

[0032] Optionally, the method for performing Monte Carlo integration on the data range is as follows:

[0033]

[0034] where sel(θ) is the selectivity of the current query, x is an attribute or column of the input relation, R n is the corresponding column, and p(x) is the probability distribution of the current x.

[0035] The beneficial effects of the present invention are as follows: When a query is executed in a database, there are multiple query execution plans, and different query execution plans correspond to different costs (time, memory, etc.). Therefore, the purpose is to find an execution plan with a relatively small execution cost (the reason why it is not the smallest is that there are many execution plans, and it is too time-consuming to search all execution plans. If a relatively small one has been found, there is no need to search for the remaining ones). The premise of cost estimation is cardinality estimation, which is the task of the present invention. Only when the cardinality estimation is more accurate can a good execution plan be selected. The model of the present invention has achieved the SOTA effect of existing methods. Description of the Drawings

[0036] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required in the embodiments. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0037] Figure 1 It is a schematic diagram showing the difference between the supervised and unsupervised data-driven methods of the embodiments of the present invention;

[0038] Figure 2 It is a flowchart of an adaptive model cardinality estimation method according to an embodiment of the present invention;

[0039] Figure 3 It is a schematic diagram showing the maximum estimation error of different methods in the IMDB dataset according to an embodiment of the present invention;

[0040] Figure 4 It is a schematic diagram showing the training time of each estimator according to an embodiment of the present invention;

[0041] Figure 5 It is a schematic diagram showing the end-to-end time of different methods according to an embodiment of the present invention;

[0042] Figure 6 It is a schematic diagram showing the increment of changing the smoothing factor according to an embodiment of the present invention. Detailed Embodiments

[0043] The following will clearly and completely describe the technical solutions in the embodiments of the present invention with reference to the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without making creative efforts shall fall within the protection scope of the present invention.

[0044] To make the above objects, features, and advantages of the present invention more obvious and understandable, the present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0045] As Figure 2 shown, the present invention discloses an adaptive model cardinality estimation method, including: the left side is the model training stage, and the right side is the online inference stage. The overall architecture is to train a model using a data table offline for online use. First, the data table is input into the model as training data. The neural network cannot directly understand the original data and needs to encode the data. After encoding, in this embodiment, an adaptive smoothing factor selection strategy is used to add different degrees of noise to the data. Different distributed data should be added with different degrees of noise, and how much noise should be added to the distribution is obtained through the adaptive smoothing factor selection strategy. After the smoothing strategy, the original distribution will become smoother, and the autoregressive model is easier to learn the distribution of the smooth data. The smoothed distribution is input into the deep autoregressive model, and the model will output a joint probability distribution. In the model training stage, a problem needs to be considered. In this embodiment, noise is added to the original distribution. In fact, what this embodiment really needs is the original distribution of the data. Therefore, this embodiment needs to consider making the model learn to remove the added noise and make the smoothed distribution closer to the original distribution. The model is trained through the maximum likelihood loss (the maximum likelihood is obtained by calculating the cross-entropy loss between the distribution of the original data and the learned distribution). Online inference stage: The input is a batch query. According to the previous work, query construction is required. Then, in this embodiment, the joint distribution obtained in the training stage is used to sample each attribute column in turn according to the query Q = [R1, R2, R3,..., R n . The sampled data is passed into the autoregressive model to obtain the joint probability. Among them, but sometimes the probability obtained in the middle is an outlier. In this embodiment, this error is alleviated by resampling and increasing the number of samples. After the selection rate adjustment, the total selection rate is multiplied by the model cardinality to obtain the result estimated for the current query.

[0046] Figure 2 illustrates the overall process of AdaCard, which is divided into two main stages: the offline training stage and the online inference stage. Initially, in the offline training stage, a training data model is trained for subsequent online use. As Figure 2 shown on the left, it takes the original data as input to obtain xori Then encode the data to produce x de , and then perform a data copying operation. A part of the data undergoes adaptive smoothing convolution to produce x ad , while another part remains unchanged. Subsequently, the two sets of data are concatenated to form the input x of the AR model in . In the AR model, the input tuple is encoded as discrete dictionary encoding. The encoded data is embedded using an embedding matrix, followed by a series of residual blocks. Then the output is added to the embedding matrix to obtain logits, and the final output is obtained through backpropagation. In the inference stage, the process involves taking the query as input and constructing q based on the original query qc . According to the learned distribution, continuously sample the data to obtain the joint probability. If an abnormal selection rate is detected during this process, the selection rate needs to be adjusted (after sampling the data, input the sampled results into the autoregressive model to obtain the joint probability distribution, and at this time, the abnormal selection rate can be obtained, and then the selection is adjusted). The specific steps involve using adaptive resampling to correct the selectivity. Finally, multiply the total selectivity by the cardinality of T to obtain the estimated result

[0047] In the online inference stage, use the trained model, input the query query into the model, and then output the estimated cardinality of the current query, which is the main task in the inference stage

[0048] A query can be composed of multiple predicates, so the original query can be converted into q ori [R1, R2, R3,..., R n . This embodiment refers to the previous work. More specifically, the previous work converted the large-domain continuous attribute into a new attribute, and this conversion can significantly reduce the domain size of the current attribute. Since the attribute is changed, the original query needs to be modified, that is, the query construction part, and it is converted into q through query construction qc [R1, R2, R3,..., R n . Next, according to the probability distribution learned during training, sample data points in the data in sequence to obtain a series of data point sets s. What is stored in s is a series of points. sp1 represents the point sampled according to R1, sp2 is the point sampled according to R2, and similarly sp n is the point sampled according to R n . After passing through the autoregressive model, a series of joint probabilities are obtained In this embodiment, according to the obtained joint probability, some abnormal selection rates may be adjusted, so some of the joint probabilities after adjustment become Finally, multiply the total selection rate by the cardinality to obtain the estimated result

[0049] Coding strategy

[0050] In order for AdaCard to train an autoregressive model subsequently, this embodiment adopts the coding and decoding strategies in previous work (54, 55 of IAM), encodes the input into a format recognizable by the model, and converts the output of the model into a probability distribution form for better modeling. Deep learning methods have been proven to be able to effectively encode and decode data outputs. The model in this embodiment requires that the encoded tabular data can be input into the neural network and can be successfully decoded by the autoregressive model. At the same time, this embodiment ensures that the output of the model is converted into a probability distribution format, retaining the necessary information to ensure that data correlation is not lost.

[0051] Generally speaking, there are three common data types in a data table: numerical type, categorical type, and string type. For AdaCard, regardless of all data types, this embodiment maps the domain values to a series of integer values: [0, |A i |).

[0052] To handle range queries, the model needs to know the size relationship between data. Specifically, first scan all the attribute columns of the original data to obtain the domain size |A i | of all the attribute columns, and then sequentially assign |A i | different values to [0, |A i |). This mapping will maintain the original order of the domain values. For example, if Dom(Ai) = {iPhone, huawei, MI}, the encoding will be {iphone→1, huawei→0, MI→2}, following the lexicographical order. This order encoding operation conforms to the property of bijective and will not cause loss. For each encoded attribute |A i |, the output layer will assign a |A i -dimensional vector of the distribution For example, for Dom(Ai) = {iPhone, huawei, MI}, an example of the output distribution is 0.1, 0.6, 0.3.

[0053] After completing the position encoding, in order to obtain semantic information, it is necessary to perform input encoding on the data. In the field of machine learning, there are various common encoding methods to choose from to effectively represent the data. These encoding methods include one-hot encoding, binary encoding, label encoding, embedding, ordinal encoding, frequency encoding, and so on. Each encoding method has its unique advantages and disadvantages and is suitable for different scenarios and data types. Selecting the appropriate encoding method can have an important impact on the model performance. One-hot encoding is simple and easy to implement, but when the domain size of an attribute is relatively large, it will result in a high-dimensional coefficient matrix, increasing the computational and storage overhead. Binary encoding reduces the dimension compared to the former, but may still introduce problems of relatively high dimensions. In addition, it may introduce too much relational information, which may mislead the model. For embedding encoding, high-dimensional discrete data can be mapped to a low-dimensional continuous space to capture the relationships between features, but it is necessary to learn an embedding matrix, and the additional overhead will be relatively large. In this embodiment, a compromise solution is adopted. If the domain size of the attribute column is greater than a value, this embodiment selects embedding encoding; otherwise, this embodiment selects to use one-hot encoding or binary encoding. In the model of this embodiment, this embodiment selects 100, 500, and 1000 as the thresholds for selecting which encoding method.

[0054] Adaptive Smoothing Factor Selection Strategy

[0055] When the AR model processes tabular data as unsupervised information, it often encounters challenges due to the wide range of values in the real dataset. In a typical scenario, the values in the tabular data have a wide range, while the number of tuples is limited. Therefore, the distribution of data points within the value range tends to be sparse. The sparsity of the tabular data distribution may have an adverse impact on the performance of the model globally. Therefore, data preprocessing is crucial for improving the overall performance. One way to address the sparse distribution problem is to introduce a smooth distribution to the original data. The addition of this smooth distribution enhances the robustness of the sampling operation and provides better anti-interference ability. In addition, combining the smooth distribution can generate positive feedback for cardinality estimation, thus helping to improve the results. To facilitate the learning of the data distribution, this embodiment adopts ASFSS. This strategy aims to make it easier for the AR model to learn the data distribution. Specifically, this embodiment introduces the following operations:

[0056]

[0057] where p ori represents the original data distribution, p ad represents the adaptive smooth distribution introduced in this embodiment, and p new represents the new distribution. This embodiment uses p ad for p oriSmoothing is performed to obtain p new . Where p ad In this embodiment, it is obtained in the following manner:

[0058] p ad (t) = αp ad (t - 1)+(1 - α)p ori (t)

[0059] The expression p ad (t) represents the current time step distribution, where α is a smoothing factor selected adaptively, and p ad (t - 1) is the distribution of the previous time step after smoothing, and (1 - α) is the corresponding weight coefficient of α. p ori (t) represents the original distribution of the current time step. To obtain the best result, this embodiment recommends setting the bucket sequence D for the domain size |A i |. Different buckets represent different thresholds and smoothing factors α. The α in each bucket is obtained through the smoothing factor increment Δα, that is, the α of the current bucket is obtained by adding Δα to the α of the previous bucket. In this way, the data is adjusted to different degrees. Intuitively, the task of ASFSS is to use a smaller smoothing factor when the data change is not significant and retain the original data distribution. When the data mutates, a larger smoothing factor is used to smooth the original data. To prevent this smoothing technique from over-changing the original data, a copy of the original data is created before applying the smoothing. Finally, the processed data and the unprocessed data are concatenated to form the input vector of the AR model in the method of this embodiment. Therefore, in the model of this embodiment, the joint probability distribution calculated by autoregression can also be expressed as:

[0060] P(x1, x2, x3,..., x n ) = M θ (x ad : x cp )

[0061] Where P is the joint probability distribution of the current query, x n is the data corresponding to the predicate involved in the query, M6 is the autoregressive model, x ad is the data after the adaptive smoothing strategy is performed, and x cp is the original data.

[0062] Deep autoregressive model

[0063] The AR model is a feed-forward deep learning model that approximates the joint distribution by using previous data to predict future values. Intuitively, when given a tuple as input, the model outputs a series of probability distributions conditional on prior attributes. This can be expressed using the following formula:

[0064]

[0065] where M(x) is the output of the autoregressive model, is the conditional probability under the condition of X1, is the probability that X2 satisfies under the condition of X1.

[0066] In this embodiment, a neural network is assigned to each column a i and the AR property of the current column is achieved through information masking. The input includes information about the previous column values, and the neural network of the current column uses the context information to output the distribution within its own domain. It should be noted that the first output P(X1) of the first column does not depend on any attribute value. Subsequently, the second output P(X 2| x1) depends only on the attribute value of the previous column, and the third output P(X3|x1, x2) depends only on the values of the previous two columns. During the training process, the AR model learns its weights by minimizing the difference between the true distribution and the estimated distribution. The measure of this difference is the cross-entropy loss.

[0067]

[0068] where Loss AR is the loss function of autoregression, is the cross-entropy between the true probability distribution P and the estimated probability distribution P, P(t) represents the true probability distribution, indicating the true probability of the tuple t in the dataset. logP(t) represents the natural logarithm of P(t). In the loss function, logP(t) is used to measure the difference between the prediction of the model for the probability distribution of the tuple t and the true probability distribution. By minimizing logP(t), the model can learn to estimate the probability distribution of the tuple t more accurately, thereby improving the accuracy of the selectivity estimation. This embodiment adopts the backpropagation algorithm and uses the stochastic gradient method to minimize the loss and optimize the model parameters. Different AR models can achieve different degrees of accuracy. AdaCard supports any AR model M, including MADE, Transformer, etc. Previous studies have shown that Transformer can achieve higher accuracy but will bring a large amount of time and space overhead. MADE has lower time and space costs but needs to improve accuracy. ResMADE has achieved a good balance between accuracy and efficiency and provides satisfactory performance. The model of this embodiment also uses ResMADE as the AR estimator. Nowadays, there are many variants of the Transformer-based models, and researchers have modified the Transformer model, such as the linear Transformer. Different models show differences in terms of cost and accuracy, but this is not the focus of this embodiment. This embodiment will consider the performance of different models in future work.

[0069] Eliminate the introduced noise

[0070] After adaptively smoothing the data, the original distribution becomes the smoothed distribution p new . The AR model is better at learning from the distribution p new . AdaCard uses p new as the input for model training. However, since the model is trained on a modified version of the original distribution, bias is inevitably introduced. To solve this problem, the goal of this embodiment is to enable the model to learn the characteristics of the original data distribution. Therefore, it is necessary to eliminate the introduced bias. After selecting the adaptive smoothing factor, this embodiment knows the degree of smoothing applied to the original distribution. Therefore, this embodiment performs gradient denoising on the output p out (joint probability distribution) of the model. Specifically, this embodiment needs to perform the following operations:

[0071]

[0072] where x ad represents the smoothed data, and α represents the smoothing factor applied to the original data. This method ensures that the learned distribution is closely consistent with the original distribution. AdaCard achieves more accurate estimation accuracy by introducing noise. Based on this method, this embodiment needs to consider another problem: how to balance the difficulty of the model learning the smoothed distribution and the difficulty of eliminating the added noise. Assume that p ad is the noise distribution introduced in this embodiment. If p ad is too large, it will be difficult for the model to eliminate the introduced noise. On the other hand, if p ad is too small, the ability of the model to learn the smoothed distribution will be affected. Therefore, an appropriate smoothing factor is crucial for cardinality estimation. This embodiment experiments with hyperparameters later and selects a suitable smoothing factor increment, which is used as a hyperparameter of the model, such as Figure 5 .

[0073] Selection rate adjustment

[0074] The AR model can easily handle answering equality queries but is difficult for range queries because the model cannot sample all data points within the specified range. For a given range query q, R = R1 ∧ R2 ∧ R3,.., R n , an intuitive method is to iterate over all tuples, but this incurs a large amount of time and space costs because R can contain O(Π i |A i)。As previously mentioned, due to the inherent nature of the AR model, the selectivity of the output current column depends on the previously obtained selectivity. If a significant error occurs in an intermediate result, this error will propagate throughout the result, leading to a serious miscalculation of the final selectivity estimate. Previous SOTA methods also faced this challenge. They were unable to sample the actual distribution of queries with low selectivity, resulting in serious tail errors. This problem constitutes a bottleneck for most estimators to improve the estimation performance. During query inference, given the result of sampling for the trained model M and the predicates of query Q, the initial step is to convert the predicates into ranges and perform Monte Carlo integration on them:

[0075]

[0076] The error of Monte Carlo integration is The numerator represents the variance of the set If is a constant, that is then the variance of the set is 0. Obviously, this situation hardly exists in actual datasets. The denominator represents the number of samples. By increasing the number of samples, the error can be reduced to a certain extent. This conclusion is independent of the dimension of the integration. To alleviate the above problems and improve the accuracy of Monte Carlo integration, this embodiment adopts resampling and increasing the number of samples. Increasing the denominator of the error through resampling helps to mitigate the introduced error. This improvement is reflected in the model by increasing the number of samples. Generally speaking, it is unrealistic to increase the sample size of all queries. The method of this embodiment only increases the number of samples for a single query and will not significantly affect the inference latency. Now, when should the model trigger resampling? Although resampling may have been performed at each step before, for each predicate, the present invention only performs resampling once. After performing it once, in most cases, there will be no less than 1, but sometimes it is actually less than 1. Therefore, the overall result may still be less than 1 at the end, and a selectivity adjustment will be made. Therefore, this embodiment recommends that during query inference, when the product of the current overall selectivity and the cardinality of table T is less than 1 (the cardinality of table T represents the number of tuples in table T), this embodiment recommends making a selectivity adjustment. This embodiment makes such an improvement because the current data is generally relatively large, and there are few cases where a query has no tuples that meet the conditions. Specifically:

[0077] Set a first target threshold, input the sampling result into the trained deep autoregressive model to obtain a data range;

[0078] Perform Monte Carlo integration on the data range to obtain a first error, make an error judgment on the first error. If the first error is less than the first target threshold, perform a selectivity adjustment on the trained deep autoregressive model to obtain the total selectivity;

[0079] Set a second target threshold, and judge the product of the total selection rate and the base number and the target threshold. If the product is greater than the second target threshold, output the adjusted selection rate. If the product is less than the second target threshold, resample the predicate and increase the sample size.

[0080] Among them, both the first target threshold and the second target threshold are 1.

[0081] Obtaining the total selection rate includes: A query may have multiple query predicates. For example, there are three query predicates, x>1 and y>2 and z>3. First, the present invention samples the first predicate according to the data distribution learned previously, and obtains the selection rate according to the sampling result. Then, the present invention samples the second predicate according to the selection rate obtained in the first step to obtain the total selection rate of the first and the second. Then, the selection rate of the third is obtained by sampling in the same way... In turn, the present invention obtains the total selection rate of a query.

[0082] If the product is less than the target threshold, resampling the predicate and increasing the sample size includes: If the product of the total selection rate obtained from an intermediate result and the base number is a result less than 1, the present invention considers that the currently sampled predicate is an abnormal situation, re-increases the sampling of the current predicate, and only needs one time (here because the present invention has increased the sampled data. If the obtained result is still abnormal, it may be that this predicate is relatively restricted in the original data). Finally, the obtained total selection rate.

[0083] After that is the process of selection rate adjustment. If the product of the current total selection rate and the base number is less than 1, the present invention expands the selection rate until the result after multiplying the selection rate and the base number is greater than 1. If the result after multiplying the final selection rate and the base number is already greater than 1, the result of directly multiplying by the base number is the result of base number estimation.

[0084] In addition, this embodiment also optimizes the final selectivity. Specifically, using the same condition that triggers resampling (that is, when the product of the overall selectivity and the base number of the current table T is still less than 1), this embodiment identifies it as an abnormal situation. When the number of decimal places of the overall selectivity exceeds the number of digits of the total base number, this embodiment chooses to expand the overall selectivity until the number of decimal places matches the total base number. This improvement does not affect queries that have been well estimated, but significantly enhances queries with abnormal selectivity. Experimental results show that this enhancement greatly reduces the tail error in Q-error.

[0085] This embodiment utilizes three real-world data sets with unique challenging features, each of which has been evaluated in at least one study within its respective field. Table 1 provides the description of each data set. "Rows" and "Columns" represent the number of rows and columns involved in the current data set evaluation respectively. "Domain" represents the product of all domain sizes, while the "Features" list describes the features of each data set or the query workload itself. It is worth noting that this embodiment pays more attention to the performance of AdaCard on the IMDB data set because the IMDB data set is closer to the real-world scenario involving the use of multiple tables, as shown in Table 1, which is the data set for evaluation.

[0086] Table 1

[0087]

[0088] WISDM:. This real data set is the accelerometer and gyroscope time series sensor data collected from the mobile phones and smart watches of 51 test subjects performing 18 activities. There are a total of 6 columns, and this embodiment uses 5 columns with categorical and continuous attributes: subject_id (categorical attribute, 51), activity_code (categorical attribute, 18), x (continuous attribute, 1×106), y (continuous attribute, 1×106), and z (continuous attribute, 1×106). The numbers in the brackets represent the domains of the attributes. It has been shown in previous studies that WISDM has a strong correlation, while WISDM has a weak skewness.

[0089] DMV: This data set is a data set containing vehicle registration information in New York City. Due to the more skewed data and more complex relationships in this data set, it has been applied to the cardinality estimation task many times.

[0090] To test the accuracy of join queries and the end-to-end execution time of the query optimizer, this embodiment also uses a real IMDB dataset with additional attributes. To test the accuracy of join queries and the end-to-end execution time of the query optimizer, this embodiment also uses a real IMDB dataset with additional attributes. IMDB has been proven to be a good dataset for selectivity estimation evaluation in previous work because it exhibits strong attribute correlations. This embodiment tests two workloads, "Job-Light" and "Job-Light-Ranges", on IMDB, which have significantly different characteristics. The first query workload consists of 70 queries involving joins of 6 tables, where each non-primary table is joined only to "title" on "title.id". The full outer join contains 2×10^12 tuples. Each query involves joining 2 to 5 tables. Regarding filters, this embodiment uses a range filter on "title.product_year" and equality filters on other attributes. Compared with the first query workload, the second query workload increases the estimation difficulty. First, the number of queries increases to 1000. Second, more tables need to be joined, involving a total of 18 join graphs. Finally, this embodiment adds more predicates to each query, including a larger number of range predicates.

[0091] Baseline model

[0092] This embodiment compares the model of this embodiment with various estimators, including classical methods, query-driven methods, data-driven methods, and hybrid-driven methods

[0093] 1. PG, this embodiment selects Postgres version 9.6.6 for specific reasons, mainly because it retains the one-dimensional histogram of the data.

[0094] 2. KDE, which models the continuous data distribution using a Gaussian kernel. This embodiment uses open-source code and applies existing parameters.

[0095] 3. MSCN, as a SOTA query-driven cardinality estimation method. This embodiment adopts its open-source code. For each dataset used in this embodiment, this embodiment uses 105 queries generated in the same way as the workload to train the model.

[0096] 4. BayesNet, which is a Bayesian network-based cardinality estimator that has shown high precision beyond other classical estimators in previous work. This embodiment follows the parameter settings in previous studies and uses an implementation based on the Chow-Liu tree method to construct the Bayesian network structure because this method can provide the best performance for BayesNet.

[0097] 5. DeepDB, which is a method for learning joint data distributions based on sum-product networks (SPNs). The open-source code of it is utilized in this embodiment. For fair comparison, the sample size of each SPN is set to 1 million in accordance with previous work. As hyperparameters, this embodiment uses a 0.3 RDC threshold and a 1% minimum instance slice of the input data, which determines the granularity of clustering.

[0098] 6. UAE and UAE-Q, as representatives of hybrid-driven estimators, learn joint data distributions from data and queries using an AR model. UAE-Q only learns joint data distributions from queries.

[0099] 7. Naru, which showed excellent accuracy in previous work, has its source code adopted in this embodiment.

[0100] 8. Neurocard, an extension of Naru, further enhances the support for join operators, extends cardinality estimation to multiple tables, and optimizes Naru through column decomposition. This can better handle large-domain categorical attributes and significantly reduce the size of the model. The source code of Neurocard is utilized in this embodiment and its settings are followed, including setting the sample size to 8,000 samples.

[0101] 9. IAM, as the SOTA method in recent cardinality estimation research, is built on Neurocard and fits the distribution of continuous attributes and effectively reduces its domain size by integrating multiple Gaussian mixture models and AR models. The method of this embodiment is further improved based on IAM, achieving a balance between accuracy and efficiency.

[0102] Evaluation metrics: This embodiment reports Q-error as an indicator of estimation accuracy:

[0103]

[0104] where est(q) represents the estimated result and act(q) represents the actual result. For example, if the actual result is 100 but the final estimate of the base estimator is 10, the generated Q-error will be 10. Q-error is a metric widely adopted by most learning methods. It allows for equal penalties for large and small results. As reported in previous studies, reducing errors at higher percentiles is more challenging than the mean or median. Most existing base estimators perform well in the median case, so this embodiment reports the median error, as well as the errors at the 95th and 99th percentiles and the maximum error.

[0105] Hyperparameter Settings: For datasets such as IMDB, DMV, and WISDM, in this embodiment, five hidden layers are configured for the MLP, and each hidden layer has the following number of neurons: [512, 256, 512, 128, 1024]. In this embodiment, grid search is used to determine the following hyperparameters, which helps the model achieve good results. In this embodiment, the number of buckets for ASFSS is set to 100, and the smoothing factor increment is 0.05. For regular query sampling, the size is set to 10k, and the resampling configuration is 50k.

[0106] Experimental Environment: All experiments are conducted using Python. In this embodiment, a server equipped with a 16-core CPU, a single NVIDIA GeForce RTX 3090 GPU, 24GB VRAM, and 128GB RAM is used to report latency and throughput.

[0107] Experimental Results: Tables 2 and 3. Table 2 shows the estimation errors on the WISDM and DMV datasets, and Table 3 shows the estimation errors on the IMDB dataset. The q-errors of different CE methods on the three datasets are reported, and the lowest errors are highlighted in bold. In this embodiment, it is observed that AdaCard exhibits higher accuracy on many datasets compared to other estimators. Overall, on a single table, the model in this embodiment is 1.33 - 7061 times faster than Postgres, 1.06 - 97 times faster than MSCN, 1.06 - 1427 times faster than DeepDB, 1.15 - 5 times faster than Neurocard, and 1 - 2 times faster than IAM. On multiple tables, the model in this embodiment is 9 - 603 times better than Postgres, 3 - 40 times better than MSCN, 2.13 - 293 times better than DeepDB, 1.10 - 9 times better than Neurocard, and 1.06 - 9 times better than IAM. Next, this embodiment discusses the results.

[0108] Table 2

[0109]

[0110] Table 3:

[0111]

[0112] (1) As expected in this embodiment, PG shows large errors on the dataset. This is mainly due to the independence assumption made by PG. When the dataset contradicts this assumption, especially in the case of strong correlations, its accuracy will decrease significantly.

[0113] (2) KDE performs moderately, with good median error, but performs poorly in cases involving large domains or categorical attributes, especially in the tails of the distribution. This may be attributed to the difficulty of the Gaussian kernel in modeling the distribution of discrete data.

[0114] (3) BayesNet performs relatively well except for the maximum error. This may be because the Bayesian network relies on the assumption of conditional independence. When the dataset exhibits strong correlations, the estimation results may deteriorate significantly.

[0115] (4) Compared with multi-table datasets, MSCN performs slightly better on single-table datasets. It can handle sampling algorithms well, but still exhibits high errors in the tails of the distribution. A large part of this can be attributed to the fact that MSCN heavily relies on the query distribution obtained through training. However, training queries are usually difficult to accurately represent the original data distribution, resulting in higher errors.

[0116] (5) UAE performs exceptionally well on single-table datasets, mainly because it does not rely solely on samples. Similar to other query-driven methods, it performs poorly on strongly skewed datasets due to the difficulty in accurately representing the data distribution. UAE's performance is relatively better than UAE-Q.

[0117] (6) DeepDB performs well in terms of median error. However, its performance is particularly dependent on the correlation of attributes. When dealing with strongly correlated attributes, especially in the tails of the distribution, the accuracy drops significantly.

[0118] (7) Naru and Neurocard, both Naru and Neurocard achieved good results on the benchmark metrics. However, Naru faces challenges in training due to the large domain size, while Neurocard alleviates this problem through column decomposition technology, but at the cost of a certain degree of accuracy.

[0119] (8) IAM demonstrated excellent performance on most datasets. The model uses GMM to reduce the domain size, thereby making the sample space smaller, reducing the search space, and achieving higher precision with the same number of samples. However, like Neurocard, IAM has difficulty handling some low-selectivity queries, resulting in suboptimal estimation results.

[0120] (9) AdaCard achieved the best performance in terms of tail error and average error on most datasets. It is worth noting that similar models such as Naru, Neurocard, UAE, and IAM are all based on the AR model. However, the model in this embodiment is superior to these AR-based models to varying degrees in terms of median, 95th, 99th, and maximum errors.

[0121] Main findings: Compared with other basic estimator models, AdaCard achieves lower errors, which this embodiment mainly attributes to two reasons. First, ASFSS can be used to adjust the original data, making it easier for the AR model to learn the data distribution. Second, selective adjustment allows adjustment in the case of low selection rate queries, reducing the impact of error propagation. In summary, AdaCard exhibits higher accuracy. In the tail scenario, its performance is 17 times higher than UAE, nearly ten times higher than Neurocard, and nearly ten times higher than IAM.

[0122] Training cycles and estimation errors:

[0123] To test the impact of training cycles on query estimation errors, this embodiment uses two different query workloads on the IMDB dataset to evaluate the impact of training cycles on various query estimators, as Figure 3 shown. The experimental results show that the early training of AdaCard is crucial because AdaCard can converge to excellent estimation performance in just 7 training cycles. In fact, during the testing process of this embodiment, AdaCard often achieved near-optimal estimation performance as early as the second training epoch. In addition, the AR-based method consistently produced good results within 7 epochs, while UAE and DeepDB required more epochs, specifically 13 and 11 epochs respectively to reach convergence.

[0124] This embodiment evaluates the training time of different methods. Since each estimator can be quickly trained on a single table, this embodiment uses the IMDB job-light-ranges workload to obtain significant comparison results. Figure 4 and Figure 5 reports the training time and end-to-end time of each estimator. The training times are listed from the longest to the shortest as follows: AdaCard > IAM > Neurocard > DeepDB > UAE > MSCN. AR models generally require longer training times because they need to learn more compared to other methods. IAM needs to learn the GMM and AR models in advance, while AdaCard may require data preprocessing and even resampling. MSCN has the shortest training time, mainly because the dataset size is much larger than the size of the training queries.

[0125] To compare the end-to-end query execution times of AdaCard and other cardinality estimators in practical applications, this embodiment conducts a comparative experiment. This embodiment modifies the query optimizer in Postgres to allow it to accept externally provided selectivity estimates. For all three datasets, this embodiment selects the more complex and challenging IMDB dataset with more complex join scenarios. For each query, this embodiment uses the modified Postgres to collect the selectivity estimates of all subqueries provided by AdaCard and other cardinality estimators and uses them for query optimization.

[0126] This embodiment shows the average end-to-end query execution times of different estimators. Similar to many studies, this embodiment observes that UAE and Neurocard achieve comparable accuracy on IMDB. This embodiment makes two important findings. First, the high accuracy of the cardinality estimator has a positive impact on the query optimizer to generate a more optimized query plan. Through experiments, this embodiment finds that AdaCard can achieve more accurate estimates than other existing estimators, almost matching the query execution efficiency of IAM. Compared with Postgres, AdaCard is 600 times more accurate than PG in terms of accuracy. However, in terms of end-to-end execution time, AdaCard is 1.86 times higher than PG. In terms of accuracy, AdaCard exceeds IAM by nearly 10 times, and the end-to-end time is slightly slower, probably due to resampling involved, which may affect the execution efficiency to a certain extent. Compared with NeuroCard, the accuracy of AdaCard is 10 times that of NeuroCard, and the end-to-end time is increased by 1.35 times. Compared with other cardinality estimators, AdaCard shows superior performance in terms of both accuracy and end-to-end execution efficiency.

[0127] Based on the above comparative experiment, this embodiment believes that even if two different cardinality estimators produce different Q errors, they may still produce the same query execution plan. In addition, even if the query optimizer selects different query execution plans, the end-to-end query execution time may still be the same. Previous research experiments also show that a lower Q error does not necessarily mean an optimal execution plan and the shortest end-to-end time. This shows that an increase in estimator accuracy does not necessarily translate into a reduction in query execution time. However, the Q error can be used as an effective and convenient metric to represent the quality of the estimation results.

[0128] Changing the smoothing factor increment

[0129] To gain a deeper understanding of the impact of different increments of the smoothing factor on the performance of the AdaCard estimator, this embodiment evaluates the estimation accuracy of the model on three datasets. According to the experience of this embodiment, the number of buckets is set to 100, and different increments of the smoothing factor are represented by Δα. A larger Δα indicates that more noise is added to the original distribution, resulting in a smoother processed distribution. This embodiment selects the maximum error as the metric for plotting the graph.

[0130] The experimental results are as Figure 6 shown. It can be seen that the influence of Δα on cardinality estimation shows a trend of increasing first and then decreasing. When Δα is close to 0, it means that the data in each bucket is smoothed to the same extent, which makes it challenging for the AR model to learn the distribution. As Δα gradually increases, indicating different degrees of smoothing added to the original distribution, the AR model finds it easier to learn the data distribution. When Δα exceeds 0.05, it indicates that too much noise is added to the original distribution, and the denoising ability of the model begins to decline. At this time, the estimation performance of AdaCard starts to deteriorate rapidly.

[0131] Cardinality estimation is an important part of the database query optimizer. After decades of research, using autoregressive models for cardinality estimation has proven to be of extraordinary accuracy. However, when the query involves a large range of continuous attributes, the sparsity of the data becomes particularly prominent.

[0132] This challenge makes it difficult for the cardinality estimator to capture the correct distribution, resulting in abnormal selectivity. In addition, due to the nature of the autoregressive model, errors tend to propagate, further reducing the accuracy. To address these challenges, this embodiment proposes a new adaptive cardinality estimator, AdaCard. On the one hand, this embodiment uses an adaptive smoothing factor strategy to adjust the sensitivity of the model to the original data, thereby reducing the impact of data sparsity. On the other hand, considering the reasons for errors introduced by Monte Carlo sampling, this embodiment performs resampling on low-selectivity predicates to improve the accuracy of these predicates and reduce error propagation.

[0133] In addition, this embodiment adjusts the abnormal selection rate to improve the accuracy of the estimation results. This embodiment makes an accurate approximation of the joint data distribution without making any independence assumptions. Through evaluations using multiple real-world datasets, this embodiment compares the method of this embodiment with actual systems and mainstream baseline models. The final experimental results show that the estimator of this embodiment exhibits the lowest estimation error at the tail and achieves almost a 10-fold improvement in accuracy compared to the second-best method while maintaining similar latency and model size.

[0134] The innovation points compared with the previous work are mainly reflected in two aspects. One is the adaptive smoothing factor selection strategy, which is in the model training stage because the autoregressive model is more likely to learn smooth distributions. The second is the selection rate adjustment, which is reflected in the query inference stage. According to the reasons for the errors generated by Monte Carlo sampling, the abnormal selection rate obtained by the autoregressive model is adjusted, greatly reducing the tail error of cardinality estimation.

[0135] The embodiments described above are only descriptions of the preferred embodiments of the present invention, and do not limit the scope of the present invention. Without departing from the design spirit of the present invention, various deformations and improvements made by those of ordinary skill in the art to the technical solutions of the present invention shall fall within the protection scope determined by the claims of the present invention.

Claims

1. An adaptive model cardinality estimation method, characterized in that, Including: Obtain a data set, add noise to the data set based on an adaptive smoothing factor selection strategy, construct a deep autoregressive model, input the data set with added noise into the deep autoregressive model, and obtain a joint probability distribution; The method of adding noise to the data set based on an adaptive smoothing factor selection strategy is: Among them, is the original data distribution, is the adaptive smoothing distribution, is the new distribution, is the attribute or column of the input relationship; The method of obtaining an adaptive smoothing distribution is: Among them, is the current time step distribution, is the smoothing factor adaptively selected, is the distribution of the previous time step after smoothing, is the original distribution of the current time step; Obtain a number of predicates, sample the predicates involved in the query in sequence according to the joint probability distribution, adjust the selection rate of the deep autoregressive model through the sampling results, and multiply the adjusted selection rate by the cardinality to obtain an estimation result; Adjusting the selection rate of the deep autoregressive model includes: S1. Set a first target threshold, input the sampling result into the trained deep autoregressive model to obtain a data range; S2. Perform Monte Carlo integration on the data range to obtain a first error, judge the first error. If the first error is less than the first target threshold, adjust the selection rate of the trained deep autoregressive model to obtain a total selection rate; S3. Set a second target threshold, judge the product of the total selection rate and the cardinality and the target threshold. If the product is greater than the second target threshold, output the adjusted selection rate. If the product is less than the second target threshold, resample the predicate and increase the sample size.

2. The adaptive model cardinality estimation method according to claim 1, wherein After obtaining the data set, it includes: Scan the attribute columns of the data set, obtain the domain values of the attribute columns, assign the domain values to an integer value sequence to complete position encoding, and perform input encoding on the data set after position encoding to obtain the encoded data set.

3. The adaptive model cardinality estimation method according to claim 1, wherein The method of obtaining a joint probability distribution is: Among them, is the joint probability distribution of the current query, is the data corresponding to the predicates involved in the query, is the autoregressive model, is the data after performing the adaptive smoothing strategy, is the original data.

4. The adaptive model cardinality estimation method according to claim 1, wherein After constructing the deep autoregressive model, it includes: Perform gradient denoising on the output result of the deep autoregressive model, and train the deep autoregressive model in combination with the maximum likelihood loss to obtain the trained deep autoregressive model.

5. The adaptive model cardinality estimation method according to claim 1, characterized in that The method of performing Monte Carlo integration on the data range is: Among them, is the selectivity of the current query, is the attribute or column of the input relationship, is the attribute column to which x belongs, is the probability distribution of the current x.

Images