A query cost estimation method, device, medium and equipment

Abstract

This specification discloses a method for estimating query costs. The method obtains the query statement to be executed and identifies all created indexes. A query plan encoder encodes each execution node in the query plan tree corresponding to the query statement to be executed, obtaining node features for each execution node. An index encoder encodes all created indexes, obtaining index features. A fusion network fuses the node features and index features to obtain query plan execution features. Finally, a prediction network predicts the query cost based on the query plan features. This method, after encoding the node features of each execution node in the query plan tree and the index features of the created indexes, fuses them to obtain query plan execution features characterizing the execution of the query plan tree under the conditions of the created indexes. Finally, the prediction network predicts the query cost, enabling accurate estimation of the query cost.

Description

Technical Field

[0001] This specification relates to the field of computer technology, and in particular to a method, apparatus, medium and device for estimating query costs. Background Technology

[0002] Currently, database systems are widely used across various industries. Users can store data in the form of tables within a database system for easy data retrieval. When retrieving data, users can use query statements such as SQL to find the data they need within the database system.

[0003] To improve the efficiency of data retrieval, users can create one or more indexes on a database instance. However, the execution efficiency of query statements will vary depending on the indexes created. Therefore, to improve the execution efficiency of frequently executed queries, it is necessary to create appropriate indexes for these queries.

[0004] To evaluate whether a created index is suitable for frequently executed queries, it is necessary to estimate the query cost of those queries when executed with the created index. Therefore, accurately estimating the query cost of executing queries is a problem that urgently needs to be solved. Summary of the Invention

[0005] This specification provides a method, apparatus, storage medium, and electronic device for estimating query costs, in order to partially solve the problems existing in the prior art.

[0006] The embodiments in this specification adopt the following technical solutions: This specification provides a method for estimating query costs, the method comprising: Obtain the query statement to be executed and determine the database instance to be queried by the query statement to be executed; Based on the query statement to be executed, determine the query plan tree corresponding to the query statement to be executed; and determine each index that has been created on the database instance. The query plan tree is input into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder encodes the node features corresponding to each execution node based on each execution node in the query plan tree; and each index is input into the index encoder in the query cost estimation model, so that the index encoder encodes the index features of the database instance based on each index. The node features and index features corresponding to each execution node are input into the fusion network in the query cost estimation model, so as to fuse the node features and index features through the fusion network to obtain the query plan execution features of the query plan tree under the conditions of each index. The query plan execution characteristics are input into the prediction network in the query cost estimation model to predict the query cost of executing the query plan tree under the conditions of each index.

[0007] This specification provides a cost estimation device, the device comprising: The acquisition module is used to acquire the query statement to be executed and determine the database instance to be queried by the query statement to be executed; The determination module is used to determine the query plan tree corresponding to the query statement to be executed based on the query statement to be executed; and to determine each index that has been created on the database instance. The encoding module is used to input the query plan tree into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder can encode the node features corresponding to each execution node based on each execution node in the query plan tree; and input each index into the index encoder in the query cost estimation model, so that the index encoder can encode the index features of the database instance based on each index. The fusion module is used to input the node features corresponding to each execution node and the index features into the fusion network in the query cost estimation model, so as to fuse the node features and the index features through the fusion network to obtain the query plan execution features of the query plan tree under the conditions of each index; The prediction module is used to input the query plan execution characteristics into the prediction network in the query cost estimation model, so as to predict the query cost of executing the query plan tree under the conditions of each index through the prediction network.

[0008] This specification provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the above-described method for estimating query costs.

[0009] This specification provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described method for estimating query costs.

[0010] This specification provides a computer program product comprising a computer program that, when executed by a processor, implements the aforementioned method for estimating query costs.

[0011] The above-described at least one technical solution used in the embodiments of this specification can achieve the following beneficial effects: This specification discloses a method for estimating query costs. The method obtains the query statement to be executed and identifies all created indexes. A query plan encoder encodes each execution node in the query plan tree corresponding to the query statement to be executed, obtaining node features for each execution node. An index encoder encodes all created indexes, obtaining index features. A fusion network fuses the node features and index features to obtain query plan execution features. Finally, a prediction network predicts the query cost based on the query plan features. This method, after encoding the node features of each execution node in the query plan tree and the index features of the created indexes, fuses them to obtain query plan execution features characterizing the execution of the query plan tree under the conditions of the created indexes. Finally, the prediction network predicts the query cost, enabling accurate estimation of the query cost. Attached Figure Description

[0012] The accompanying drawings, which are included to provide a further understanding of this specification and form part of this specification, illustrate exemplary embodiments and are used to explain this specification, but do not constitute an undue limitation thereof. In the drawings: Figure 1 This is a schematic diagram of a query cost estimation method provided in the embodiments of this specification; Figure 2 This is a schematic diagram of the query plan tree provided in the embodiments of this specification; Figure 3 This is a schematic diagram of the query cost estimation model provided in the embodiments of this specification; Figure 4 This is a schematic diagram of the predicate tree structure provided in the embodiments of this specification; Figure 5 A schematic diagram of a query cost estimation device provided for embodiments of this specification; Figure 6 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this specification. Detailed Implementation

