Method for estimating uncertainty quantitative query cost under cross-database generalization
Patent Information
- Application Number
- CN202411154225.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-22
- Publication Date
- 2024-11-26
- Estimated Expiration
- 2044-08-22
AI Technical Summary
The existing learning-based query optimization model is difficult to maintain robustness when processing unseen databases and facing data transfer and workload drift, and lacks quantification of prediction uncertainty, resulting in performance degradation in practical applications. And the training cost is high.
Using a cross-database generalized uncertainty quantitative query cost estimation method, the query plan features are encoded through the cross-database representation module (CDR), and the features are extracted using Tree-LSTM, and the cost is combined with the uncertainty cost estimation module (CEU) Estimation and uncertainty measures, providing quantification of uncertainty for cost estimates.
Achieve high-precision and robust cost estimation in unknown databases, able to effectively respond to workload changes, provide confidence to support management decisions, and demonstrate excellent operational accuracy, efficiency, and practicality through extensive cross-public benchmark empirical verification.
Smart Images

Figure CN119025554A_ABST
Abstract
Description
Technical Field
[0001] The present invention belongs to the field of database query optimization tasks and relates to an uncertainty quantization query cost estimation method under cross-database generalization. Background Art
[0002] The query optimizer is an important component of modern database management systems (DBMS). Its main goal is to select the best query plan for a specific SQL query, thereby minimizing the query's execution time and resource utilization while maximizing the overall efficiency of the system. However, recent studies have shown that traditional query optimizers often overestimate or underestimate the runtime, resulting in suboptimal plan decisions and degraded 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 work has been conducted in query optimization, cardinality estimation, and cost estimation. For example, MSCN adopts 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 has been widely recognized, researchers have increasingly focused on enhancing their robustness in practical applications rather than just improving predictions. For example, Bao utilized modern tree convolutional neural networks and the Thompson sampling algorithm to adapt to changes in query workloads, data, and patterns. Warper generates additional queries and selects useful queries to update the model, achieving rapid adaptation to drift.
[0004] However, existing learning-based models have some common limitations. Although these models outperform traditional optimizers on static datasets, their resilience and reliability in practical applications are still worrying, limiting their widespread adoption. First, these models are difficult to generalize to databases that have not been learned. They mainly focus on model adaptation within known databases and have difficulty dealing with issues such as data shift and workload drift. Second, most of these models provide an exact value without any uncertainty quantification of the prediction. The uncertainty of the model stems from data noise (accidental uncertainty) and insufficient representation of training data (epistemic uncertainty). Specifically, when encountering unseen datasets, epistemic uncertainty rises significantly due to changes in data distribution, feature space, and other factors. In addition, the quantification of uncertainty can not only help decision makers choose the optimal query plan, but also guide the improvement and optimization of the model, ultimately enhancing the robustness of the model. Third, the model training cost is high. When the workload or database data distribution changes, new datasets need to be collected to update and train the model, which incurs a lot of costs. Summary of the invention
[0005] In view of the above problems, the present invention provides an uncertainty quantification query cost estimation method under cross-database generalization. It can be generalized to databases that have not been learned and quantify the uncertainty of cost estimation. It is mainly divided into a cross-database representation module CDR and an uncertainty cost estimation module CEU. The CDR module encodes the minimum set of cross-database query plan features and uses Tree-LSTM to ignore database-specific features. The CEU module then estimates the cost of a given set of query plans in the database and outputs the estimated uncertainty. The present invention not only realizes cross-database cost estimation, but also quantifies the uncertainty of the estimate for decision-making.
[0006] The technical solution of the present invention is:
[0007] A cross-database generalized uncertainty quantification query cost estimation method is mainly divided into two stages: cross-database query plan feature extraction stage and cost estimation uncertainty measurement stage;
[0008] The main task of the cross-database query plan feature extraction phase is to encode a set of physical execution plans corresponding to the queries in the database and extract their features through neural networks. It mainly includes three sub-processes:
[0009] A1. Data acquisition: The query optimizer obtains multiple candidate physical execution plans for a given SQL query and their corresponding execution times.
[0010] A2. Encode transferable features. Encode transferable features in the query that are not related to the database instance, and convert each node information in the physical query plan tree into a vector representation.
[0011] A3. Extract features. Use Tree-LSTM to extract features: Input the node encoding into Tree-LSTM from bottom to top, and finally convert the output of the root node into the final query plan representation after passing through two fully connected layers.
[0012] The main task of the uncertainty measurement phase of cost estimation is to receive dynamic query workloads on unknown databases, make time cost predictions, and output uncertainty. If the task is currently in an offline training task, the error is calculated based on the actual execution cost and the learning model parameters are updated by backpropagation; if the task is currently in an online reasoning task, the optimal physical execution plan is selected based on the output uncertainty and combined with the threshold judgment. It mainly includes the following processes:
[0013] B1. Query cost prediction: Input the feature vector into the model, 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 back-propagated to update the learning model parameters.
[0016] If you are currently in an online reasoning task:
[0017] B22. Select the optimal plan. Then filter the forecast results according to the uncertainty, and discard those with too high uncertainty.
[0018] The feature extraction step in the present invention is mainly performed by the cross-database representation module CDR, the purpose of which is to encode and extract features from the physical execution plan tree, retain its transferable features so that the cost estimator can be migrated to other unknown databases. The specific method is:
[0019] First, encode each node in the physical execution plan tree, including the following attributes: operator (such as SORT, LOOP JOIN, AGGREGATE, etc.), cardinality estimation, cost estimation, and tuple width. The operator is one-hot encoded and directly spliced to the vector header composed of other attributes.
[0020] Subsequently, the node features are sequentially input into the LSTM network using the depth-first search (DFS) traversal algorithm to facilitate feature extraction of the entire tree. Finally, two fully connected layers are used to complete the representation of the query plan.
[0021] The optimal execution plan selection in the present invention mainly uses the uncertainty of the model output to assist the selection decision of the most physical execution plan. The specific method is:
[0022] In the online prediction stage, for any unknown database A set of query statements Q = {q 1 ,q 2 ,…,q m}, each query statement q i The set of physical execution plans is The model outputs each physical execution plan Predicted execution time and uncertainty When the uncertainty is higher than the threshold, the estimated value is not used and the execution plan p generated by the expert cost estimator is used instead. c .
[0023] The beneficial effects of the present invention are as follows: an innovative cost estimation model that focuses on accuracy and flexibility in practical environments is developed. The model provides uncertainty cost estimation for unknown databases with high accuracy and robustness. Unlike existing methods, this invention performs well in dealing with workload changes, can be effectively expanded to new databases, and provides confidence for each prediction to support management decisions. Extensive empirical verification across public benchmarks demonstrates the excellent operational accuracy, efficiency, robustness and practicality of this innovation. BRIEF DESCRIPTION OF THE DRAWINGS
[0024] Figure 1 This is the overall architecture diagram of the cost estimator.
[0025] Figure 2 This is a schematic diagram of using Tree-LSTM to extract features.
[0026] Figure 3 It is a schematic diagram of node encoding.
[0027] Figure 4 This is the accuracy comparison between our method and other schemes on three different datasets. DETAILED DESCRIPTION
[0028] The present invention will be described in detail below with reference to the accompanying drawings.
[0029] In the present invention, the overall cost estimator architecture is shown in FIG. Figure 1 It is mainly divided into 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 phase, mainly encodes and extracts features from query plans in different databases so that subsequent models can adapt to workloads in other location databases. It includes the following three sub-phases:
[0033] a1. Obtain model input data: from different databases DB 1 ,DB 2 ,...,DB n Different physical execution plan trees and their corresponding actual execution times are extracted, and the two together constitute the original input data of the model.
[0034] a2. Encode transferable features: In order to make the model adaptable to workloads on unknown databases, some database detail-related features in the input physical execution plan tree are removed, leaving only four features: operator type, cardinality estimate, cost estimate, and tuple width. Use 7-bit one-hot encoding to represent the operator type, and directly concatenate other features to the end of the vector, such as Figure 3 shown.
[0035] a3. Use Tree-LSTM to extract features: Input the node encoding into Tree-LSTM from bottom to top, and finally convert the output of the root node into the final query plan representation after passing through two fully connected layers, such as Figure 2 shown.
[0036] S2, the uncertainty measurement stage of cost estimation, mainly inputs the feature representation 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 back-propagated; if the current stage is inference, the optimal physical execution plan is selected based on the uncertainty and threshold.
[0037] The details are as follows:
[0038] b1. Calculate cost estimate and uncertainty. Use the NNGP used in the cardinality estimation task to calculate uncertainty.
[0039] For different query plans X on different databases, 1 ,x 2 ,…,x n}, corresponding to their actual execution time Y = {y 1 ,y 2 ,…,y n}, the parameter of the model is w, then the posterior distribution of the model parameter can be obtained by Bayes' theorem:
[0040]
[0041] Then for a new set of eigenvectors Calculate the prediction cost Distribution:
[0042] P(Y * |X * ,Y,X)=∫P(Y * |X * ,w)P(w|Y,X)dw
[0043] In NNPG, f(X * ) is a Gaussian distribution, so we get:
[0044]
[0045]
[0046] Take the mean μ as the prediction cost The coefficient of variation is taken as the uncertainty U, which is:
[0047]
[0048] If you are currently in an offline training task:
[0049] b2-1. Calculate the error and update the model parameters. i , after encoding, we get the vector representation x, and then use feature extraction to get the query plan representation R p Then NNGP calculated the prediction cost and uncertainty
[0050] In this example, mean square error (MSE) is used as the loss function, as shown below:
[0051]
[0052] Then update the model parameters according to the error and learning rate lr:
[0053]
[0054] θ=θ-lr*δθ
[0055] If you are currently in an online reasoning task:
[0056] b2-2. Select the optimal query plan.
[0057] Then, the optimal execution plan is selected based on the cost estimate output by the model and the corresponding uncertainty. A set of query statements Q = {q 1 ,q 2 ,…,q m}, each query statement q i The set of physical execution plans is The model outputs each physical execution plan Predicted execution time and uncertainty When the uncertainty is higher than the threshold, the estimated value is not used and the execution plan p generated by the expert cost estimator is used instead. c :
[0058]
Claims
1. The uncertainty quantization query cost estimation method under cross-database generalization is characterized by: The following steps are involved: S1. Cross-database query plan characteristics, including: A1. Data acquisition: obtaining multiple candidate physical execution plans and their corresponding execution times for a given SQL query through the query optimizer; A2. Encode the transferable features. Encode the transferable features in the query that are not related to the database instance, and convert each node information in the physical query plan tree into a vector representation; A3. Use Tree-LSTM to extract features: Input the node encoding into Tree-LSTM from bottom to top, and finally convert 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, predict the execution cost corresponding to the query plan, and simultaneously output the uncertainty of this prediction; B2. Perform different operations according to the current model status: If you are currently in an offline training task: Calculate the error based on the actual execution cost and back-propagate to update the learning model parameters; If you are currently in an online prediction task: Prediction results are screened based on uncertainty, and those with too high uncertainty are discarded.
2. The uncertainty quantization query cost estimation method under cross-database generalization according to claim 1 is characterized in that: The specific method of step A2 is: First, encode each node in the physical execution plan tree, including the following attributes: operator, cardinality estimate, cost estimate, and tuple width. The operator is one-hot encoded and directly concatenated to the vector header composed of other attributes.
3. The uncertainty quantization query cost estimation method under cross-database generalization according to claim 1 is characterized in that: The specific method of step A3 is: The depth-first search traversal algorithm is used to input node features into the LSTM network in sequence to extract features from the entire tree; finally, two fully connected layers are used to complete the representation of the query plan.
4. The uncertainty quantization query cost estimation method under cross-database generalization according to claim 1 is characterized in that: The specific method of step B1 is: For different query plans X={x1,x2,…,x n }, corresponding to their actual execution time Y = {y1,y2,…,y n }, the parameter of the model is w, and the posterior distribution of the model parameter can be obtained by Bayes' theorem: Then for a new set of eigenvectors Calculate the prediction cost Distribution: P(Y * |X * ,Y,X)=∫P(Y * |X * ,w)P(w|Y,X)dw In NNPG, f(X * ) is a Gaussian distribution, so we get: Take the mean μ as the prediction cost The coefficient of variation is taken as the uncertainty U, which is: This results in a cost estimate and the uncertainty of this estimate.
5. The uncertainty quantization query cost estimation method under cross-database generalization according to claim 1 is characterized in that: The specific method of step B2 is: In the online prediction stage, for any unknown database A set of query statements Q = {q 1 ,q 2 ,…,q m }, each query statement q i The set of physical execution plans is The model outputs each physical execution plan Predicted execution time and uncertainty When the uncertainty is higher than the threshold, the estimated value is not used and the execution plan p generated by the expert cost estimator is used instead. c .
Citation Information
Patent Citations
Deep learning cost estimation system, method and equipment for cloud side-end collaborative query
CN114911823A
Query optimization domain cost estimation method based on graph attention network
CN117113075A
Database query optimization method based on exploration value and query cost
CN117520385A
Database query optimizer cost model based on machine learning
CN117874071A
Automatically and adaptively determining execution plans for queries with parameter markers
US20080195577A1
Cited By
Cross-database and concept drift-oriented cardinal number estimation method and system
CN122045172A
A cardinality estimation method and system for cross-database and concept drift
CN122045172B
