Query cost estimation method and system based on structure perception and context modeling

Abstract

The present application belongs to the technical field of query optimizer, and proposes a query cost estimation method and system based on structure perception and context modeling. The cost estimation model is trained by using a two-stage progressive learning method. In the first stage, the query execution plans collected across databases are used as training samples, and the real costs of all sub-plans are used for joint supervision training to realize the key learning of the structure representation and cost modeling capability of the query plan. In the second stage, on the basis of the first stage training, a plurality of candidate execution plan sets for the same query are constructed, the candidate plan sets are modeled, the cost differences between different execution strategies are explicitly learned, and the model parameters are updated. The second stage strengthens the model's perception of the differences between the plans, thereby improving the cost estimation effectiveness of the model in the candidate execution plan space and alleviating the inconsistency between the training and reasoning stages.

Description

Technical Field

[0001] This invention belongs to the technical field of query optimizers, and in particular relates to a query cost estimation method and system based on structure awareness and context modeling. Background Technology

[0002] The statements in this section are merely background information related to the present invention and do not necessarily constitute prior art.

[0003] The query optimizer is a key component of a database management system. Its core task is to transform user-submitted SQL queries into efficient execution plans to reduce query response time and system resource overhead.

[0004] With the rapid development of artificial intelligence technology, deep learning-based learning-based cost estimation methods have been introduced. These methods typically model cost estimation as a supervised learning problem, learning the mapping relationship between query execution plans and actual execution costs. Compared to traditional methods, they can better capture complex dependencies in the execution plan and nonlinear relationships between multidimensional features, thus achieving higher estimation accuracy. To further enhance versatility, cross-database cost estimation methods have been proposed. These methods construct query execution plan representations and learning mechanisms that are independent of specific database instances, enabling the model to make effective predictions on unseen databases.

[0005] However, for real-world database query optimization scenarios, existing learning-based cost estimation methods still lack sufficient effectiveness in cost estimation within the candidate execution plan space. Current methods typically use the actual query plan and its true cost after execution as training samples, thus learning the near-optimal execution plan ultimately selected by the optimizer during the training phase. However, in real-world query optimization, the model needs to estimate costs and compare the merits of numerous candidate execution plans generated by different join orders, physical operators, and execution strategies. This means that the sample distribution during training and actual inference phases is inconsistent, leading to insufficient effectiveness in comparing and selecting candidate execution plans. Summary of the Invention

[0006] To overcome the shortcomings of the prior art, this invention provides a query cost estimation method and system based on structure awareness and context modeling. Through a two-stage progressive learning framework design, it enhances the modeling ability for cost mapping of complex query plans and improves the effectiveness of the model in cost estimation in the candidate execution plan space.

[0007] To achieve the above objectives, the present invention adopts the following technical solution: In a first aspect, the present invention provides a query cost estimation method based on structure awareness and context modeling, including: The training set construction module is configured to: use the query plan tree as the basic modeling object, traverse and encode the query plan, extract the structural features and node semantic features of the query plan, and use the actual execution time as the supervision signal to construct the training set. The training module is configured to train the cost estimation model using a two-stage progressive learning approach based on the training set. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The cost estimation module is configured to use the trained cost estimation model to estimate the cost of all sub-plans.

[0008] Secondly, the present invention provides a query cost estimation system based on structure awareness and context modeling, comprising: The training set construction module is configured to: use the query plan tree as the basic modeling object, traverse and encode the query plan, extract the structural features and node semantic features of the query plan, and use the actual execution time as the supervision signal to construct the training set. The training module is configured to train the cost estimation model using a two-stage progressive learning approach based on the training set. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The cost estimation module is configured to use the trained cost estimation model to estimate the cost of all sub-plans.

[0009] Thirdly, the present invention provides an electronic device including a memory and a processor, and computer instructions stored in the memory and running on the processor, wherein the computer instructions, when executed by the processor, perform the method described in the first aspect.

[0010] Fourthly, the present invention provides a computer-readable storage medium for storing computer instructions, which, when executed by a processor, perform the method described in the first aspect.

[0011] The above one or more technical solutions have the following beneficial effects: In this invention, a two-stage progressive learning approach is used to train the cost estimation model. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans to achieve focused learning of the structural representation and cost modeling capabilities of the query plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and context modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The second stage strengthens the model's perception of the differences in the quality of the plans, thereby improving its cost estimation effectiveness in the candidate execution plan space and alleviating the problem of inconsistent context sources between the training and inference stages.

[0012] Advantages of additional aspects of the invention will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the invention. Attached Figure Description

[0013] The accompanying drawings, which form part of this invention, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an improper limitation of the invention.

[0014] Figure 1 This is a diagram illustrating the overall framework of the cost estimation model in this embodiment of the invention. Figure 2 This is the overall framework for the structure-aware cost modeling stage in this embodiment of the invention; Figure 3 This is a structural diagram of the cost-aware context aggregation module in an embodiment of the present invention; Figure 4 This is the overall framework for the planned quality perception enhancement phase in this embodiment of the invention; Figure 5 This is a comparison chart of planned selection rates in an embodiment of the present invention; Figure 6 This is a graph showing the overall running time ratio in an embodiment of the present invention; Figure 7 This is a comparison of the cost estimation Q-error of the model across databases in the embodiments of the present invention. Detailed Implementation

[0015] It should be noted that the following detailed descriptions are exemplary and intended to provide further illustration of the invention. Unless otherwise specified, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains.

[0016] It should be noted that the terminology used herein is for the purpose of describing particular implementations only and is not intended to limit the exemplary implementations of the present invention.

[0017] Where there is no conflict, the embodiments and features in the embodiments of the present invention can be combined with each other.

[0018] Terminology Explanation: SQL: Structured Query Language.

[0019] LCMs: Learned Cost Models.

[0020] DFS: Depth First Search.

[0021] CA-CNP: Cost-Aware Conditional Neural Process.

[0022] FFN: Feed-Forward Network.

[0023] MLP: Multi-Layer Perceptron.

[0024] HINT: Query plan control hint mechanism.

[0025] Q-error: Cost estimation error evaluation index, defined as the ratio of the larger to the smaller of the predicted cost and the actual cost.

[0026] Example 1 The query optimizer is a key component of a Database Management System (DBMS). Its core task is to transform user-submitted SQL queries into efficient execution plans, thereby reducing query response time and system resource overhead. The physical optimization phase is the core of the query optimizer, mainly consisting of three parts: plan enumeration, cost estimation, and cardinality estimation. Plan enumeration is responsible for combining access paths, join orders, and physical operators to generate candidate execution plans. Cardinality estimation is used to predict the data size output by each operator, providing basic parameters for cost calculation. Cost estimation comprehensively considers resource consumption factors such as CPU computational overhead and disk I / O operations, calculates the overall execution cost of each candidate execution plan, and selects the lowest-cost plan as the final query execution plan for the execution engine to execute.

[0027] Of the three key components of physical optimization, cost estimation directly determines the comparison and selection of candidate execution plans. For example, for multi-table joins on large-scale data tables, different strategies such as hash joins or nested loop joins can result in execution costs differing by orders of magnitude. More reliable cost estimation can help the system avoid high-overhead access methods such as full table scans, thereby significantly shortening the response time of complex queries. Therefore, cost estimation is a crucial factor affecting the effectiveness of query optimization.