[0013] To make the objectives, technical solutions, and advantages of this specification clearer, the technical solutions of this specification will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this specification, and not all of them. Based on the embodiments in this specification, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this specification.

[0014] The technical solutions provided in the various embodiments of this specification are described in detail below with reference to the accompanying drawings.

[0015] Figure 1 A flowchart illustrating a query cost estimation method provided in this embodiment of the specification includes the following steps: S100: Obtain the query statement to be executed and determine the database instance to be queried by the query statement to be executed.

[0016] In the embodiments described in this specification, the following are employed: Figure 1 The device used to estimate query costs using the method shown can be any device, including but not limited to personal computers, mobile phones, tablets, servers, or server clusters consisting of multiple servers. This device may or may not host a database system, as long as it can access and query data within the database system; this specification does not impose any restrictions. For ease of description, the following explanation uses a server as an example.

[0017] In the embodiments described in this specification, the server may first obtain a query statement to be executed. This query statement is used to retrieve data from a database system, including but not limited to SQL statements. The following description uses only SQL statements as an example; those skilled in the art should understand that other types of query statements used to retrieve data from a database system are also applicable to the methods provided in this specification.

[0018] After the server obtains the query statement to be executed, it can determine the database instance that the query statement is to query in the database system based on the query statement.

[0019] In this specification, a database instance refers to a set of interconnected database files stored by a user within a database system. The database system primarily comprises a computation engine, a transaction engine, and a storage engine. The computation engine, including the query optimizer, parses SQL statements into corresponding query plan trees and sends them to the storage engine for execution. It then performs grouping, aggregation, and sorting calculations on the results returned by the storage engine and provides them to the user. The transaction engine updates the data and indexes stored in the storage engine in the form of transactions when SQL statements modify data within the database instance. The storage engine, in turn, stores the data and indexes.

[0020] Since this application only involves the estimation of query costs, that is, the query statement to be executed is only an SQL statement used to query data, not an SQL statement used to modify data, the method provided in this specification is basically unrelated to the transaction engine mentioned above.

[0021] S101: Based on the query statement to be executed, determine the query plan tree corresponding to the query statement to be executed; and determine each index that has been created on the database instance.

[0022] After obtaining the query statement to be executed, the server can parse the query statement into the corresponding query plan tree through the database system's computing engine. At the same time, it can also determine the indexes created on the database instance to be queried by the database system's storage engine.

[0023] Figure 2 This is a schematic diagram of the query plan tree provided in the embodiments of this specification. Figure 2 In this context, the query plan tree is a tree structure composed of several execution nodes, each representing an operation of a certain type. Figure 2 The meanings of the operators in each execution node are as follows: TABLE FULL SCAN indicates a table scan operation. Different TABLE FULL SCAN operations correspond to different tables or different partitions of the same table. PX PARTITION ITERATOR stands for Parallel Partition Iterator, which is used to perform scan operations in parallel on multiple partitions. It is usually used in conjunction with partitioned data tables, with each parallel process handling one partition. EXCHANGE IN DISTR / EXCHANGE OUT DISTR represent data exchange operations used to distribute or collect data between different parallel processes. EXCHANGE OUT DISTR means sending data out of the current process, while EXCHANGE IN DISTR means receiving data from other processes. (PKEY) indicates data distribution by partition key. HASH JOIN means using a hash join algorithm to join two datasets. It is usually executed in parallel, with each parallel process processing a portion of the data. MERGE GROUP BY indicates parallel execution of local aggregation, that is, the datasets processed in each parallel process are first locally aggregated according to the grouping key, and the results of local aggregation such as SUM (summation), AVG (average), and COUNT (count) are calculated. PX COORDINATOR indicates that it receives the local aggregation results of all parallel processes, and then merges and sorts them. SCALAR GROUP BY indicates global aggregate sorting. Figure 2 The query plan tree shown has undergone two aggregations: local aggregation within each parallel process of MERGEGROUP BY and global aggregation of SCALAR GROUP BY. LIMIT indicates a restriction on the final dataset, such as taking the TOP N or LIMIT N operation.

[0024] Parse the query statement to be executed into Figure 2 After the query plan tree is shown, it is sent to the database system's storage engine, which can then execute the query plan tree to retrieve the necessary queries for the pending query statement. However, estimating the query cost of the pending query statement would be significantly less efficient if the corresponding query plan tree were actually executed. Therefore, this specification subsequently employs a machine learning model to predict the query cost.

[0025] While determining the query plan tree, the server can also determine all indexes that have been created on the database instance through the database system's storage engine.

