Staircase query memory prediction method based on uncertainty perception

Abstract

The application discloses a ladder query memory prediction method based on uncertainty perception, which firstly acquires historical query samples and extracts plan features, then calls each prediction stage according to a preset order, calculates a prediction value and uncertainty through member integration, random forward propagation or Bayesian approximate inference and the like, and decides to accept the current prediction value or upgrade to a subsequent stage for continuous prediction according to whether the uncertainty meets an acceptance condition. When all models cannot give an acceptable prediction value, the method falls back to a database native estimator. The application can achieve a good balance among prediction accuracy, online inference overhead and safe fallback boundary, so that ordinary queries can quickly complete prediction, complex queries can enter stronger model review on demand, and extreme or unreliable samples can be safely backed up, thereby improving the overall reliability and stability of the prediction system.

Description

Technical Field

[0001] This invention belongs to the field of database resource prediction and machine learning technology, specifically, it relates to a step-by-step query memory prediction method based on uncertainty awareness. Background Technology

[0002] When processing complex analytical queries, database systems need to allocate memory for operations such as sorting, hash joins, aggregation, materialization, and window aggregation. Query-level peak memory estimation not only affects whether a single query receives an appropriate memory budget, but also impacts the admission, waiting time, and overall completion time of other queries in the resource pool. Therefore, query-level peak memory prediction is a key technology in database resource management.

[0003] Existing database systems typically rely on optimizer statistics, cost models, heuristics, or empirical formulas for memory estimation. While these methods are mature and have low overhead, they are prone to inaccurate peak memory estimations in scenarios involving complex joins, data skew, non-uniform data distribution, mixed loads, and accumulated estimation errors. Overestimating memory can lead to overly conservative resource pool scheduling, reducing concurrency; underestimating memory can result in insufficient execution memory, temporary disk writes, and increased query tail latency.

[0004] Machine learning-based query resource prediction methods can learn nonlinear mapping relationships from historical query execution plans and actual resource consumption, showing better predictive potential compared to traditional rule-based estimation. However, single learning models still face challenges in online database scenarios: lightweight models offer fast inference but are insufficient for representing complex plan structures; structured models have strong expressive power but higher inference overhead; while the most complex models may offer better offline accuracy, entrusting all queries to this model increases unnecessary online costs and may still produce unreliable predictions in cases of anomalous samples or distribution drift.

[0005] Therefore, a query-in-memory prediction method is needed that can strike a balance between prediction accuracy, online inference overhead, and safe fallback boundaries, enabling ordinary queries to complete predictions quickly, complex queries to enter stronger model verification as needed, and extreme or unreliable samples to fall back to the database's native estimation path. Summary of the Invention

[0006] The purpose of this invention is to provide a step-by-step query memory prediction method based on uncertainty awareness, which solves the problems of insufficient accuracy of traditional rule-based query memory estimation, difficulty of a single learning model in simultaneously balancing accuracy and online overhead, and lack of clear rejection and backoff boundaries for unreliable predictions.

[0007] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0008] A step-by-step query memory prediction method based on uncertainty awareness includes the following steps:

[0009] S1, Obtain historical query samples for training or calibration, the historical query samples including query execution plans and corresponding query-level peak memory;

[0010] S2, based on the query execution plan to be predicted, extract plan features related to query-level peak memory;

[0011] S3, construct or invoke a ladder-like prediction model chain including multiple prediction stages, wherein the multiple prediction stages have different input representations, model complexities or inference overheads; wherein the multiple prediction stages are invoked in a condition-triggered manner, and subsequent prediction stages are invoked only if the previous prediction stage is not accepted.

[0012] S4, call the current prediction stage in the ladder prediction model chain in a preset order to obtain the query-level peak memory prediction value of the current stage and the uncertainty corresponding to the prediction value;

[0013] S5. Based on the legality of the current stage prediction value and whether the uncertainty meets the acceptance conditions, decide whether to accept the current stage prediction value or upgrade to a subsequent prediction stage to continue prediction.

[0014] S6, when the predicted value of any prediction stage is accepted, the predicted value is used as a candidate value for the peak memory at the query level; when the ladder prediction model chain cannot give an acceptable predicted value or the prediction process is abnormal, it falls back to the database native query memory estimator.

[0015] S7. Based on the candidate values ​​of query-level peak memory or the output of the database native query memory estimator, generate the query-level peak memory estimation result of the query to be predicted.