[0028] For real-world database query optimization scenarios, the application of learned cost models suffers from several drawbacks. Firstly, the inconsistent sample distribution between the training and inference phases leads to insufficient effectiveness in comparing and selecting candidate execution plans. Secondly, existing query plan representations are largely based on the schema semantics and predicate information of specific database instances, resulting in strong instance dependence in the learned cost patterns. This makes it difficult to form stable and transferable representations across databases and in cold-start environments. Furthermore, existing learned cost models still struggle to meet the efficiency requirements of real-world database systems. Cost estimation is crucial in the query optimizer, and its inference latency directly amplifies the query optimization latency. Therefore, the model must be able to estimate the cost of candidate execution plans with low latency. However, existing methods often improve accuracy by introducing more complex network structures, leading to decreased inference speed and increased training costs. Cross-database models, needing to cover a wider variety of databases and workloads, further increase model complexity and training overhead.

[0029] For example, QueryFormer, proposed by Zhao et al., is a query optimization neural network based on the Transformer architecture. This method encodes the query execution plan as a sequence representation and introduces a self-attention mechanism with tree structure bias. It uses the tree distance between nodes as a learnable bias to characterize node dependencies and long-path information flow. Simultaneously, this method combines statistical features such as histograms to enhance the expression of input information and model long-range dependencies in complex execution plans. In its implementation, QueryFormer first encodes the features of each node in the query plan tree, including one-hot encoding of operator types, histogram encoding, and sampled bitmap features. Then, it calculates the distance between nodes in the query plan tree and introduces this distance as a learnable attention bias term into the Transformer's self-attention calculation, enabling the model to perceive the tree structure positional relationships between nodes during global information interaction. Finally, it outputs the cost estimate of the query plan by mapping the root node representation. However, the training data for this method mainly comes from the locally optimal plans selected by the optimizer, lacking explicit modeling of the relative cost differences between candidate plans, resulting in insufficient predictive effectiveness in the candidate execution plan space. Furthermore, this method's learnable bias based on tree distance only introduces structural priors into the attention score, without explicitly constraining the scope of information interaction between nodes. In the cost modeling of the execution plan, allowing nodes to focus on global nodes without constraints during attention computation easily introduces information dependencies inconsistent with the actual execution plan logic. Additionally, the method introduces features closely related to specific database instances, such as histograms and sampled bitmaps, in its feature design. These features lack consistent semantic interpretation across different database environments, limiting the model's cross-database generalization ability. Using only the total cost corresponding to the root node of the query plan as a supervision signal lacks full utilization of sub-plan-level cost information. In scenarios with large connection depths and complex operator combinations, the supervision signal is sparse, making it difficult to fully characterize the cost features of sub-plans at different levels.

[0030] Hilprecht et al. proposed a zero-shot cost estimation method called Zero-Shot, which aims to predict the runtime of a query plan directly on unseen databases without needing to recollect a large number of training queries for new databases. The core idea of ​​this method is to divide the cost estimation process into two parts: a database-independent learning-based cost model and database-dependent data features. By pre-training on multiple databases and workloads, the model learns a general mapping relationship between different query plan structures and runtime, thus achieving zero-shot generalization across databases. In its implementation, the method represents the query plan as a heterogeneous graph containing various node types, including plan operators, predicates, tables, attributes, and output columns, and constructs transferable features for each type of node. Then, the method uses a multilayer perceptron partitioned by node type to encode each node and performs bottom-up message passing according to the topological order, aggregating child node information layer by layer to the parent node. Finally, the root node is hidden, representing the input to the estimation network, and the output is the predicted runtime of the entire query plan. During the inference phase, this method requires supplementing the query plan with database directory information and intermediate result cardinality, among other data features. Some of the intermediate cardinality can be obtained using additional data-driven models. Furthermore, this method supports further fine-tuning to a few-shot mode on new databases using a small number of samples. While this method achieves zero-shot generalization across databases, its training and inference processes are essentially still runtime regressions for individual query plans, with the overall cost represented by the root node. Therefore, this method does not explicitly model the relative merits of different candidate execution plans for the same query, nor does it possess a contextual comparison mechanism for the candidate plan set. Consequently, the effectiveness of comparing and selecting candidate execution plan spaces remains limited in real query optimization. To achieve zero-shot transfer, this method constructs a heterogeneous graph representation containing multiple types of nodes, including tables, attributes, and predicates, and relies on various database-related features such as page count, row count, attribute relevance, number of distinct values, null value ratio, and intermediate result cardinality. In particular, the intermediate result cardinality requires additional data-driven models or other complex techniques, which increases the complexity of feature extraction, system deployment, and maintenance, and makes the model's performance dependent to some extent on the quality of external data features.

[0031] This embodiment employs a transferable feature representation centered on operator type, optimizer cardinality estimation, and cost estimation, which is more conducive to reducing feature engineering complexity. This method uses a bottom-up message passing mechanism and only utilizes the runtime of the entire query plan corresponding to the root node as the primary prediction target, lacking parallel modeling and hierarchical supervision of sub-plan-level cost information. Therefore, in scenarios with deep connection layers and complex operator combinations, the supervision signal is prone to sparseness, making it difficult to fully characterize the fine-grained cost features of sub-plans at different levels.

[0032] Since this embodiment studies not only the query execution plan actually selected and executed by the optimizer, but also incorporates candidate execution plans into the modeling and reasoning process, the two are collectively referred to as query plans.

[0033] This embodiment proposes a query optimization method and system based on structure awareness and context modeling, aiming to achieve efficient and accurate estimation of execution costs by fully modeling the query plan structure and contextual dependencies. The overall framework is as follows: Figure 1 As shown, it mainly includes four core parts: training data collection, query plan feature extraction and encoding, two-stage progressive learning framework, model inference and cost estimation.

[0034] During the training data collection phase, query workloads are first executed on multiple known database instances to collect query plans, statistics, and actual execution time labels, which constitute the model training data.

[0035] The next step is feature extraction and encoding. Using the query plan tree as the basic modeling object, the query plan is traversed and encoded. Combined with statistical information and optimizer prior knowledge, the structural features and node semantic features of the query plan are extracted to form the model input representation. At the same time, the actual execution time is used as a supervision signal.

[0036] Building upon this foundation, the model is trained using a two-stage progressive learning framework. The first stage uses query plan samples collected across databases as a foundation, focusing on learning the structural representation and cost modeling capabilities of the query plans. The second stage further introduces query plan samples of varying quality to enhance the model's perception of differences in plan quality, thereby improving its effectiveness in cost estimation within the candidate execution plan space. After these two stages of training, a pre-trained SCANet model is obtained and optimized using the loss between the true labels and the predicted results.

[0037] During the inference phase, SCANet receives test data from an unseen database and generates a query plan representation through a feature extraction process consistent with the training phase. Subsequently, the pre-trained model performs inference, ultimately outputting a cost estimate of the query plan, i.e., the query execution time. Through this design, the SCANet model can effectively model the complex relationship between the query plan and the actual cost, fully utilizing query plan information and optimizer prior knowledge, thus providing reliable cost estimation support for subsequent query optimization decisions.