[0026] S102: Input the query plan tree into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder can encode the node features corresponding to each execution node based on each execution node in the query plan tree; and input each index into the index encoder in the query cost estimation model, so that the index encoder can encode the index features of the database instance based on each index.

[0027] In the embodiments described in this specification, the server predicts the query cost of the query statement to be executed using a pre-trained query cost estimation model. That is, the query cost required to execute the query statement under the condition of all currently created indexes. The query cost described in this specification is used to at least represent the execution efficiency of the query statement to be executed. A higher query cost corresponds to lower execution efficiency, and vice versa.

[0028] Because the query cost estimation model needs to predict the query cost of executing the query statement assuming all currently created indexes, it needs to encode the query plan tree and all currently created indexes on the database instance. Figure 3 As shown.

[0029] Figure 3 This is a schematic diagram of the query cost estimation model provided in the embodiments of this specification. The query cost estimation model includes a query plan encoder and an index encoder.

[0030] The server can input the query plan tree determined in step S101 into the query plan encoder, which can then encode the node features corresponding to each execution node based on the query plan tree. Alternatively, the server can input all currently created indexes on the database instance determined in step S101 into the index encoder, which can then encode the index features of the database instance based on each index.

[0031] Specifically, when encoding each execution node in the query plan tree using the query plan encoder, the encoder can determine whether the operation type corresponding to each execution node is a table scan type, that is, whether the operator of the execution node is... Figure 2 The table shown is TABLE FULL SCAN.

[0032] If so, since the query statement to be executed usually contains filtering conditions used for scanning data, such as a>1 and b<2 or c>3, the filtering conditions used by the execution node when performing the data table scan operation can be encoded through the query plan encoder to obtain the filtering characteristics of the execution node. The operation type corresponding to the execution node can be encoded to obtain the operation characteristics of the execution node. The height of the execution node in the query plan tree can be encoded to obtain the height characteristics of the execution node. Finally, based on the operation characteristics, filtering characteristics, and height characteristics of the execution node, the node characteristics corresponding to the execution node can be determined. Specifically, the operation characteristics, filtering characteristics, and height characteristics of the execution node can be directly concatenated to obtain the node characteristics corresponding to the execution node.

[0033] Otherwise, the query plan encoder can be used to encode only the operation type corresponding to the execution node to obtain the operation feature of the execution node, and the height of the execution node in the query plan tree can be encoded to obtain the height feature of the execution node. Finally, based on the operation feature and height feature of the execution node, the node feature corresponding to the execution node can be determined. Specifically, the operation feature and height feature of the execution node can be directly concatenated to obtain the node feature corresponding to the execution node.

[0034] The dimensions of the aforementioned filtering features, operation features, and height features can all be preset fixed dimensions. How to determine these features will be described later. It should be noted that, to ensure that the dimensions of the encoded node features are the same regardless of whether the operation type of the execution node is a data table scan, when the operation type of the execution node is not a data table scan, the default filtering feature can be a preset default filtering feature. This default filtering feature can specifically be a zero-dimensional vector of the aforementioned filtering features.

[0035] When encoding all currently created indexes in a database instance using an index encoder, specifically for each index created on that database instance, the encoding is performed based on the corresponding data table and the corresponding data column in that data table within the database instance, thus obtaining the table column feature corresponding to the index. This is because index creation requires at least one data column in at least one data table within the database instance. Therefore, a one-hot encoding method can be used, initially initializing the vector representing all data columns of all data tables in the database instance to 0. For example, assuming there are 6 data tables in the database instance, each containing 5 data columns, the dimension of this vector is 30, initialized to 30 zeros. Then, based on the corresponding data table and the corresponding data column in that data table, the element in the vector corresponding to that data column in the data table is set to 1, and this vector is used as the table column feature of the index.

[0036] On the other hand, the index can also be encoded based on the sorting of its corresponding data columns in the corresponding data table to obtain the index's sequential characteristics. This is because an index often corresponds to more than one data column, and these data columns have a specific order. For example, suppose an index is I(customer.c_id, c_name), where I represents the index, customer is the name of the data table corresponding to the index, and c_id and c_name are two data columns in the customer table. In this index, the data column c_id comes first, followed by the data column c_name. This means that if the data in the customer table is sorted according to this index, it is sorted first by c_id in ascending order. If c_id is the same, it is sorted by c_name in ascending order. Therefore, the sorting of different data columns corresponding to an index also determines the degree of data sharing when querying the index. Therefore, when encoding the sorting of the data columns corresponding to the index, the vector representing all data columns of all data tables in the database instance can still be initialized to 0 first. Then, according to the sorting of the different data columns corresponding to the index, the values ​​of the elements in the vector corresponding to these data columns can be set in ascending order. For example, in the above example, the value of the element corresponding to c_id in the vector can be set to 1 first, then the value of the element corresponding to c_name in the vector can be set to 2, and so on, to represent the sorting of the data columns corresponding to the index. The vector at this time is used as the order feature corresponding to the index.