[0016] Furthermore, in this invention, the plan features are one or more of the following: plan size features, cost features, operator semantic features, operator resource sensitivity features, and plan structure features; the plan features include one or more of the following: estimated number of rows, estimated width, startup cost, total cost, operator type, node depth, plan tree topology, plan graph topology, and memory-sensitive operator aggregation amount.

[0017] Furthermore, in this invention, the ladder-like prediction model chain includes at least a low-overhead prediction stage and a structured verification prediction stage. The ladder-like prediction model chain includes a first-level fast prediction model, a second-level tree-structured verification model, and a third-level graph-structured deep verification model. The first-level fast prediction model makes predictions based on fixed-length statistical features, the second-level tree-structured verification model makes predictions based on a query plan tree, and the third-level graph-structured deep verification model makes predictions based on a query plan graph.

[0018] Furthermore, in this invention, the uncertainty is calculated using multiple candidate outputs within the same prediction stage. These multiple candidate outputs are generated by one or more of the following methods: member integration, random forward propagation, Bayesian approximate inference, or repeated sampling. After transforming the multiple candidate outputs to the actual memory space, the mean of the multiple candidate outputs is used as the prediction value for the current stage, and the dispersion of the multiple candidate outputs is used as the uncertainty for the current stage.

[0019] Furthermore, in this invention, the acceptance conditions include that the current stage prediction value is a valid value and the current stage uncertainty does not exceed the corresponding uncertainty acceptance threshold; the uncertainty acceptance threshold is determined based on the uncertainty distribution on the calibration sample and is dynamically adjusted based on the prediction error, tail error, underestimation rate, or prediction interval coverage in the online historical feedback.

[0020] Furthermore, in this invention, the specific process of falling back to the database native query memory estimator is as follows: when any of the following occurs: model not enabled, model unavailable, input construction failure, inference failure, output invalid, or all prediction stages failing to meet the acceptance conditions, the output of the database native query memory estimator is used as the query-level peak memory estimation result; wherein, before generating the query-level peak memory estimation result, the accepted query-level peak memory candidate value is subject to boundary constraints, ensuring that it is not lower than a preset lower limit and not higher than the memory upper limit allowed by the current query context.

[0021] Furthermore, in this invention, the historical query samples are derived from one or more of the following: historical query information from the database, execution logs, or resource monitoring records, and the query-level peak memory serves as the basis for supervised training, threshold calibration, or online feedback adjustment.

[0022] Furthermore, in this invention, the method accesses the database query memory estimation path through any of the following forms: database plugin, database built-in module, independent prediction service, or computer program.

[0023] Compared with the prior art, the present invention has the following beneficial effects:

[0024] (1) This invention does not entrust all queries to a single model for processing, but organizes the prediction chain in a way that prioritizes low cost and places complex models at the end, so that most common queries can be predicted quickly at the first layer.

[0025] (2) By unifying uncertainty measurement and accept threshold management of prediction results, this invention enables the model output to have acceptable, rejectable and rollback boundaries, thus avoiding unreliable predictions from directly entering database resource decisions.

[0026] (3) The present invention establishes a hierarchical usage relationship between lightweight statistical features and structured planning features, and can allocate model inference budgets as needed between ordinary samples and complex samples.

[0027] (4) The present invention retains the database native query memory estimator as the final fallback path and limits the accepted prediction value, thus maintaining the system decision safety boundary while introducing learning prediction capabilities. Attached Figure Description

[0028] Figure 1 This is a schematic diagram of the overall process of the step-by-step query memory prediction method in an embodiment of the present invention.

[0029] Figure 2 This is a schematic diagram illustrating the construction of execution plan features in an embodiment of the present invention.

[0030] Figure 3 This is a schematic diagram of the ladder-like model chain and uncertainty-aware gating in an embodiment of the present invention.

[0031] Figure 4 This is an engineering example diagram illustrating the algorithm's access to the database for memory estimation path estimation in an embodiment of the present invention. Detailed Implementation

[0032] The present invention will be further described below with reference to the accompanying drawings and embodiments. The embodiments of the present invention include, but are not limited to, the following embodiments.

[0033] like Figure 1 As shown, this invention discloses a ladder-style query memory prediction method based on uncertainty awareness. This method takes the query execution plan as input and the actual peak memory after query execution as the supervision label, and trains and uses a ladder-style prediction model chain composed of three levels of models.