[0038] The following is a detailed description of the query optimization method based on structure awareness and context modeling proposed in this embodiment: Step 1: Using the query plan tree as the basic modeling object, traverse and encode the query plan, extract the structural features and node semantic features of the query plan, and construct a training set using the actual execution time as the supervision signal.

[0039] From the query plan The following three types of information are extracted: plan structure dependency features, node-level semantic features, and subplan-level features. These are used to characterize the overall structure of the query plan, the local semantics of nodes, and the hierarchical information of subplans, respectively.

[0040] (1) Planning structure dependency characteristics.

[0041] Query Plan The tree structure represents the execution order between nodes. To formally describe this structural dependency, it is defined as a directed acyclic graph as follows. :

[0042] in, Represents a set of node sequences. This represents the set of directed dependencies between nodes.

[0043] To explicitly model this dependency while preserving the original tree structure information, this embodiment uses Depth-First Search (DFS) to traverse the query plan tree. DFS prioritizes child nodes, naturally satisfying the "child before parent" order, and can simultaneously obtain the node sequence representation. and the corresponding set of partial order relations Query plan Structural information Matrixing yields the adjacency matrix For any two distinct nodes If and only if node It is a node The parent node, at this time Otherwise, it is 0.

[0044] (2) Node-level semantic features.

[0045] For each operator node, a set of general information independent of any specific database schema is extracted, including operator type, cardinality estimate, and cost estimate. The operator type indicates the specific operator used by the node (e.g., nested loop joins, sequential scans, etc.); the cardinality and cost information are estimates provided by the database query optimizer. The cardinality estimate reflects the estimated number of rows output by the operator, while the cost estimate is the relative cost calculated by the optimizer based on the cardinality and the system's preset cost model parameters.

[0046] For query plan Each node in the query plan consists of three parts in its feature encoding: operator encoding, cardinality estimation encoding, and cost estimation encoding. For operators in the query plan node, such as Sequential Scan and Index Scan, these are typical discrete category features. One-hot encoding is used to represent the operator type. Let... A collection of operator types, dictionary size is any node Corresponding operator type Its one-hot encoding is represented as a vector. ,satisfy:

[0047] Among them, with nodes The dimension corresponding to the operator type takes a value of 1, while the other dimensions take a value of 0.

[0048] The query optimizer provides a cardinality estimate. Cost estimation Both are continuous statistical features, and their values ​​usually have significant scale differences between different operator nodes. If the original estimated values ​​are directly used as model input, the model training process is easily dominated by extreme values, which leads to a decline in generalization performance.

[0049] Therefore, this embodiment uses a robust scaling method based on the median and interquartile range to normalize the cardinality and cost estimates, mapping continuous statistical features to a unified numerical space. For any node Continuous features The calculation formula is as follows:

[0050] in, This represents the set of values ​​for this feature in the training set. The median. and These represent the 25th and 75th percentiles, respectively.

[0051] This scaling method, centered on the median and scaled by the interquartile range, effectively suppresses the influence of extreme estimates and better aligns with the cardinality and cost statistics of the query optimizer output. After numerical scaling, both the cardinality estimation code and the cost estimation code participate in node feature construction in a normalized form.

[0052] Discrete operator one-hot encoding The scaled cardinality estimation features and cost estimation features are concatenated at the node level to form the final feature vector for each node in the query plan. The eigenvector representation is defined as:

[0053] (3) Sub-plan level characteristics.

[0054] Query Plan Each node in the algorithm corresponds to a sub-plan rooted at that node. The information of these sub-plans significantly impacts the execution cost of the overall plan. While sub-plan-level execution costs are introduced during feature extraction, learning the cost estimates for all sub-plans can easily lead to information redundancy. For example, a leaf node at the bottom level of the query plan might appear in multiple upper-level sub-plans, resulting in repeated calculations during the learning of each sub-plan. Therefore, a specialized sub-plan hierarchy loss adjustment strategy is designed to address this information redundancy.

[0055] This strategy introduces node height information, defined as the shortest path length from that node down to a leaf node. Node height reflects the node's level and the size of the sub-plans it covers. This is achieved by recording the height set of each node during DFS traversal. During the encoding phase, the scope information and hierarchical structure of the sub-plan corresponding to each node can be obtained. During the training phase, the prediction errors at the sub-plan level are weighted based on the hierarchical information of the nodes to achieve the training objective of hierarchical correction. Considering that the execution plans of different queries may have significant differences in structural depth, this strategy uses normalized node height as the basis for loss weighting, making the loss weights reflect more the position of the node in the relative hierarchical structure than its absolute depth. For query plans... any node in Its normalized height is defined as:

[0056] in, Represents a node height, To execute the plan The maximum height of all nodes in the array.

[0057] Optionally, in the query plan feature extraction and encoding part, in addition to using the structure dependency matrix extraction and robust scaling encoding method based on depth-first search provided in this embodiment, breadth-first search can be used to traverse the query plan tree to obtain the node sequence, or other normalization methods (such as Z-Score normalization, Min-Max normalization) can be used to normalize and map continuous statistical features.

[0058] Step 2: Based on the training set, a two-stage progressive learning approach is used to train the cost estimation model. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters.

[0059] This embodiment first introduces the first stage of the two-stage progressive learning framework. The overall framework of the structure-aware cost modeling stage, i.e., the first stage, is as follows: Figure 2 As shown, the model takes the node encoding sequence of the query plan as input. First, it models the dependencies between nodes by introducing a structural constraint attention mechanism. Then, it achieves deep fusion of node semantics and structural information based on the Transformer structure. On this basis, a cost-aware context aggregation module, CA-CNP, is designed to model global conditional information. Finally, a sub-plan parallel learning mechanism is used to jointly supervise the costs of sub-plans at different levels. This embodiment will not repeat the basic structure of the Transformer, but focuses on the query plan cost estimation task, emphasizing the targeted mechanism design and module extension of the existing model framework.

[0060] This paper introduces a plan structure constraint attention mechanism into Transformer, which limits the scope of information interaction between nodes by explicitly constructing a structure constraint mask matrix. Unlike the learnable bias method based on the tree distance between nodes in QueryFormer, this method does not introduce additional structure bias parameters. Instead, it directly utilizes the structural relationship of the query plan to prune attention connections, ensuring that the attention calculation process is consistent with the structural constraints of the execution plan.

[0061] For query plan Expanding the sequence yields a node sequence with a defined order. :

[0062] in, Indicates the query plan The number of nodes in the sequence, for any node in the sequence Its node feature vector All node features are arranged according to the node sequence By concatenating the elements in the correct order, a query plan can be constructed. Node encoding matrix:

[0063] Based on node encoding matrix The structurally constrained attention mechanism generates a query, key, and value matrix through linear mapping, specifically defined as:

[0064] in, , , , , For a learnable parameter matrix, This represents the dimension of the query and key vectors. Indicates the dimension of the value vector.

[0065] To ensure that information exchange between nodes strictly follows the structural dependencies of the execution plan, the extracted structural dependency matrix is ​​used. Used as a Transformer attention mask, and converted into an additive mask form. By introducing a negative infinity penalty to node pairs that do not satisfy structural dependencies in the attention score, the corresponding attention weights after softmax normalization are zero, defined as:

[0066] Under the constraint of the program structure dependency matrix, the Transformer self-attention weight matrix is ​​calculated as follows:

[0067] And based on this, the attention output constrained by the plan structure is obtained:

[0068] To further enhance the model's ability to model complex dependencies in query plans, this embodiment introduces a multi-head attention mechanism based on the aforementioned structurally constrained self-attention. For the first... Each attention head uses an independent linear mapping parameter. , and Generate the corresponding query, key, and value matrices, and use the same structure mask. Computational Structure Constraint Attention Output under Constraints The outputs of each attention head are then concatenated along the feature dimension and a linear mapping is used to obtain the final output representation of the multi-head attention. For the , Nodes:

[0069] in, This is the output mapping matrix.

[0070] This design enables the model to model dependencies between nodes in parallel from multiple representation subspaces under unified structural constraints, thereby enhancing its expressive power for complex query plans.

[0071] After aggregating information between nodes in the query plan, a feed-forward network (FFN) is used to apply a nonlinear mapping independently to each node representation position-wise, further enhancing the expressive power of node features. The calculation formula is as follows:

[0072] in, , and , These are learnable parameters.

[0073] By introducing nonlinear mapping at the node dimension, FFN can reorganize the features of the linear combination results after attention aggregation of the plan structure, making the model more effective in distinguishing the differences in operator combination complexity and cost growth trend of different sub-plans.

[0074] The vector representations obtained through the aforementioned process are essentially still deterministic representation learning based on fixed-structure interaction patterns. That is, the update of node representations mainly relies on the structural relationships within the execution plan and the semantic information of operators, without explicitly modeling the conditional constraints implied by the observed costs between different sub-plans. In scenarios lacking historical workloads or cross-database deployments, the cost distributions between different query plans may differ significantly. In such cases, relying solely on structural interactions between nodes for cost estimation is insufficient to form stable conditional inference capabilities and has low efficiency in utilizing limited samples.

[0075] The Conditional Neural Process (CNP), proposed by Garnelo et al., is based on the idea of ​​encoding a variable-sized set of contextual observations into a permutation-invariant global representation, which is then combined with the target input for conditional prediction. This approach retains the efficient reasoning capabilities of neural networks while achieving rapid adaptability and uncertainty modeling capabilities similar to Gaussian processes. CNP and its subsequent work have validated the effectiveness of this type of method on typical tasks such as regression, classification, and image completion, demonstrating strong potential for few-shot conditional modeling, rapid adaptation, and cross-task transfer.

[0076] This embodiment designs a cost-aware conditional neural process (CA-CNP) module, such as... Figure 3As shown, by conditionally modeling the cost context information of the observed sub-plans, the inference stability and generalization ability of the model under limited observation conditions are improved.

[0077] Set up a query plan The set of sub-plans is Each sub-plan corresponds to a node representation vector. (Obtained from the output of the structural constraint attention mechanism) and its corresponding observation cost During the training phase, the cost of observation The cost is derived from the actual execution cost of the sub-plan; during the reasoning phase, since the candidate execution plan has not yet been actually executed... Use the cost estimate given by the query optimizer for this subplan.

[0078] The sub-plan is divided into a context set and a target set, where the context set is... It contains several observed sub-plan-cost pairs, and an objective set. The sub-plan that requires cost prediction is represented as follows:

[0079] To fully utilize contextual information, CA-CNP employs an attention-based context aggregation approach for any target sub-plan. Its context representation is calculated in the following way:

[0080] in, This represents the context encoding function, used to jointly embed the sub-plan representation with its observation cost.

[0081] In this embodiment, It is implemented using a two-layer perceptron MLP: its input is the sub-plan representation vector. Compared to the observation cost after normalization The concatenated vector, after undergoing a first-level linear transformation and ReLU activation, is input into a second-level linear layer, and the final output is... Dimensionally consistent context embedding vectors.

[0082] This represents the correlation weight between the target sub-plan and the context sub-plan, used to measure the contribution of different contextual information to the current target prediction. The attention-based approach using scaled dot products is employed for calculation, and the specific formula is as follows:

[0083] Through this mechanism, different target sub-plans can adaptively select more relevant contextual information for conditional inference based on their own characteristics. This is achieved by obtaining a contextual aggregation representation. Afterwards, CA-CNP will The common input probability decoder yields the conditional prediction of the target sub-plan cost. This prediction is given in distribution form, where, This represents the predicted mean used for cost estimation. This represents the prediction variance, reflecting the uncertainty and reliability of the prediction results under the current context:

[0084] A sub-plan parallel cost modeling mechanism is adopted to jointly model and predict the costs of all sub-plans. Under the structurally constrained attention mechanism, the Transformer encoding layer computes the hidden representations of all nodes in parallel. Each node representation corresponds to a sub-plan representation rooted at that node. The CA-CNP module aggregates the observed sub-plan cost context and outputs the cost estimate distribution of the corresponding sub-plan through the probabilistic decoder.

[0085] At the implementation level, the output of CA-CNP is further mapped through a shared multilayer perceptron (MLP) to generate cost estimates for all sub-plans in parallel. For the query plan... The model estimates the cost of each sub-plan using the following formula:

[0086] This design enables the model to simultaneously estimate the costs of all sub-plans in a single forward propagation. On the one hand, it significantly improves the computational efficiency of the training and inference phases. On the other hand, by jointly learning the costs at the sub-plan level, it enhances the density of the supervision signal and alleviates the problem of insufficient information caused by relying solely on root node supervision.

[0087] Optionally, in terms of structure-aware modeling, in addition to using the plan structure constraint attention mechanism provided in this embodiment, graph neural networks, tree-structured recurrent neural networks, or other structured attention mechanisms can also be used to model the node dependencies in the query plan.

[0088] Optionally, in terms of context information modeling, in addition to using the cost-aware conditional neural process module CA-CNP provided in this embodiment, set-based encoding methods (such as DeepSets) or other conditional prediction models can also be used to model the sub-plan cost context information.

[0089] The second stage of the two-stage progressive learning framework is described below: After completing structure-aware cost modeling, the model can achieve relatively ideal cost estimation accuracy on the offline test set. However, relying solely on the reduction of prediction error is insufficient to guarantee an improvement in end-to-end query optimization performance. In real query optimization, the optimizer needs to select from a wide space of candidate execution plans; if the model fails to fully learn the differences between good and bad plans, even with a low overall prediction error, the optimizer may still select a significantly suboptimal execution plan due to severe misestimation of a few key plans, ultimately resulting in a decrease in query performance instead of an increase.

[0090] A key reason for the aforementioned problems is that the training samples of existing learning-based cost models are typically derived from query plans generated and actually executed by the optimizer. This causes the model to primarily learn local patterns of near-optimal plans during the training phase, lacking effective coverage of inferior candidate plans. Taking join operators as an example, inferior plans containing nested loop joins (NLJ) often lead to extremely high execution times in large table join scenarios, even triggering execution timeouts. However, such timeout negative samples are usually not collected during the training data acquisition phase, ultimately only including a small number of NLJ examples that can be completed quickly on small-scale data. Therefore, the model is prone to forming biased knowledge inductions during training, systematically underestimating the cost of NLJ in large table scenarios during prediction. This causes the optimizer to incorrectly select such high-risk execution plans during the plan selection phase, significantly amplifying the probability of misestimation and the risk of performance degradation in practical applications.