[0037] Then, based on the table column features and sequence features corresponding to the index, the single index features corresponding to the index can be obtained. Specifically, the table column features and sequence features corresponding to the index can be directly concatenated to obtain the single index features. Finally, based on the single index features corresponding to each index already created on the database instance, the index features of the database instance can be obtained. Specifically, the single index features corresponding to each index can be concatenated, and then the concatenated features can be input into the fully connected layer of the index encoder to obtain the index features of the database instance with fixed dimensions.

[0038] S103: Input the node features and index features corresponding to each execution node into the fusion network in the query cost estimation model, so as to fuse the node features and index features through the fusion network to obtain the query plan execution features of the query plan tree under the conditions of each index.

[0039] In the embodiments described in this specification, such as Figure 3The query cost estimation model shown also includes a fusion network, which consists of a two-level transformer network. The first-level transformer network is used to fuse the node features of each execution node in the query plan tree obtained by the query plan encoder in step S102 into the global query plan feature of the query plan tree. The second-level transformer network is used to fuse the global query plan feature with the index feature obtained by the index encoder in step S102 into the query plan execution feature that represents the execution of the query plan tree under the conditions of the currently created indexes.

[0040] Specifically, the server first determines the distance between each pair of nodes in the query plan tree using a query plan encoder. This distance represents the topological distance between two execution nodes in the query plan tree. For example, when the edges between nodes in the query plan tree have no weight or all weights are equal, the shortest hop count between two nodes is the distance between them. The distance between each pair of nodes in the query plan tree can be determined using the Floyd-Warshall algorithm, or other algorithms; this specification does not impose any restrictions. Then, through a first-level transformer network, the first self-attention weights between execution nodes are determined based on the distances between them. Based on these first self-attention weights, the node features of each execution node are self-attention-weighted, and then the self-attention-weighted node features are fused to obtain the global query plan features of the query plan tree. The first self-attention weights between execution nodes are negatively correlated with the distances between them. That is, the node features of each execution node can be treated as a token. The tokens are then concatenated in the order of their connection in the query plan tree. Self-attention weighting is then applied to the concatenated features. The distance between each token can be used as the bias of the self-attention weight between them. The smaller the distance, the higher the self-attention weight. The features obtained after self-attention weighting are the features of each execution node after self-attention fusion, which is the global feature of the query plan of the query plan tree.

[0041] After obtaining the global features of the query plan, the global features of the query plan and the index features obtained by the index encoder in step S102 can be fused through the second-level transformer network, and the second self-attention weights between the fused features can be determined. Based on the second self-attention weights, the fused features are self-attention weighted to obtain the query plan execution features of the query plan tree under the conditions of the currently created indexes.

[0042] It should be noted that the dimension of the global query plan feature of the query plan tree obtained by the first-level transformer network is the same as the dimension of the index feature obtained by the index encoder in step S102. Therefore, when fusing the global query plan feature and the index feature, the global query plan feature and the index feature can be added together symmetrically to obtain the fused feature.

[0043] S104: Input the query plan execution features into the prediction network in the query cost estimation model, so as to predict the query cost of executing the query plan tree under the conditions of each index through the prediction network.

[0044] like Figure 3 As shown, the query cost estimation model described in this specification also includes a prediction network, which can be composed of a multilayer perceptron (MLP), and this specification does not impose any restrictions on this. After the server obtains the query plan execution features characterizing the execution of the query plan tree under the conditions of the currently created indexes through step S103, it can input the query plan execution features into the prediction network. The prediction network then predicts the query cost of executing the query plan tree under the conditions of the currently created indexes based on the input query plan execution features.

[0045] Once the query cost is predicted, the existing indexes can be adjusted accordingly, including adding new indexes, deleting existing indexes, and / or changing the order of data columns corresponding to one or more indexes.

[0046] Using the above method, after encoding the node features of each execution node in the query plan tree and the index features of the created indexes, the query plan execution features that characterize the execution of the query plan tree under the conditions of the created indexes can be fused together. Finally, the query cost can be accurately estimated by predicting the query cost through the prediction network.

[0047] Furthermore, in Figure 1 In step S102, when encoding node features for each execution node in the query plan tree, for each execution node, when the operation type corresponding to that execution node is a data table scan type, when determining the filtering features of that execution node, the query plan encoder can first determine the predicate tree corresponding to the filtering condition based on the filtering condition used when that execution node performs the data table scan operation. This predicate tree is a binary tree, with atomic predicates as leaf nodes and logical operators as non-leaf nodes, such as... Figure 4 As shown.