[0034] In its implementation, the system first retrieves historical query sample data from the database system's query history. These historical query samples originate from one or more of the following: database historical query information, execution logs, or resource monitoring records. The peak memory usage at the query level serves as the basis for supervised training, threshold calibration, or online feedback adjustments. Each historical query sample contains the corresponding query statement text, query execution plan information, database optimizer estimation information, and the actual peak memory usage observed after execution. To reduce the impact of extreme values ​​on training, continuous features and labels can be modeled using a log(x+1) transformation. After the model outputs, the prediction results are then inversely transformed to the actual memory space to participate in decision-making.

[0035] Secondly, the system extracts plan features related to query-level peak memory usage based on the query execution plan to be predicted. For example... Figure 2As shown, in this embodiment, the plan features are constructed into two types of input features. The first type is low-dimensional statistical features for the first-level fast prediction model, including one or more of the following: plan size features, cost features, operator semantic features, operator resource sensitivity features, and plan structure features. This type of feature has fixed dimensions and low extraction cost, making it suitable for online fast paths. The second type is node features for the second- and third-level structured models, including one or more of the following: estimated number of rows, estimated width, startup cost, total cost, operator type, node depth, plan tree topology, plan graph topology, and memory-sensitive operator aggregation amount.

[0036] Then, the system trains a laddered prediction model chain, which includes at least a low-overhead prediction stage and a structured review prediction stage. For example... Figure 3 As shown, the hierarchical prediction model chain comprises three hierarchical prediction models: a first-level fast prediction model, a second-level tree-structured verification model, and a third-level graph-structured deep verification model. These three prediction models are connected in series; the output of the first-level fast prediction model is connected to the input of the second-level tree-structured verification model, and the output of the second-level tree-structured verification model is connected to the input of the third-level graph-structured deep verification model.

[0037] The first-level fast prediction model can employ a gradient boosting tree member ensemble model, i.e., XGBoost ensemble, where each member model is independently trained based on the same low-dimensional statistical feature; the first-level output is the mean of the member predictions, and the uncertainty is the standard deviation of the member predictions. The second-level tree-structured deep kernel model uses a Tree CNN structure, which takes the plan tree and node features as input, obtains a query-level representation through tree-structure aggregation, and uses multiple random forward propagations to obtain the second prediction value and the second uncertainty. The third-level graph-structured deep kernel model uses a Bayesian GNN structure, which takes the plan graph and node features as input, obtains a query-level representation through graph-structured message passing, and approximates the prediction distribution through multiple random forward propagations to obtain the third prediction value and the third uncertainty.

[0038] For any level model, if it yields multiple outputs in logarithmic space, each output is first inversely transformed to the actual memory space, and then the mean and standard deviation are calculated. The mean is used as the current level prediction value, and the standard deviation is used as the current level uncertainty. This unified definition allows the outputs of first-level member ensemble, second-level stochastic forward propagation, and third-level stochastic forward propagation to be compared under the same gating rules.

[0039] The three prediction models are not simply chained together, but are uniformly controlled by an uncertainty-aware gating module: when the prediction result of the current stage meets the validity check and the uncertainty is below the threshold, the system accepts the output of that stage and enters result limiting; when the prediction is unstable, the gating module triggers the next level model; if all learned models cannot provide reliable results, it falls back to the database's native estimator. Finally, the model prediction values ​​or native fallback values ​​are processed by the safe output module to form the query-level peak memory prediction results, which are used for subsequent query memory allocation and admission decisions.

[0040] Subsequently, the current prediction stage in the ladder-like prediction model chain is called in a preset order to obtain the query-level peak memory prediction value of the current stage and the uncertainty corresponding to the prediction value. The uncertainty is calculated through multiple candidate outputs within the same prediction stage. The multiple candidate outputs are generated by one or more of the following methods: member integration, random forward propagation, Bayesian approximate inference, or repeated sampling. After transforming the multiple candidate outputs to the actual memory space, the mean of the multiple candidate outputs is used as the prediction value of the current stage, and the dispersion of the multiple candidate outputs is used as the uncertainty of the current stage.

[0041] Among these methods, the member ensemble approach maintains multiple model copies with different initial weights, performs inference on the same input separately, and produces multiple predictions. The stochastic forward propagation approach introduces a random dropout mechanism during the model's inference process, randomly discarding some neurons during each inference iteration, resulting in different predictions for the same input in different inference rounds. The Bayesian approximation inference method uses Monte Carlo Dropout, approximating the Bayesian posterior distribution by enabling Dropout multiple times during inference. The resampling method adds slight perturbations to the input features and performs multiple inference iterations to observe the model's sensitivity to these perturbations.