[0091] Based on the above motivations, this embodiment introduces a phase for enhancing the perception of plan quality, the overall process of which is as follows: Figure 4 As shown, this stage uses a constructed set of candidate execution plans as training samples to explicitly introduce information about plan quality differences. For each candidate execution plan, the model uses the previously designed feature extraction module and the trained backbone network to generate a corresponding high-quality plan embedding representation. Based on this, a CA-CNP module is introduced to perform contextual modeling on the constructed set of candidate plans, enabling the model to explicitly perceive the cost differences brought about by different execution strategies during training. The plan quality awareness enhancement stage is only used during progressive training to update the backbone network parameters obtained in the first stage of SCANet; in the inference stage, the model directly uses the enhanced backbone network for cost estimation.

[0092] For the construction of the candidate execution plan set, the HINT mechanism supported by the query optimizer is used to intervene in the physical execution strategy during the query optimization stage without changing the query's logical semantics. Multiple candidate execution plans with different physical execution strategies are generated for the same query, and these candidate plans are actually executed to obtain the corresponding real cost information. The feature construction and encoding process of the candidate execution plan set follows the query plan feature extraction and encoding method in step 1. The backbone network trained in the structure-aware cost modeling stage is used to introduce a structure-constrained attention mechanism to model the encoded plan, thereby mapping each candidate execution plan to a unified plan embedding representation.

[0093] Furthermore, the CA-CNP module is introduced to perform contextual modeling on the candidate execution plan set. This enables explicit learning of the impact of execution strategy changes on cost at the candidate set level, allowing cost differences between different physical execution strategies to be effectively characterized through contextual information. This enhances the ability to identify high-risk execution plans while maintaining the accuracy of cost estimation, thereby improving the effectiveness of the learning-based cost model in real query optimization scenarios.

[0094] Optionally, in the candidate plan quality perception part, reinforcement learning methods, ranking learning methods, or other plan generation strategies can be used to construct candidate execution plan samples of different qualities.

[0095] This embodiment employs a two-stage progressive training strategy. The training objective is to gradually enhance the model's ability to perceive differences in execution plan quality, based on a thorough understanding of the structural dependencies and hierarchical cost characteristics of query execution plans. During model inference, it is required to provide stable and reliable cost estimates for individual candidate query execution plans under low latency conditions.

[0096] In the structure-aware cost modeling phase, the model uses query execution plans collected across databases as training samples, focusing on learning the structural dependencies, operator semantic features, and sub-plan-level cost patterns within the query plans. To fully utilize the supervision signals provided by sub-plans at different levels within the query plan, cost estimation is performed simultaneously on all sub-plans in the execution plan, and the prediction errors at the sub-plan level are jointly optimized. Execution Plan Each node in This corresponds to a sub-plan rooted at that node. The model's cost estimate for this sub-plan is denoted as... Its actual execution cost is recorded as Considering the information redundancy issue during training of sub-plans at different levels, SCANet introduces a sub-plan hierarchical loss adjustment strategy, assigning different weights to the loss terms corresponding to different nodes. Specifically, based on the node height information extracted during the query plan feature extraction process in step 1... After normalization, the nodes are defined. The loss weights are:

[0097] in, is a hyperparameter used to control the decay rate of loss weights for sub-plans at different levels; nodes with higher heights are assigned lower loss weights. Since lower-level sub-plans are repeatedly contained within multiple higher-level sub-plans, assigning excessively high weights to their prediction errors can lead to overfitting of recurring local patterns during training, resulting in information redundancy. Based on this, the overall training loss for the first stage, i.e., the structure-aware cost modeling stage, is defined as:

[0098] This loss function weights and sums the prediction errors at the sub-plan level, enabling the model to simultaneously consider the global execution plan cost and the cost characteristics of local sub-plans during training. In training implementation, the backpropagation algorithm is used to optimize the loss function, and the model parameters are updated using a stochastic gradient descent-based optimization method (Adam). Since the sub-plan cost estimation employs parallel modeling, the above loss calculation process can also be completed in parallel at the node level without introducing additional training overhead.

[0099] The prediction cost of the sub-project is jointly determined by the structural feature representation extracted by the backbone network and the conditional context representation generated by the CA-CNP module. Therefore, the above loss function optimizes the prediction cost error while jointly updating all parameters of the backbone network and the CA-CNP module through backpropagation. The CA-CNP module context set... The observation cost in During the training phase, the actual execution cost of the subplan can be used directly. During the inference phase, since the candidate plan has not yet been actually executed, the cost estimate given by the query optimizer is used as the observed cost in the context.

[0100] To mitigate the issue of inconsistent context sources between the training and inference phases, this embodiment introduces a context cost hybrid strategy. In each training iteration, the context set... Each sample in With probability Replace its observed cost with the cost estimate given by the query optimizer:

[0101] in, It is used to control the proportion of optimizer cost estimation used during training.

[0102] The core idea of ​​this strategy is that the actual execution cost helps the model learn the accurate cost dependencies between sub-plans, while the optimizer cost estimate simulates the actual input conditions during the inference phase, enabling the model to adapt to contextual observations containing systematic biases.

[0103] It should be noted that the above-mentioned context cost hybrid strategy only applies to the observation costs in the CA-CNP context set. The supervision labels in the training loss function always use the actual execution cost of the sub-plan to ensure the accuracy of the model's learning objectives.

[0104] In the second stage, the plan quality awareness enhancement phase, the model further introduces a set of candidate execution plans corresponding to different physical execution strategies under the same query to explicitly model plan quality differences. This stage uses the backbone network structure obtained in the first stage and updates the model parameters accordingly. The CA-CNP module enhances the conditional inference capability under limited observation conditions. The training objective is to maximize the conditional likelihood of the target variable under given context conditions by randomly partitioning the context set and the target set, thereby improving the model's adaptability to different context sizes and distribution variations.

[0105] The CA-CNP module is based on context sets. For the target sub-plan Perform conditional modeling and output the predicted distribution of the cost of the target sub-plan. The training loss function is defined as the negative conditional log-likelihood of the target set under context conditions:

[0106] This expectation is approximated by a Monte Carlo method using random context partitioning. For the target set The observation cost corresponding to each target sub-plan is used as the supervision signal during the training phase, and the actual execution cost of the sub-plan is used as the supervision signal. C is the feature representation vector extracted from the backbone network for the target sub-plan; C is the context set, which consists of several observed sub-plan-cost pairs obtained by random partitioning. The structure is used to provide conditional information for cost inference of the target sub-plan; the context cost hybrid strategy in formula (21) Observation cost that applies only to the context set C (i.e., the input side), while in formula (22) the supervisory signal is used as the signal. The actual execution cost of the target sub-plan is always used and is not involved in the hybrid replacement, in order to ensure the accuracy and consistency of the model's learning objectives.