[0048] Figure 4 This is a schematic diagram of the predicate tree structure provided in the embodiments of this specification. Figure 4This explanation uses the filter condition a>1 and b<2 or c>3 as an example. When determining the predicate tree, the query plan encoder first identifies all atomic predicates and logical operators between them contained in the filter conditions used by the execution node. An atomic predicate is a filter condition that cannot be further subdivided; for example, a>1, b<2, c>3 are three filter conditions that cannot be further subdivided, thus constituting an atomic predicate. Logical operators include AND, OR, and other logical operators.

[0049] Get as Figure 4 Following the predicate tree shown, the query plan encoder can encode the atomic predicate corresponding to each leaf node in the predicate tree, obtaining the predicate feature corresponding to the atomic predicate, which serves as the node feature corresponding to the leaf node. For each non-leaf node in the predicate tree, the logical operator corresponding to the non-leaf node is used as the target logical operator. A preset fusion algorithm corresponding to the target logical operator is used to fuse the node features corresponding to the child nodes of the non-leaf node, and the resulting fused feature serves as the node feature corresponding to the non-leaf node. Finally, the node feature corresponding to the root node of the predicate tree is determined as the filtering feature of the execution node.

[0050] Specifically, when encoding the atomic predicate corresponding to a leaf node, the string corresponding to the atomic predicate can be text-encoded to obtain its text features. For example, various text encoding methods such as word2vec and skip-gram can be used to determine the text features of the string corresponding to the atomic predicate. Simultaneously, each character in the string corresponding to the atomic predicate can be hashed using bitmap encoding to obtain its bitmap features. For example, a vector of dimension n can be constructed, and each character in the string corresponding to the atomic predicate can be modulo n. The element corresponding to the resulting modulo value in the vector of dimension n is set to 1, while other elements remain 0, serving as the bitmap features of the atomic predicate. Finally, based on the text features and bitmap features of the atomic predicate, the predicate features corresponding to the atomic predicate are determined. Specifically, the text features and bitmap features of the atomic predicate can be concatenated to obtain the predicate features.

[0051] For each non-leaf node in the predicate tree, when fusing the node features corresponding to the child nodes of the non-leaf node using a predefined fusion algorithm corresponding to the target logical operator of that non-leaf node, if the target logical operator is an AND operator, then a minimum pooling method is used to fuse the node features corresponding to the child nodes of the non-leaf node; that is, the node features corresponding to the left and right child nodes of the non-leaf node are minimum pooled and used as the node features of the non-leaf node. If the target logical operator is an OR operator, then a maximum pooling method is used to fuse the node features corresponding to the child nodes of the non-leaf node; that is, the node features corresponding to the left and right child nodes of the non-leaf node are maximum pooled and used as the node features of the non-leaf node. This process continues until the node features of the child nodes are determined are... Figure 4 When considering the node features corresponding to the root node of the predicate tree shown, the node features corresponding to the root node are determined as the filtering features of the execution node in the query plan tree.

[0052] When determining the operational characteristics of an execution node in the query plan tree, the strings corresponding to the operators of that execution node in the query plan tree can be encoded to obtain the text features of the strings corresponding to the operators of that execution node, which can be used as the operational characteristics of that execution node. For example, for the execution node TABLE FULL SCAN, the text features corresponding to its operator TABLE FULL SCAN can be directly encoded as the operational characteristics of that execution node.

[0053] When determining the height feature of an execution node in a query plan tree, the longest path length from that execution node to a leaf node in the query plan tree can be determined as the height feature of that execution node.

[0054] For execution nodes whose operation type is a table scan, the node's filter feature, operation feature, and height feature can be concatenated to obtain the node's node feature. For execution nodes whose operation type is not a table scan, the default filter feature (i.e., a fixed-dimensional 0 vector), the node's operation feature, and height feature can be concatenated to obtain the node's node feature. Query cost can then be predicted using steps S103-S104.

[0055] Furthermore, in the embodiments described in this specification, the training of the above-mentioned... Figure 3 The query cost estimation model shown can be trained using supervised learning methods. Specifically, sample query statements can be obtained in advance, and the sample database instance to be queried by the sample query statement can be determined, and the above-mentioned methods can be applied. Figure 1The method described predicts the query cost of a sample query statement using a query cost estimation model to be trained. Specifically, based on the sample query statement, a sample query plan tree corresponding to the sample query statement is determined, along with the sample indexes already created on the sample database instance. Then, the node features of each execution node in the sample query plan tree are encoded using the query plan encoder in the query cost estimation model to be trained, and the index features of the sample database instance are encoded using the index encoder in the same model. Next, the node features of each execution node in the sample query plan tree and the index features of the sample database instance are fused using the fusion network in the model to obtain the sample query plan execution features. Finally, these execution features are input into the prediction network in the query cost estimation model to obtain the predicted query cost. Then, with the training objective of reducing the difference between the predicted query cost and the labeled query cost corresponding to the sample query statement, the model parameters of at least one of the query plan encoder, index encoder, fusion network, and prediction network in the query cost estimation model to be trained are adjusted.

