Uncertainty quantification query cost estimation method under database generalization
By using the Cross-Database Representation Module (CDR) and the Uncertainty Cost Estimation Module (CEU), the performance degradation of existing models in unknown databases is addressed, achieving high-precision and robust cost estimation, supporting decision-making, and reducing training costs.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- UNIV OF ELECTRONICS SCI & TECH OF CHINA
- Filing Date
- 2024-08-22
- Publication Date
- 2026-07-07
Smart Images
Figure CN119025554B_ABST
Abstract
Description
Technical Field
[0001] This invention belongs to the field of database query optimization tasks and relates to an uncertainty quantification query cost estimation method under cross-database generalization. Background Technology
[0002] The query optimizer is a crucial component of modern database management systems (DBMS). Its primary goal is to select the optimal query plan for a given SQL query, thereby minimizing query execution time and resource utilization while maximizing overall system efficiency. However, recent research indicates that traditional query optimizers often overestimate or underestimate runtime, leading to suboptimal plan decisions and reduced query performance.
[0003] Recently, learning-based models have been introduced into the field of database query optimization due to their powerful modeling capabilities and higher estimation accuracy. Extensive research has been conducted in query optimization, cardinality estimation, and cost estimation. For example, MSCN uses a CNN model for cardinality estimation. TPool utilizes a tree-based model to simultaneously estimate data cardinality and latency. As the effectiveness of these models gains widespread recognition, researchers are increasingly focusing on enhancing their robustness in practical applications, rather than simply improving predictions. For instance, Bao utilizes modern tree convolutional neural networks and the Thompson sampling algorithm to adapt to changes in query workload, data, and patterns. Warper generates additional queries and selects useful ones to update the model, enabling rapid adaptation to drift.
[0004] However, existing learning-based models share some common limitations. While these models outperform traditional optimizers on static datasets, their resilience and reliability in real-world applications remain a concern, limiting their widespread adoption. First, these models struggle to generalize to untrained databases. They primarily focus on model adaptation within known databases, making them ill-suited for data transfer, workload shifts, and other challenges. Second, most of these models provide an exact value without quantifying any uncertainty in the prediction. Model uncertainty stems from data noise (random uncertainty) and insufficient representativeness of training data (cognitive uncertainty). Specifically, cognitive uncertainty increases significantly when encountering unseen datasets due to variations in data distribution, feature space, and other factors. Furthermore, quantifying uncertainty not only helps decision-makers choose the optimal query plan but also guides model improvement and optimization, ultimately enhancing model robustness. Third, model training is costly. Changes in workload or database data distribution necessitate collecting new datasets to update and train the model, incurring substantial costs. Summary of the Invention
[0005] To address the aforementioned problems, this invention provides a method for quantifying the uncertainty of query cost estimation across databases. It can generalize to databases it has not previously studied and quantify the uncertainty of cost estimation. It mainly consists of a cross-database representation module (CDR) and an uncertainty cost estimation module (CEU). The CDR module encodes a minimal set of features for cross-database query plans while using Tree-LSTM to ignore database-specific features. Subsequently, the CEU module estimates the cost of a given set of query plans in the database and outputs the uncertainty of the estimate. This invention not only achieves cross-database cost estimation but also quantifies the uncertainty of the estimate for decision-making.
[0006] The technical solution of this invention is as follows:
[0007] An uncertainty quantification query cost estimation method under cross-database generalization is mainly divided into two stages: the query plan feature extraction stage across databases and the uncertainty measurement stage of cost estimation.
[0008] The main task of the cross-database query plan feature extraction stage is to encode a set of physical execution plans corresponding to queries in the database and extract their features through a neural network. This mainly includes three sub-processes:
[0009] A1. Data Acquisition. The query optimizer obtains multiple candidate physical execution plans and their corresponding execution times for a given SQL query.
[0010] A2. Encode transferable features. Encode transferable features in the query that are independent of the database instance, and convert the information of each node in the physical query plan tree into a vector representation.
[0011] A3. Feature Extraction. Features are extracted using Tree-LSTM: The node encodings are input into the Tree-LSTM from the bottom up, and the output of the root node is then passed through two fully connected layers to transform it into the final query plan representation.
[0012] The uncertainty measurement stage of cost estimation primarily involves receiving dynamic query workloads from unknown databases, predicting time costs, and outputting the uncertainty. If the task is currently in offline training, the error is calculated based on the actual execution cost, and the learned model parameters are updated via backpropagation. If the task is currently in online inference, the optimal physical execution plan is selected based on the output uncertainty and a threshold. This mainly includes the following processes:
[0013] B1. Query Cost Prediction. Input the feature vector into the model to predict the execution cost corresponding to the query plan, and simultaneously output the uncertainty of this prediction.
[0014] If you are currently in an offline training task:
[0015] B21. Update model parameters. The error is calculated based on the actual execution cost, and the learned model parameters are updated via backpropagation.
[0016] If you are currently in an online inference task:
[0017] B22. Select the optimal plan. The forecast results are then filtered based on uncertainty, with those exhibiting high uncertainty being discarded.
[0018] The feature extraction step in this invention is mainly performed by the Cross-Database Representation (CDR) module. Its purpose is to encode and extract features from the physical execution plan tree, preserving its transferable features so that the cost estimator can be migrated to other unknown databases. The specific method is as follows:
[0019] First, each node in the physical execution plan tree is encoded, including the following attributes: operators (such as SORT, LOOP JOIN, AGGREGATE, etc.), cardinality estimation, cost estimation, and tuple width. The operators are encoded using one-hot encoding and are directly concatenated to the head of the vector composed of other attributes.
[0020] Subsequently, a depth-first search (DFS) traversal algorithm is used to sequentially input node features into the LSTM network, facilitating feature extraction across the entire tree. Finally, two fully connected layers are used to represent the query plan.
[0021] The optimal execution plan selection in this invention mainly utilizes the uncertainty output by the model to assist in the selection decision of the most physically feasible execution plan. The specific method is as follows:
[0022] In the online prediction phase, for any unknown database One of the query statements is Q = {q} 1 ,q 2 ,…,q m}, each query statement q i The set of physical execution plans is The model outputs a physical execution plan for each physical instance. Predicted execution time and uncertainty If the uncertainty exceeds the threshold, the current estimate will not be used; instead, the execution plan p generated by the expert cost estimator will be used. c .
[0023] The beneficial effects of this invention are as follows: It innovatively develops a cost estimation model that emphasizes accuracy and flexibility in real-world environments. This model provides uncertain cost estimation for unknown databases, with high accuracy and robustness guarantees. Unlike existing methods, this invention performs exceptionally well in handling workload variations, can effectively scale to new databases, and provides confidence levels for each prediction, thereby supporting management decisions. Extensive empirical validation across public benchmarks demonstrates the outstanding operational accuracy, efficiency, robustness, and practicality of this innovation. Attached Figure Description
[0024] Figure 1 This is the overall architecture diagram of the cost estimator.
[0025] Figure 2 This is a schematic diagram of feature extraction using Tree-LSTM.
[0026] Figure 3 This is a schematic diagram of node encoding.
[0027] Figure 4 This is a comparison of the accuracy of our method (our) with other schemes on three different datasets. Detailed Implementation
[0028] The present invention will now be described in detail with reference to the accompanying drawings.
[0029] In this invention, the overall cost estimator architecture is shown in the diagram below. Figure 1 As shown, it is mainly divided into several parts: model input, cross-database representation module, uncertainty cost estimation module, and model output.
[0030] Example:
[0031] This example specifically includes the following steps:
[0032] S1, the cross-database query plan feature extraction stage, mainly encodes and extracts features from query plans in different databases, enabling the subsequent model to adapt to workloads in other databases. This stage includes the following three sub-stages:
[0033] a1. Obtain model input data: from different databases DB1, DB2, ..., DB n Different physical execution plan trees and their corresponding actual execution times are extracted from them, and the two together form the original input data of the model.
[0034] a2. Encoding Transferable Features: To enable the model to adapt to workloads on unknown databases, some database-detailed features in the input physical execution plan tree are removed, retaining only four features: operator type, cardinality estimate, cost estimate, and tuple width. Operator type is represented using 7-bit one-hot encoding, and other features are directly appended to the end of the vector, such as... Figure 3 As shown.
[0035] a3. Feature extraction using Tree-LSTM: The node encodings are input into the Tree-LSTM from the bottom up. Finally, the output of the root node is transformed into the final query plan representation after passing through two fully connected layers, such as... Figure 2 As shown.
[0036] S2, the uncertainty measurement stage of cost estimation, mainly involves inputting the feature representations extracted in the previous step into the model to obtain the final cost estimate and the uncertainty of this estimate. If the current stage is training, the corresponding error is calculated and the model parameters are backpropagated to learn the model; if the current stage is inference, the optimal physical execution plan is selected based on the uncertainty and the threshold.
[0037] Details are as follows:
[0038] b1. Calculate the cost estimate and uncertainty. The uncertainty is calculated using the NNGP used in the cardinality estimation task.
[0039] For different query plans X = {x1, x2, ..., x} on different databases n}, corresponding to their actual execution time Y={y1,y2,…,y n If the model's parameters are w, then the posterior distribution of the model parameters can be obtained using Bayes' theorem:
[0040]
[0041] Then for a new set of feature vectors Calculate the predicted cost Distribution:
[0042] P(Y * |X * ,Y,X)=∫P(Y * |X * ,w)P(w|Y,X)dw
[0043] In NNPG, f(X) * Since the distribution is Gaussian, we have:
[0044]
[0045]
[0046] The mean μ is used as the prediction cost. The coefficient of variation, as the uncertainty U, is:
[0047]
[0048] If you are currently in an offline training task:
[0049] b2-1. Calculate the error and update the model parameters. For each execution plan P i After encoding, a vector representation x is obtained, and then feature extraction is used to obtain the query plan representation R. p Then NNGP calculated the predicted cost. and uncertainty
[0050] In this example, the mean squared error (MSE) is used as the loss function, as shown below:
[0051]
[0052] Then update the model parameters based on the error and learning rate (lr):
[0053]
[0054] θ=θ-lr*δθ
[0055] If you are currently in an online inference task:
[0056] b2-2, Optimal query plan selection.
[0057] Then, the optimal execution plan is selected based on the cost estimate output by the model and the corresponding uncertainty. In the online prediction phase, for any unknown database... One of the query statements is Q = {q} 1 ,q 2 ,…,q m}, each query statement q i The set of physical execution plans is The model outputs a physical execution plan for each physical instance. Predicted execution time and uncertainty If the uncertainty exceeds the threshold, the current estimate will not be used; instead, the execution plan p generated by the expert cost estimator will be used. c :
[0058]
Claims
1. An uncertainty quantification query cost estimation method under database generalization, characterized in that, Includes the following steps: S1. Characteristics of cross-database query plans, including: A1. Data Acquisition: Obtain multiple candidate physical execution plans and their corresponding execution times for a given SQL query through the query optimizer; A2. Encode transferable features, encode transferable features in the query that are not related to the database instance, and convert the information of each node in the physical query plan tree into a vector representation; A3. Using Tree-LSTM to extract features: Input the node encoding into Tree-LSTM from the bottom up, and finally transform the output of the root node into the final query plan representation after passing through two fully connected layers; S2, Uncertainty Quantification Query Cost Estimation, including: B1. Query Cost Prediction: Input the query plan feature vector obtained in S1 into the model to predict the execution cost corresponding to the query plan, and simultaneously output the uncertainty of this prediction; the specific method is as follows: Different query plans on different databases The corresponding actual execution time The parameters of the model are The posterior distribution of the model parameters can be obtained from Bayes' theorem: ， Then for a new set of feature vectors Calculate the predicted cost Distribution: ， In NNPG, It follows a Gaussian distribution, therefore we get: ， ， Mean As a prediction cost The coefficient of variation is used as uncertainty. ,have: ， ， This yields a cost estimate and the uncertainty of this estimate; B2. Perform different operations based on the current model state: If you are currently in an offline training task: The error is calculated based on the actual execution cost and backpropagated to update the parameters of the learning model; If you are currently performing an online prediction task: Prediction results are filtered based on uncertainty, and those with high uncertainty are discarded. The specific method is as follows: In the online prediction phase, for any unknown database One of the sets of query statements Each query statement The set of physical execution plans is The model outputs a physical execution plan for each physical instance. Predicted execution time and uncertainty If the uncertainty is higher than the threshold, the current estimate will not be used, and the execution plan generated by the expert cost estimator will be used instead. .
2. The uncertainty quantization query cost estimation method under cross-database generalization as described in claim 1, characterized in that, The specific method for step A2 is as follows: First, each node in the physical execution plan tree is encoded, including the following attributes: operator, cardinality estimate, cost estimate, and tuple width. The operator is encoded using one-hot encoding and directly concatenated to the head of the vector composed of other attributes.
3. The uncertainty quantification query cost estimation method under cross-database generalization as described in claim 1, characterized in that, The specific method for step A3 is as follows: The node features are sequentially input into the LSTM network using a depth-first search traversal algorithm to extract features from the entire tree; finally, two fully connected layers are used to represent the query plan.