[0107] By minimizing the aforementioned loss function, the model can learn a stable conditional cost inference mechanism under different context scales and sub-plan combinations, thereby explicitly characterizing the quality differences between different plans. In the second stage of model training, the model is initialized with the parameters obtained in the first stage and the overall model is jointly fine-tuned. The conditional log-likelihood loss used is updated simultaneously with the parameters of the backbone network and the CA-CNP module through the backpropagation algorithm, enabling the conditional modeling capability of the CA-CNP module to adapt to the distribution differences between candidate execution plans.

[0108] Inference phase: Query plan for the input The model first extracts and encodes query plan features, and then models the dependencies between nodes using a structural constraint attention mechanism. Subsequently, the CA-CNP module performs conditional inference of cost information at the sub-plan level. Since the candidate execution plans have not yet been actually executed, the actual execution costs of the sub-plans cannot be obtained. Therefore, during the inference phase, the cost estimates provided by the query optimizer for each sub-plan are uniformly used as the observed costs in the context set.

[0109] A subset of sub-plans is sampled from the query plan's sub-plan set. The representation vectors output by their structural constraint attention mechanism, along with the corresponding optimizer cost estimates, form the context set for the inference phase. The remaining sub-plans are used as the target set for conditional cost estimation. Cost estimation results for all sub-plans are output in parallel through a shared MLP layer, with the cost corresponding to the root node serving as the total cost estimate for that execution plan. Since the model has fully learned the structural dependencies within the execution plans and the cost patterns of sub-plans at different levels during training, the inference process only requires one forward propagation to complete the cost estimation for all sub-plans, thus improving computational efficiency.

[0110] Through the above training and reasoning design, the scheme in this embodiment can achieve stable, accurate and efficient cost estimation by making full use of query plan structure information and optimizer prior knowledge.

[0111] The following describes the experimental method used in this embodiment: To more comprehensively evaluate the performance of the model proposed in this embodiment, the publicly available benchmark dataset proposed by Benjamin et al. was selected as the experimental basis. This benchmark includes 20 databases (including IMDB, TPC-H, SSB, etc.), covering real-world datasets and classic synthetic datasets. These databases differ significantly in terms of the number of tables, columns, foreign key relationship size, and attribute value distribution, and can comprehensively characterize the heterogeneity of different databases in schema structure and data distribution.

[0112] To evaluate the performance of the model in this embodiment from different dimensions, three types of workloads were designed. All queries were actually executed, and the corresponding query execution plans and actual runtimes were obtained using the PostgreSQL EXPLAIN ANALYZE command, as detailed below: (1) Workload 1: Candidate plan evaluation workload.

[0113] To evaluate the accuracy of the learning-based cost model in estimating the cost of candidate plans and its end-to-end benefits in query optimization tasks, 1,000 queries containing multi-table joins and diverse filtering conditions were generated based on foreign key relationships in four datasets: IMDB, TPC-H, Baseball, and SSB. Multiple candidate execution plans were then constructed for each query to simulate the comparison and selection of candidate plans during query optimization. Example queries are shown below: SELECT AVG("pitching"."BB"), COUNT(*) FROM "pitching" JOIN "players" ON "pitching"."playerID" = "players"."playerID" WHERE "players"."college" = 'Glendale (CA) CC' AND "players"."lahmanID"<= 13662 AND "pitching"."HBP"<= 0.0; By using PostgreSQL's HINT function to control the execution of each query using three join strategies—Hash Join (HJ), Sort Merge Join (SMJ), and Indexed Nested Loop Join (INLJ)—a set of candidate execution plans is constructed for the same query. The actual runtime and execution plan information of each candidate plan are then obtained. Based on this set of candidate execution plans, this paper not only evaluates the model's prediction accuracy of the cost of candidate execution plans but also analyzes the actual operational benefits of using the model's prediction results to guide plan selection, thus more comprehensively reflecting the model's effectiveness in real-world query optimization tasks.

[0114] (2) Workload 2: Cross-database workload.

[0115] To evaluate the model's prediction accuracy across database scenarios, 12,000 complex queries were constructed for each of 20 databases. These queries covered various join structures, filtering conditions, and query patterns to create a diverse range of training and testing samples. The experiments employed leave-one-out cross-validation: in each round of experiments, one database was fixed as the test set, while the remaining 19 databases were used for training, examining the model's adaptability to different pattern structures and data distribution differences.

[0116] (3) Workload 3: Cost estimation evaluation workload.

[0117] To further evaluate the model's prediction accuracy on the cost estimation task, a comprehensive evaluation workload was constructed based on the IMDB dataset. IMDB was chosen because it originates from the real world, contains multiple related tables with strong correlations between attributes within and between tables, and has a large data scale, making the cost estimation task more challenging. The training phase used 100,000 generated queries, while the testing phase consisted of four query sets: Synthetic, Scale, JOB-full, and JOB-light. These four query sets have different design focuses, allowing for the evaluation of the model's cost estimation performance from different perspectives.

[0118] Synthetic: This set contains 5,000 queries, featuring a rich variety of join structures and filtering combinations, resulting in a large query volume. This query set is primarily used to evaluate the overall cost estimation performance of the model in complex query scenarios.

[0119] Scale: Contains 500 queries, primarily used to evaluate the model's performance under varying data scales. This query set maintains a similar structural pattern to the original queries, achieving different data distributions and execution costs by expanding the data scale.

[0120] JOB-full: Contains 113 queries, derived from the JoinOrder Benchmark (JOB), a standard test set in the field of Join order selection research. It includes a set of complex multi-table join queries constructed based on IMDB. It exhibits strong sensitivity to join order and has a complex query structure, used to evaluate the model's cost estimation ability in complex join scenarios.

[0121] JOB-light: With 70 queries, it is one of the most commonly used query sets in the field of cost estimation research. While retaining the typical multi-table join pattern, it reduces computational complexity and facilitates experimental testing with lower overhead.

[0122] Baseline Methods. To comprehensively evaluate the SCANet model, the experiment selected multiple models, including traditional methods and learning-based methods, as baselines for comparison, as follows: (1) PostgreSQL: As a classic open-source database system, PostgreSQL's cost estimation mainly relies on the statistical information and heuristic rules built into the query optimizer, which can be used as a representative baseline of traditional cost estimation methods.

[0123] (2) Flat Vector: An earlier baseline method that represents the physical execution plan as a fixed-dimensional vector and summarizes intermediate statistics in units of different operator types. This paper uses the advanced LightGBM regression model to predict the execution plan runtime. This model has a relatively simple structure and low implementation cost.

[0124] (3) End-to-End: An end-to-end learning-based cost estimation method that uses a tree-structured neural network to model the query execution plan. This method learns the mapping relationship between the execution plan structure and the running cost by encoding operator features and recursively aggregating them along the execution plan tree.

[0125] (4) QPPNet: A neural network cost model oriented towards execution plan structure, whose model structure corresponds to the execution plan tree generated by the optimizer. This method designs modular subnetworks for different operator types and realizes query delay prediction through information propagation and combination on the tree structure, which is representative in plan-aware modeling.

[0126] (5) TPool: A tree-structured learning-based estimation method that encodes the semantics and heterogeneous features of operators in the execution plan and aggregates them on the plan tree to achieve joint modeling of costs and related statistics. This method can utilize the hierarchical structure information of the execution plan and is one of the typical tree-structured plan representation methods.