[0056] Specifically, the difference between the predicted query cost and the labeled query cost corresponding to the sample query can be expressed using the mean squared error. To characterize, among which, This represents the query statement for the i-th sample. This represents the labeled query cost of the i-th sample query statement. This represents the predicted query cost for the i-th sample query statement.

[0057] It should be noted that the equipment and methods used to train the query cost estimation model described above... Figure 1 The method shown can be used to estimate the query cost of the query statement to be executed using the same device or different devices; this specification does not impose any restrictions on this.

[0058] The above is an example of a method for estimating query costs provided in this specification. Based on the same idea, this specification also provides corresponding devices, such as... Figure 5 As shown.

[0059] Figure 5 This is a schematic diagram of a cost estimation device provided in an embodiment of this specification. The device includes: The acquisition module 500 is used to acquire the query statement to be executed and determine the database instance to be queried by the query statement to be executed; The determination module 501 is used to determine the query plan tree corresponding to the query statement to be executed based on the query statement to be executed; and to determine each index that has been created on the database instance. The encoding module 502 is used to input the query plan tree into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder can encode the node features corresponding to each execution node based on each execution node in the query plan tree; and input each index into the index encoder in the query cost estimation model, so that the index encoder can encode the index features of the database instance based on each index. The fusion module 503 is used to input the node features and index features corresponding to each execution node into the fusion network in the query cost estimation model, so as to fuse the node features and index features through the fusion network to obtain the query plan execution features of executing the query plan tree under the conditions of each index; The prediction module 504 is used to input the query plan execution features into the prediction network in the query cost estimation model, so as to predict the query cost of executing the query plan tree under the conditions of each index through the prediction network.

[0060] Optionally, the encoding module 502 is specifically used to, for each execution node, determine whether the operation type corresponding to the execution node is a data table scan type; if so, encode the operation type corresponding to the execution node through the query plan encoder to obtain the operation characteristics of the execution node, encode the filtering conditions used by the execution node when performing the data table scan operation to obtain the filtering characteristics of the execution node, encode the height of the execution node in the query plan tree to obtain the height characteristics of the execution node, and determine the node characteristics corresponding to the execution node based on the operation characteristics, filtering characteristics, and height characteristics; otherwise, encode the operation type corresponding to the execution node through the query plan encoder to obtain the operation characteristics of the execution node, encode the height of the execution node in the query plan tree to obtain the height characteristics of the execution node, and determine the node characteristics corresponding to the execution node based on the operation characteristics and height characteristics.

[0061] Optionally, the encoding module 502 is specifically used to: determine the predicate tree corresponding to the filtering conditions used when the execution node performs a data table scan operation; the predicate tree has atomic predicates as leaf nodes and logical operators as non-leaf nodes; for each leaf node in the predicate tree, the atomic predicate corresponding to the leaf node is encoded to obtain the predicate feature corresponding to the atomic predicate, which is used as the node feature corresponding to the leaf node; for each non-leaf node in the predicate tree, the logical operator corresponding to the non-leaf node is used as the target logical operator, and a preset fusion algorithm corresponding to the target logical operator is used to fuse the node features corresponding to the child nodes of the non-leaf node, and the obtained fused feature is used as the node feature corresponding to the non-leaf node; the node feature corresponding to the root node of the predicate tree is determined as the filtering feature of the execution node.

[0062] Optionally, the encoding module 502 is specifically used to: perform text encoding on the string corresponding to the atomic predicate to obtain the text feature corresponding to the atomic predicate; and perform hash bitmap encoding on each character in the string corresponding to the atomic predicate to obtain the bitmap feature corresponding to the atomic predicate; and determine the predicate feature corresponding to the atomic predicate based on the text feature corresponding to the atomic predicate and the bitmap feature corresponding to the atomic predicate.

[0063] Optionally, the encoding module 502 is specifically used to: when the target logical operator is an AND operator, use a minimum pooling method to fuse the node features corresponding to the child nodes of the non-leaf node; when the target logical operator is an OR operator, use a maximum pooling method to fuse the node features corresponding to the child nodes of the non-leaf node.

[0064] Optionally, the encoding module 502 is specifically configured to: for each index already created on the database instance, encode the index according to the data table corresponding to the index in the database instance and the data column corresponding to the index in the data table to obtain the table column features corresponding to the index; encode the index according to the sorting of the data columns corresponding to the index in the data table to obtain the order features corresponding to the index; obtain the single index features corresponding to the index according to the table column features and order features corresponding to the index; and obtain the index features of the database instance according to the single index features corresponding to each index already created on the database instance.