[0042] The system calculates the mean for each of the three prediction models: the mean of the candidate outputs corresponding to the first-level fast prediction model is calculated as the first-level prediction mean; the mean of the candidate outputs corresponding to the second-level tree-structured kernel model is calculated as the second-level prediction mean; and the mean of the candidate outputs corresponding to the third-level graph-structured deep kernel model is calculated as the third-level prediction mean. Simultaneously, the overall mean of all candidate outputs is calculated as the final fused prediction value.

[0043] In the specific implementation, standard deviation is used as a quantitative indicator of dispersion. The standard deviations of the candidate outputs for the three prediction models are calculated as uncertainties for each model, and the total standard deviation of all candidate outputs is calculated as a fusion uncertainty measure. The standard deviation of dispersion is an important input parameter for the subsequent result determination unit.

[0044] Furthermore, the system decides whether to accept the current stage's predicted value or upgrade to a subsequent prediction stage based on the legality of the current stage's predicted value and whether the uncertainty meets the acceptance criteria. The acceptance criteria include that the current stage's predicted value is a legal value and that the current stage's uncertainty does not exceed the corresponding uncertainty acceptance threshold. This uncertainty acceptance threshold is determined based on the uncertainty distribution on the calibration samples and is dynamically adjusted based on prediction errors, tail errors, underestimation rates, or prediction interval coverage from online historical feedback.

[0045] When a predicted value at any prediction stage is accepted, that predicted value is used as a candidate peak memory value at the query level. When the laddered prediction model chain cannot provide an acceptable predicted value or the prediction process is abnormal, it falls back to the database's native query memory estimator. That is, before the model goes live, the system calculates the uncertainty distribution of each level of the model based on independent calibration samples and selects a preset quantile as the initial acceptance threshold. For example, the 90th percentile of the uncertainty of the calibration samples can be selected as the initial threshold. After online operation, the system can smoothly correct the threshold based on the prediction error, high quantile error, severe underestimation rate, and prediction interval coverage in the recent historical feedback window. When the severe underestimation rate increases or the tail error becomes larger, the threshold is tightened, allowing more samples to be upgraded or rolled back. When the prediction quality is stable and the upgrade ratio is too high, the threshold can be appropriately relaxed within the safety boundary.

[0046] During online inference, the query to be predicted first undergoes a runtime condition check. If any of the following occurs: learned prediction is not enabled, the model is unavailable, input construction fails, inference fails, output is invalid, or all prediction stages fail to meet the acceptance criteria, the system directly uses the database's native query memory estimator. Specifically, before generating the query-level peak memory estimation result, boundary constraints are applied to the accepted query-level peak memory candidate values ​​to ensure they are not lower than a preset lower limit and not higher than the memory limit allowed by the current query context.

[0047] If the operating conditions are met, the first-level fast prediction model is invoked to obtain the first predicted value and the first uncertainty. If the first predicted value is a finite positive number and the first uncertainty does not exceed the first-level threshold, the prediction is accepted; otherwise, if the first-level output is valid but not sufficiently certain, the model is upgraded to the second-level tree structure verification model.

[0048] The two-level tree-structured verification model infers based on the execution plan tree and node features, outputting a second predicted value and a second uncertainty. If the second prediction meets the validity and uncertainty threshold requirements, it is accepted; otherwise, it is upgraded to the three-level graph-structured deep verification model. The three-level graph-structured deep verification model infers based on the execution plan graph and node features, outputting a third predicted value and a third uncertainty. If the third prediction meets the requirements, it is accepted; otherwise, it falls back to the database native query memory estimator.

[0049] In the above process, upgrading and rolling back have different meanings. Upgrading means that the current model is usable but not certain enough, so the inference cost of a higher-level model is paid for the query; rolling back means that the current learning path cannot provide an acceptable prediction for the query, and the system should use the native estimation path as a safety net.

[0050] For accepted model predictions, the system performs a throttling process. For example, the predictions are limited to between a preset lower memory limit and the upper memory limit allowed by the current query context. Only the throttled predictions are used as the final query-level peak memory prediction. If no model predictions are accepted, the final result remains the output of the database's native query memory estimator.

[0051] like Figure 4 As shown, in this embodiment, the method can be integrated into the database query memory estimation path, or it can serve as an independent prediction service to provide candidate values ​​for peak query memory to the database resource scheduling module. The engineering implementation does not constitute a limitation on the scope of protection of this invention; the core of this invention lies in the combined algorithm of execution plan feature construction, a ladder-like model chain, uncertainty-aware gating, acceptance of upgrade / rollback decisions, and amplitude-limited output.