[0127] (6) QueryFormer (QueryF): A query optimization neural network based on Transformer, which encodes the query execution plan as a sequence representation and combines statistical features such as histograms to enhance the expression of input information. It uses a tree-structure-related attention mechanism to model long-range dependencies in complex execution plans, and has strong representation capabilities in complex plan scenarios.

[0128] (7) BAO: A learning-enhanced query optimization method that improves the plan selection process by using hints and reinforcement learning mechanisms on the basis of traditional optimizers. Its cost evaluation part is usually based on the vectorized execution plan tree representation to estimate the query execution time, so it can be used as a representative baseline method for practical query optimization tasks.

[0129] (8) Zero-Shot: The first database-agnostic cost estimation method for cross-database generalization, aiming to achieve cost estimation without retraining. This method utilizes transferable execution plan-level features and graph structure representations to learn query execution plans and related information, and is highly representative in cross-database cost estimation evaluation scenarios.

[0130] (9) Roq: A risk-aware method for robust query optimization, which combines graph neural networks to learn the representation of queries and execution plans, and simultaneously estimates the execution cost and its uncertainty. This method improves the optimization stability in risky scenarios by using predicted costs and risk information together for execution plan selection.

[0131] (10) Reqo: A learning-based cost estimation model for robustness and interpretability, which makes coordinated improvements to the three stages of query execution plan generation, plan representation and plan selection. This method enhances the reliability of cost estimation and plan selection through bidirectional graph neural network plan representation and execution plan ranking mechanism.

[0132] Evaluation metrics. This chapter's experiments use three evaluation metrics: (1) Cost estimation error Q-error: It measures the relative deviation between the model's predicted cost and the actual execution cost, and is a classic metric for evaluating cost estimation models, particularly for query plans. The calculation is defined as follows:

[0133] in, The model represents the query plan. The predicted cost, Indicates the query plan The true cost, i.e., the actual running time, is considered; the closer this value is to 1, the more accurate the prediction. The experiment analyzes the Q-error values ​​(including mean, median, or quantiles) of all query plans on the test set, reflecting the overall prediction accuracy of the model.

[0134] (2) Plan Selection Rate: This is used to measure the ability of a cost model to accurately select the optimal execution plan from a set of candidate execution plans. For each query... Let its candidate execution plan set be . The execution plan selected by the model is defined as the candidate plan with the minimum prediction cost, denoted as... The true optimal execution plan is defined as the candidate plan with the lowest actual execution cost, denoted as . Then the planned selection rate Defined as:

[0135] in, This indicates the number of queries in the test set. This is an indicator function; it takes a value of 1 when the condition within the parentheses is true, and a value of 0 otherwise. This indicator represents the proportion of times the model correctly selects the optimal execution plan. The larger the value, the stronger the model's effectiveness in selecting candidate execution plans.

[0136] (3) Overall Runtime Ratio: Used to evaluate the end-to-end benefit of the cost model in query optimization tasks from a global perspective. Defined as the ratio of the total actual execution cost of the selected plan to the total actual execution cost of the true optimal plan, denoted as . The formula is:

[0137] The closer a value is to 1, the closer the overall runtime of the selected plan in the model is to the optimal execution plan.

[0138] The experimental results are shown in Tables 1-3, and Figures 5-7 As shown.

[0139] Table 1. Experimental results comparing q-error on the Synthetic and JOB-full datasets.

[0140] Table 2. Experimental results comparing q-error on the Scale and JOB-light datasets.

[0141] Table 3. Model efficiency analysis results

[0142] Experimental results show that the proposed scheme outperforms traditional cost models and existing learning methods across various query workloads and databases. This method more accurately characterizes the nonlinear mapping between query plans and actual execution costs, demonstrating strong modeling capabilities and generalization performance. Compared to comparative methods, this method exhibits greater stability in overall error distribution, particularly in high quantile error metrics, indicating more reliable estimations for extremely complex queries and high-risk execution plans. The two-stage progressive learning framework plays a crucial role in improving model performance. The structure-aware modeling stage effectively enhances the model's understanding of the internal structural dependencies and sub-plan hierarchy information of the query plan, while the plan quality-aware reinforcement stage introduces comparative learning between candidate execution plans, enabling the model to explicitly distinguish the cost differences brought about by different execution strategies, thereby significantly improving its discriminative ability in the candidate plan space. Furthermore, the CA-CNP module, by introducing a context-conditional modeling mechanism, improves the model's adaptability under limited observation conditions, maintaining high prediction accuracy even when facing cross-database scenarios and distribution changes. Overall, the method in this embodiment improves the accuracy and stability of cost estimation while maintaining inference efficiency. Experimental results show that the method of this invention has good application potential and practical value in real-world database query optimization tasks.

[0143] This embodiment addresses the problem that existing learning-based cost models primarily use training samples derived from the optimizer's actual execution plans, making it difficult to cover the candidate execution plan space. A two-stage progressive learning framework is designed. By introducing a candidate execution plan set and a plan quality-aware reinforcement mechanism in the second stage, the model can explicitly learn the cost differences between different execution strategies. This improves the model's cost estimation effectiveness in the candidate execution plan space and enhances its ability to identify high-risk, low-quality execution plans.

[0144] This embodiment addresses the problem of existing methods over-relying on specific database instance schema information and lacking cross-database generalization ability. It adopts a transferable feature representation method with operator type, optimizer cardinality estimation, and cost estimation as the core, which reduces the dependence on specific database schema semantics and statistical information forms. Therefore, it can better adapt to different database environments and improve the model's generalization ability in cross-database scenarios and cold start scenarios.

[0145] This embodiment introduces a structurally constrained attention mechanism to explicitly constrain the scope of information interaction between nodes in the query plan, ensuring that the attention calculation process remains consistent with the structural dependencies of the query plan itself. Compared to methods that only utilize general tree structure bias, this invention can more accurately model the relationship between the query plan structure and the actual execution cost, thereby improving the accuracy and stability of cost estimation.

[0146] This embodiment sets up a cost-aware context aggregation module. By conditionally modeling the sub-plan context information, it can make full use of the auxiliary information provided by the observed sub-plan costs, enhance the model's adaptability in scenarios with limited samples, complex queries, and changing distributions, thereby improving the stability and reliability of the prediction results.

[0147] This embodiment adopts a sub-plan parallel cost modeling mechanism, which can simultaneously complete the cost estimation of multiple sub-plans in a single forward propagation process. This not only improves the utilization efficiency of sub-plan level supervision signals, but also reduces the additional overhead caused by repeated calculations. Therefore, it can improve the efficiency of model training and inference while ensuring the accuracy of cost estimation, and better meet the practical application requirements of database query optimization for low latency and high efficiency.

[0148] Example 2 The purpose of this embodiment is to provide a query cost estimation system based on structure awareness and context modeling, including: The training set construction module is configured to: use the query plan tree as the basic modeling object, traverse and encode the query plan, extract the structural features and node semantic features of the query plan, and use the actual execution time as the supervision signal to construct the training set. The training module is configured to train the cost estimation model using a two-stage progressive learning approach based on the training set. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The cost estimation module is configured to use the trained cost estimation model to estimate the cost of all sub-plans.