[0065] Optionally, the fusion module 503 is specifically used to: determine the distance between each execution node in the query plan tree; determine the first self-attention weight between each execution node based on the distance between each execution node through the first-level transformer network in the fusion network, and perform self-attention weighting on the node features of each execution node based on the first self-attention weight, and fuse the node features of each execution node after self-attention weighting to obtain the global query plan features of the query plan tree; wherein, the first self-attention weight between each execution node is negatively correlated with the distance between each execution node; fuse the global query plan features and the index features through the second-level transformer network in the fusion network, and determine the second self-attention weight between the fused features, and perform self-attention weighting on the fused features based on the second self-attention weight to obtain the query plan execution features of the query plan tree executed under the conditions of each index.

[0066] Optionally, the device further includes: Training module 505 is used to acquire sample query statements and determine the sample database instance to be queried by the sample query statements; determine the sample query plan tree corresponding to the sample query statements based on the sample query statements; and determine each sample index created on the sample database instance; encode the node features of each execution node in the sample query plan tree through the query plan encoder in the query cost estimation model to be trained, and encode the index features of the sample database instance through the index encoder in the query cost estimation model to be trained; fuse the node features of each execution node in the sample query plan tree and the index features of the sample database instance through the fusion network in the query cost estimation model to be trained to obtain sample query plan execution features; input the sample query plan execution features into the prediction network in the query cost estimation model to be trained to obtain the predicted query cost; and adjust the model parameters of at least one of the query plan encoder, index encoder, fusion network, and prediction network with the training objective of reducing the difference between the predicted query cost and the labeled query cost corresponding to the sample query statement.

[0067] This specification also provides a computer-readable storage medium storing a computer program that, when executed by a processor, can be used to perform the query cost estimation method provided above.

[0068] This specification also provides a computer program product comprising a computer program that, when executed by a processor, implements the above-described method for estimating query costs.

[0069] based on Figure 1 The method for estimating query costs shown in this specification is further provided in the embodiments. Figure 6 The diagram shows the structure of the electronic device. Figure 6 At the hardware level, the electronic device includes a processor, internal bus, network interface, memory, and non-volatile storage, and may also include other hardware required for business operations. The processor reads the corresponding computer program from the non-volatile storage into memory and then runs it to implement the above-mentioned method for estimating query costs.

[0070] The above description is merely an embodiment of this specification and is not intended to limit this specification. Various modifications and variations can be made to this specification by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this specification should be included within the scope of the claims of this specification.

Claims

1. A method for estimating query costs, the method comprising: Obtain the query statement to be executed and determine the database instance to be queried by the query statement to be executed; Based on the query statement to be executed, determine the query plan tree corresponding to the query statement to be executed; And, identify each index that has been created on the database instance; The query plan tree is input into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder can encode the node features corresponding to each execution node based on each execution node in the query plan tree. Furthermore, each index is input into the index encoder in the query cost estimation model, so that the index encoder can encode the index features of the database instance based on each index. The node features and index features corresponding to each execution node are input into the fusion network in the query cost estimation model, so as to fuse the node features and index features through the fusion network to obtain the query plan execution features of the query plan tree under the conditions of each index. The query plan execution characteristics are input into the prediction network in the query cost estimation model to predict the query cost of executing the query plan tree under the conditions of each index.

2. The method as described in claim 1, wherein the query plan encoder encodes the node features corresponding to each execution node based on each execution node in the query plan tree, specifically including: For each execution node, determine whether the operation type corresponding to that execution node is a data table scan type; If so, the operation type corresponding to the execution node is encoded by the query plan encoder to obtain the operation characteristics of the execution node; the filtering conditions used by the execution node when performing the data table scan operation are encoded to obtain the filtering characteristics of the execution node; the height of the execution node in the query plan tree is encoded to obtain the height characteristics of the execution node; and the node characteristics corresponding to the execution node are determined based on the operation characteristics, filtering characteristics, and height characteristics. Otherwise, the operation type corresponding to the execution node is encoded by the query plan encoder to obtain the operation feature of the execution node, the height of the execution node in the query plan tree is encoded to obtain the height feature of the execution node, and the node feature corresponding to the execution node is determined based on the operation feature and the height feature.

3. The method as described in claim 2, wherein the filtering conditions used by the execution node when performing a data table scan operation are encoded to obtain the filtering features of the execution node, specifically including: The predicate tree corresponding to the filtering conditions is determined based on the filtering conditions used when the execution node performs the data table scan operation; The predicate tree uses atomic predicates as leaf nodes and logical operators as non-leaf nodes; For each leaf node in the predicate tree, the atomic predicate corresponding to the leaf node is encoded to obtain the predicate feature corresponding to the atomic predicate, which is used as the node feature corresponding to the leaf node. For each non-leaf node in the predicate tree, the logical operator corresponding to the non-leaf node is taken as the target logical operator. A preset fusion algorithm corresponding to the target logical operator is used to fuse the node features corresponding to the child nodes of the non-leaf node. The fused features are then used as the node features corresponding to the non-leaf node. The node features corresponding to the root node of the predicate tree are determined as the filtering features of the execution node.