[0052] Practice has shown that this method enables most queries to be predicted quickly using a low-cost model, a small number of complex queries to be verified using a structured model, and extreme outliers or unreliable samples to be reverted to the native estimator. This approach balances prediction accuracy, inference overhead, and decision stability in online database query memory prediction scenarios.

[0053] The above embodiments are merely one of the preferred embodiments of the present invention and should not be used to limit the scope of protection of the present invention. Any modifications or refinements made to the main design concept and spirit of the present invention that are not of substantial significance, but solve the same technical problem as the present invention, should be included within the scope of protection of the present invention.

Claims

1. A method for uncertainty-aware stepwise query memory prediction, the method comprising: Includes the following steps: S1, Obtain historical query samples for training or calibration, the historical query samples including query execution plans and corresponding query-level peak memory; S2, based on the query execution plan to be predicted, extract plan features related to query-level peak memory; S3, construct or invoke a ladder-like prediction model chain including multiple prediction stages, wherein the multiple prediction stages have different input representations, model complexities or inference overheads; wherein the multiple prediction stages are invoked in a condition-triggered manner, and subsequent prediction stages are invoked only if the previous prediction stage is not accepted. S4, call the current prediction stage in the ladder prediction model chain in a preset order to obtain the query-level peak memory prediction value of the current stage and the uncertainty corresponding to the prediction value; S5. Based on the legality of the current stage prediction value and whether the uncertainty meets the acceptance conditions, decide whether to accept the current stage prediction value or upgrade to a subsequent prediction stage to continue prediction. S6, when the predicted value of any prediction stage is accepted, the predicted value is used as a candidate value for the peak memory at the query level; when the ladder prediction model chain cannot give an acceptable predicted value or the prediction process is abnormal, it falls back to the database native query memory estimator. S7. Based on the candidate values ​​of query-level peak memory or the output of the database native query memory estimator, generate the query-level peak memory estimation result of the query to be predicted.

2. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The plan features are one or more of the following: plan size features, cost features, operator semantic features, operator resource sensitivity features, and plan structure features; the plan features include one or more of the following: estimated number of rows, estimated width, startup cost, total cost, operator type, node depth, plan tree topology, plan graph topology, and memory-sensitive operator aggregation amount.

3. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The ladder-like prediction model chain includes at least a low-overhead prediction stage and a structured verification prediction stage. The ladder-like prediction model chain includes a first-level fast prediction model, a second-level tree structure verification model, and a third-level graph structure deep verification model. The first-level fast prediction model makes predictions based on fixed-length statistical features, the second-level tree structure verification model makes predictions based on a query plan tree, and the third-level graph structure deep verification model makes predictions based on a query plan graph.

4. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The uncertainty is calculated through multiple candidate outputs within the same prediction phase, and the multiple candidate outputs are generated by one or more of the following methods: member integration, random forward propagation, Bayesian approximate inference, or repeated sampling. After transforming the multiple candidate outputs to the actual memory space, the mean of the multiple candidate outputs is used as the predicted value for the current stage, and the dispersion of the multiple candidate outputs is used as the uncertainty for the current stage.

5. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The acceptance criteria include that the current stage prediction value is a valid value and the current stage uncertainty does not exceed the corresponding uncertainty acceptance threshold; the uncertainty acceptance threshold is determined based on the uncertainty distribution on the calibration sample and is dynamically adjusted based on the prediction error, tail error, underestimation rate or prediction interval coverage in the online historical feedback.

6. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The specific process of falling back to the database native query memory estimator is as follows: when any of the following situations occur: model not enabled, model unavailable, input construction failure, inference failure, output invalid, or all prediction stages failing to meet the acceptance conditions, the output of the database native query memory estimator is used as the query-level peak memory estimation result; wherein, before generating the query-level peak memory estimation result, the accepted query-level peak memory candidate value is subject to boundary constraints, so that it is not lower than the preset lower limit and not higher than the memory upper limit allowed by the current query context.

7. The step-by-step query memory prediction method based on uncertainty awareness according to claim 1, characterized in that, The historical query samples are derived from one or more of the following: historical query information from the database, execution logs, or resource monitoring records. The peak memory at the query level serves as the basis for supervised training, threshold calibration, or online feedback adjustment.

8. The step-by-step query memory prediction method based on uncertainty awareness according to any one of claims 1 to 7, characterized in that, The method accesses the database query memory estimation path through any of the following forms: database plugin, database built-in module, independent prediction service, or computer program.

Images