[0149] In further embodiments, the following is also provided: An electronic device includes a memory and a processor, as well as computer instructions stored in the memory and running on the processor. When executed by the processor, the computer instructions perform the method described in Embodiment 1. For brevity, further details are omitted here.

[0150] It should be understood that in this embodiment, the processor can be a central processing unit (CPU), or it can be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor, etc.

[0151] Memory may include read-only memory and random access memory, and provides instructions and data to the processor. A portion of memory may also include non-volatile random access memory. For example, memory may also store information about the device type.

[0152] A computer-readable storage medium for storing computer instructions, which, when executed by a processor, perform the method described in Embodiment 1.

[0153] The method in Embodiment 1 can be directly implemented by a hardware processor, or implemented by a combination of hardware and software modules within the processor. The software modules can reside in readily available storage media in the art, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, or registers. This storage medium is located in memory; the processor reads information from the memory and, in conjunction with its hardware, completes the steps of the above method. To avoid repetition, a detailed description is not provided here.

[0154] A computer program product includes a computer program that, when executed by a processor, implements the method described in Embodiment 1.

[0155] The present invention also provides at least one computer program product tangibly stored on a non-transitory computer-readable storage medium. The computer program product includes computer-executable instructions, such as instructions included in program modules, which execute in a device on a target real or virtual processor to perform the processes / methods described above. Typically, program modules include routines, programs, libraries, objects, classes, components, data structures, etc., that perform specific tasks or implement specific abstract data types. In various embodiments, the functionality of program modules can be combined or divided among program modules as needed. The machine-executable instructions for the program modules can execute within a local or distributed device. In a distributed device, the program modules can reside in both local and remote storage media.

[0156] The computer program code used to implement the methods of the present invention may be written in one or more programming languages. This computer program code may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the computer or other programmable data processing device, the program code causes the functions / operations specified in the flowcharts and / or block diagrams to be implemented. The program code may be executed entirely on a computer, partially on a computer, as a stand-alone software package, partially on a computer and partially on a remote computer, or entirely on a remote computer or server.

[0157] In the context of this invention, computer program code or related data may be carried by any suitable carrier to enable a device, apparatus, or processor to perform the various processes and operations described above. Examples of carriers include signals, computer-readable media, and the like. Examples of signals may include electrical, optical, radio, sound, or other forms of propagation signals, such as carrier waves, infrared signals, etc.

[0158] Those skilled in the art will recognize that the units and algorithm steps described in conjunction with the embodiments herein can be implemented in electronic hardware or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0159] While the specific embodiments of the present invention have been described above in conjunction with the accompanying drawings, this is not intended to limit the scope of protection of the present invention. Those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solutions of the present invention are still within the scope of protection of the present invention.

Claims

1. A query cost estimation method based on structure awareness and context modeling, characterized in that, include: Using the query plan tree as the basic modeling object, the query plan is traversed and encoded to extract the structural features and node semantic features of the query plan. The actual execution time is used as the supervision signal to construct the training set. Based on the training set, a two-stage progressive learning approach is used to train the cost estimation model. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The cost estimation of all sub-plans is achieved using the trained cost estimation model.

2. The query cost estimation method based on structure awareness and context modeling as described in claim 1, characterized in that, Using the query plan tree as the basic modeling object, the query plan is traversed and encoded to extract its structural features and node semantic features. Specifically: Traverse the query plan tree and extract the plan structure dependency features of the query plan based on the execution order between nodes; Node-level semantic features, including operator type, cardinality estimation, and cost estimation, are extracted. Operator type is represented by one-hot encoding, and cardinality estimation and cost estimation are normalized by a robust scaling method based on median and interquartile range.

3. The query cost estimation method based on structure awareness and context modeling as described in claim 1, characterized in that, In the first stage of training the cost estimation model, a sub-plan hierarchical loss adjustment strategy is introduced, assigning different weights to the loss terms corresponding to different nodes, and determining the loss function for the first stage of training by combining the cost estimation errors of each sub-plan; wherein, the loss weight of a node is defined according to the normalized node height, and the node with a larger node height is assigned a lower loss weight; the node height is defined as the shortest path length from the node down to the leaf node.

4. The query cost estimation method based on structure awareness and context modeling as described in claim 1, characterized in that, The cost estimation model takes the node encoding sequence of the query plan as input, models the dependencies between nodes, then achieves deep fusion of node semantics and structural information based on the Transformer structure, then models global condition information, and jointly supervises the costs of sub-plans at different levels through a sub-plan parallel learning mechanism.

5. The query cost estimation method based on structure awareness and context modeling as described in claim 4, characterized in that, A plan structure constraint attention mechanism is introduced to model the dependencies between nodes, specifically: Based on the structural dependency matrix extracted from the query plan, an additive mask matrix is ​​generated; where, for any two distinct nodes... If and only if node It is a node The parent node, at this time Otherwise 0; In the self-attention calculation of Transformer, the additive mask matrix is ​​combined with the attention score matrix to make the attention weight of node pairs that do not satisfy the structural dependency relationship zero, thus obtaining the planned structural constraint attention output.

6. The query cost estimation method based on structure awareness and context modeling as described in claim 1, characterized in that, A cost-aware context aggregation module is introduced to model global conditional information, and a sub-plan parallel learning mechanism is used to jointly supervise the costs of sub-plans at different levels, specifically: For any target sub-plan, an attention-based context aggregation method is used to calculate the relevance weights between the target sub-plan and each sub-plan in the context, and the context encoding of the target sub-plan is weighted and summed using the relevance weights to obtain the context aggregation representation; The representation of the target sub-plan and its corresponding context are aggregated and then used to obtain the conditional prediction result of the target sub-plan cost through a probabilistic decoder. The conditional prediction results of the target sub-plan cost are mapped by a shared multilayer perceptron to generate cost estimates for all sub-plans in parallel.

7. The query cost estimation method based on structure awareness and context modeling as described in claim 1, characterized in that, In the second stage of model training, multiple candidate execution plans with differences in physical execution strategies are generated for the same query. The candidate execution plans are then actually executed to obtain the corresponding real cost information. After modeling the set of candidate execution plans, the context set and the target set are randomly divided. The loss function for the second stage is constructed by maximizing the conditional likelihood of the target variable under the given context conditions.

8. A query cost estimation system based on structure awareness and context modeling, characterized in that, include: The training set construction module is configured to: use the query plan tree as the basic modeling object, traverse and encode the query plan, extract the structural features and node semantic features of the query plan, and use the actual execution time as the supervision signal to construct the training set. The training module is configured to train the cost estimation model using a two-stage progressive learning approach based on the training set. In the first stage, query execution plans collected across databases are used as training samples, and joint supervised training is performed using the real costs of all sub-plans. In the second stage, based on the training in the first stage, multiple candidate execution plan sets are constructed for the same query, and contextual modeling is performed on the candidate plan sets to explicitly learn the cost differences between different execution strategies in order to update the model parameters. The cost estimation module is configured to use the trained cost estimation model to estimate the cost of all sub-plans.

9. An electronic device, characterized in that, It includes a memory and a processor, as well as computer instructions stored in the memory and running on the processor, which, when executed by the processor, perform the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, Used to store computer instructions, which, when executed by a processor, perform the method described in any one of claims 1-7.

Images