4. The method as described in claim 3, encoding the atomic predicate corresponding to the leaf node to obtain the predicate feature corresponding to the atomic predicate, specifically includes: Text encoding is performed on the string corresponding to the atomic predicate to obtain the text feature corresponding to the atomic predicate; and each character in the string corresponding to the atomic predicate is hashed and bitmap encoded to obtain the bitmap feature corresponding to the atomic predicate. Based on the text features and bitmap features corresponding to the atomic predicate, the predicate features corresponding to the atomic predicate are determined.

5. The method as described in claim 3, wherein a preset fusion algorithm corresponding to the target logical operator is used to fuse the node features corresponding to the child nodes of the non-leaf node, specifically including: When the target logical operator is an AND operator, a minimum pooling method is used to fuse the node features corresponding to the child nodes of the non-leaf node; When the target logical operator is an OR operator, the max pooling method is used to fuse the node features corresponding to the child nodes of the non-leaf node.

6. The method as described in claim 1, wherein the index encoder encodes the index features of the database instance based on each index, specifically including: For each index created on the database instance, the index is encoded according to the data table corresponding to the index in the database instance and the data column corresponding to the index in the data table to obtain the table column characteristics corresponding to the index. Furthermore, the index is encoded according to the sorting of the data columns corresponding to the index in the data table to obtain the order characteristics corresponding to the index. Based on the table column characteristics and order characteristics corresponding to the index, the single index characteristics corresponding to the index are obtained; The index characteristics of the database instance are obtained based on the single index characteristics corresponding to each index already created on the database instance.

7. The method as described in claim 1, wherein the query plan execution features for executing the query plan tree under the conditions of the respective indexes are obtained by fusing the node features and the index features through the fusion network, specifically including: Determine the distance between each execution node in the query plan tree; Through the first-level transformer network in the fusion network, the first self-attention weight between each execution node is determined based on the distance between each execution node. Then, based on the first self-attention weight between each execution node, the node features of each execution node are self-attention weighted, and the node features of each execution node after self-attention weighting are fused to obtain the global features of the query plan tree. Among them, the first self-attention weight between each execution node is negatively correlated with the distance between each execution node. The query plan global features and index features are fused through the second-level transformer network in the fusion network, and a second self-attention weight is determined between the fused features. Based on the second self-attention weight, the fused features are self-attention weighted to obtain the query plan execution features for executing the query plan tree under the conditions of each index.

8. The method as described in any one of claims 1 to 7, wherein the query cost estimation model is pre-trained, specifically comprising: Obtain the sample query statement and determine the sample database instance to be queried by the sample query statement; Based on the sample query statement, determine the sample query plan tree corresponding to the sample query statement; And, determine each sample index that has been created on the sample database instance; The node features of each execution node in the sample query plan tree are obtained by encoding the query plan encoder in the query cost estimation model to be trained, and the index features of the sample database instance are obtained by encoding the index encoder in the query cost estimation model to be trained. The node features of each execution node in the sample query plan tree and the index features of the sample database instance are fused together by the fusion network in the query cost estimation model to be trained to obtain the sample query plan execution features. The sample query plan execution features are input into the prediction network of the query cost estimation model to be trained to obtain the predicted query cost; With the training objective of reducing the difference between the predicted query cost and the labeled query cost corresponding to the sample query statement, the model parameters of at least one of the query plan encoder, index encoder, fusion network and prediction network are adjusted.

9. A query cost estimation apparatus, the apparatus comprising: The acquisition module is used to acquire the query statement to be executed and determine the database instance to be queried by the query statement to be executed; The determination module is used to determine the query plan tree corresponding to the query statement to be executed based on the query statement to be executed; And, identify each index that has been created on the database instance; The encoding module is used to input the query plan tree into the query plan encoder in the pre-trained query cost estimation model, so that the query plan encoder can encode the node features corresponding to each execution node based on each execution node in the query plan tree. Furthermore, each index is input into the index encoder in the query cost estimation model, so that the index encoder can encode the index features of the database instance based on each index. The fusion module is used to input the node features corresponding to each execution node and the index features into the fusion network in the query cost estimation model, so as to fuse the node features and the index features through the fusion network to obtain the query plan execution features of the query plan tree under the conditions of each index; The prediction module is used to input the query plan execution characteristics into the prediction network in the query cost estimation model, so as to predict the query cost of executing the query plan tree under the conditions of each index through the prediction network.

10. A computer-readable storage medium storing a computer program that, when executed by a processor, implements the method described in any one of claims 1-8.

11. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in any one of claims 1-8.

12. A computer program product comprising a computer program that, when executed by a processor, implements the method described in any one of claims 1-8.

Images