Method and device for database management system query planning

By generating and evaluating the probability cost value pairs of multiple query plans and selecting plans with high probability and low cost, the performance problems caused by cost estimator uncertainty in DBMS are solved, and the credibility and applicability of query plans are improved.

CN119072686APending Publication Date: 2024-12-03HUAWEI CLOUD COMPUTING TECHNOLOGIES CO LTD +1
View PDF 5 Cites 0 Cited by

Patent Information

Application Number
CN202380023780.9
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Priority Date
2022-02-25
Filing Date
2023-02-25
Publication Date
2024-12-03

AI Technical Summary

Technical Problem

There is uncertainty in existing database management system (DBMS) cost estimators, resulting in inaccurate planning selection, which can lead to performance degradation, and existing solutions such as robust query optimizers make assumptions about selective distributions, which are only applicable to limited Scene.

Method used

By generating multiple query plans and calculating the probability cost value pairs of each plan using statistical methods, evaluating the matching of their estimated upper limit cost and actual cost, a low-cost plan with high probability is selected to execute the query.

Benefits of technology

Reduces uncertainty, improves the credibility and performance of plan choices, applies to a wider range of scenarios, and no longer depends on specific selective distribution assumptions.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119072686A_ABST
    Figure CN119072686A_ABST
Patent Text Reader

Abstract

The invention provides a method and apparatus for calibrating a cost estimator for trusted DBMS performance. One aspect of the present invention provides a method that includes generating a plurality of plans for potential execution by a DBMS, each of the plurality of plans for generating a same response to a same specified database query. The method further includes generating, for each plan, a set of pairs of probabilistic cost values, each pair of probabilistic cost values indicating, if and when the plan is executed, an estimated upper cost of the plan and a probability that the cost of the plan is equal to or less than the estimated upper cost. The method further includes selecting a plan of the plurality of plans based at least in part on the evaluation of the set of probabilistic cost values for the plurality of plans, and causing the DBMS to execute the selected plan.
Need to check novelty before this filing date? Find Prior Art

Description

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application claims priority to and the benefit of prior application U.S. patent application No. 17 / 681,232, filed on February 25, 2022, the contents of which are incorporated herein by reference. Technical Field

[0003] The present invention relates to the field of database management systems (DBMS), and in particular to a method and device for planning how to execute queries in a DBMS. Background Art

[0004] DBMS architectures are widely used for organizing, retrieving, and analyzing data. Users interact with the DBMS through queries, and the DBMS may use cost estimators, among other components, to identify plans for obtaining query results. Existing cost estimators suffer from uncertainties, which may be related to the possible difference between the estimated cost of a plan and the actual cost (e.g., runtime) of the plan. These uncertainties may lead to suboptimal or even catastrophic plan selection.

[0005] Robust query optimizers are an existing solution that aims to quantify the "predictability" of cardinality estimates and strike a balance between performance and predictability during plan selection. Robust query optimizers are described by Babcock, Brian, and Surajit Chaudhuri in "Towards a robust query optimizer: a principled and practical approach" (see Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data. 2005.). Robust query optimizers involve maintaining random tuples of tables in a DBMS and running a given query on these random tuples. The technique also involves using Bayes' rule to calculate a probability distribution of the actual selectivity of the query based on the selectivity observed from the random tuples. The user can then specify a threshold α in the range [0, 1] and use the αth quantile of the selectivity distribution of the query (e.g., the 30% quantile) as the estimated selectivity of the query. The cost of each plan is then calculated based on the selectivity estimate corresponding to the αth quantile. The plan with the lowest estimated cost is used as the execution plan.

[0006] Although some cost estimators (such as the Robust Query Optimizer) try to address the uncertainty problem, existing solutions still have their limitations. For example, these cost estimators make assumptions about the selectivity distribution (the probability distribution of predicate selectivity, see the paper cited above), such as following a uniform distribution or a beta distribution, or require knowledge of the actual selectivity distribution. However, these solutions may only work in limited scenarios and may also be limited to a certain type of cost estimator.

[0007] Therefore, a method is needed that obviates or reduces one or more limitations of the prior art.

[0008] The purpose of the background art is to disclose information that the applicant believes may be relevant to the present invention. It is not necessary to admit, nor should it be construed, that any of the above information constitutes prior art against the present invention. Summary of the invention

[0009] The present invention provides methods and apparatus for DBMS query planning and associated cost estimation. Multiple plans executed by a DBMS to execute a given query can be compared to each other based on a statistical method that returns an estimated cost and a corresponding probability that the actual cost is less than or equal to one of the estimated costs. The cost can be based in part or in whole on the amount of time required to execute the plan. A plan associated with an expected estimated cost and probability can be selected for execution to execute the query.

[0010] A first aspect of the present invention provides a method. The method includes: generating multiple plans for potential execution by a DBMS. When executed by the DBMS, each of the multiple plans is used to generate the same response to the same specified database query (in terms of the final query result). The method also includes: generating a set of probability cost value pairs for each of the multiple plans. If and when the plan is executed, each probability cost value pair indicates the estimated upper limit cost of its associated plan, and the probability that the cost of the plan is equal to or less than the estimated upper limit cost. The method also includes: selecting one of the multiple plans based at least in part on an evaluation of the set of probability cost value pairs of the multiple plans. The method may also include: causing the DBMS to execute the selected one of the multiple plans.

[0011] In some embodiments of the first aspect, the cost of the plan is the amount of time the DBMS takes to execute the plan. In this case, the estimated upper bound cost of the plan is the estimated maximum amount of time the DBMS takes to execute the plan.

[0012] In some embodiments of the first aspect, generating the set of probability-cost-value pairs includes generating, for each of the plurality of plans, a respective estimated cost of the plan.

[0013] In some embodiments of the first aspect, generating the set of probability-cost value pairs further comprises: for each of the multiple plans, and for each probability value in the probability value set, providing the probability value and the plan to an estimation module. In some embodiments of the first aspect, generating the set of probability-cost value pairs further comprises: for each of the multiple plans, and for each probability value in the probability value set, subsequently receiving a cost value from the estimation module such that when the probability is equal to the probability value, the expected cost generated by the plan is less than or equal to the cost value. In some embodiments of the first aspect, generating the set of probability-cost value pairs further comprises: for each of the multiple plans, and for each probability value in the probability value set, setting a probability-cost value pair in the set of probability-cost value pairs to pair the cost value with the probability value.

[0014] In some embodiments of the first aspect, the estimation module operates based on a conformal technique.

[0015] In some embodiments of the first aspect, the conformal technique is pre-initialized based on a plurality of examples. Each example may indicate a DBMS query plan, an estimated cost of the DBMS query plan, and an actual cost incurred by executing the DBMS query plan.

[0016] In some embodiments of the first aspect, the selecting one of the plurality of plans may also be based on user-generated preferences.

[0017] In some embodiments of the first aspect, the selecting one of the multiple plans may include: presenting an output to a user, and receiving an input from the user, the input being responsive to the output. The output may indicate the set of probability cost value pairs, each of the probability cost value pairs indexing to a corresponding one of the multiple plans. In some embodiments of the first aspect, the selecting one of the multiple plans may also be based on a user-selected plan from the multiple plans. In some embodiments of the first aspect, the selecting one of the multiple plans may also include: selecting the user-selected plan from the multiple plans.

[0018] In some embodiments of the first aspect, selecting one of the multiple plans may also include: selecting one of the multiple plans, wherein the probability that the cost of the plan is less than or equal to the indicated maximum acceptable single query cost is the highest.

[0019] In some embodiments of the first aspect, the maximum acceptable single query cost may be based on user-generated preferences.

[0020] In some embodiments of the first aspect, the method further comprises: operating in a first mode or a second mode according to the instruction. The first mode is characterized by a higher certainty in cost rather than a lower cost. The second mode is characterized by a lower cost rather than a higher certainty in cost. In some embodiments of the first aspect, in the first mode, selecting one of the plurality of plans may include: selecting a probability level greater than 50%. In some embodiments of the first aspect, in the first mode, selecting one of the plurality of plans may further comprise: comparing the plurality of plans based on the estimated upper limit costs of the plurality of plans. The estimated upper limit cost of each plan may be paired with a selected probability level. In some embodiments of the first aspect, in the first mode, selecting one of the plurality of plans may further comprise: selecting one of the plurality of plans, wherein the plan has a paired lowest estimated upper limit cost. The estimated upper limit cost of each plan may be paired with a selected probability level. In some embodiments of the first aspect, in the second mode, selecting one of the plurality of plans may include: selecting one of the plurality of plans, wherein the plan has a lowest estimated cost.

[0021] In some embodiments of the first aspect, the indication is obtained from user-generated preferences.

[0022] In some embodiments of the first aspect, the selecting one of the multiple plans may include: selecting a probability level greater than 50%. In some embodiments of the first aspect, the selecting one of the multiple plans may also include: comparing the multiple plans based on the estimated upper limit costs of the multiple plans. The estimated upper limit cost of each plan may be paired with the selected probability level. In some embodiments of the first aspect, the selecting one of the multiple plans may also include: selecting a plan among the multiple plans, wherein the plan has the lowest estimated upper limit cost paired with the selected probability level.

[0023] In some embodiments of the first aspect, the selected probability level is greater than one of: 60%, 70%, 80%, 90%, 95% and 99%.

[0024] In some embodiments of the first aspect, the lowest estimated cost may be generated by comparing the costs of the plurality of plans.The cost of each plan may be generated independently of the set of probability-cost-value pairs.

[0025] According to a second aspect of the present invention, another method is provided. The method can be executed by a computer. The method includes: for each of a plurality of specified database queries, generating a plurality of plans for potential execution by a DBMS. When executed by the DBMS, each of the plurality of plans is used to generate the same response (in terms of the final query result) to the same specified database in the plurality of specified database queries. The method also includes: for each of the plurality of specified database queries, generating a set of probability cost value pairs for each of the plurality of plans. If and when the plan is executed, each probability cost value pair indicates the estimated upper limit cost of its associated plan, and the probability that the cost of the plan is equal to or less than the estimated upper limit cost. The method also includes: for each of the plurality of specified database queries, then making the following selection: one or more of the plurality of specified database queries. The method also includes: for each of the plurality of specified database queries selected, making the following selection: the corresponding plan in the plurality of plans that executes the selected specified query in the plurality of database queries. The selection is based at least in part on the evaluation of the plurality of plans and the set of probability cost value pairs for the plurality of specified database queries. The method also includes causing the DBMS to execute each selected one of the plurality of database queries using the corresponding one of the plurality of plans.

[0026] In some embodiments of the second aspect, the selection may also be based on an indication of a cost cap threshold. In some embodiments of the second aspect, the plan selection may also be based on an indication that as many of the multiple specified database queries as possible are to be completed while complying with the cost cap threshold. In some embodiments of the second aspect, the selection comprises: selecting one or more of the multiple specified database queries. In some embodiments of the second aspect, the selection further comprises: for each selected specified database query of the multiple specified database queries, selecting the corresponding plan of the multiple plans that executes the selected one or more of the multiple database queries. The corresponding plan is selected so that the selection substantially maximizes the combination of: the number of the multiple specified database queries that can be completed with a probability of meeting or exceeding a specified probability level while complying with the cost cap threshold; and the specified probability level.

[0027] In some embodiments of the second aspect, the selection may also be based on an indication of a cost cap threshold. The selection may also be based on an indication that at least a specified portion of the multiple specified database queries are to be completed while complying with the cost cap threshold. In some embodiments of the second aspect, the selection comprises: selecting one or more specified database queries from the multiple specified database queries. In some embodiments of the second aspect, the selection further comprises: for each selected specified database query from the multiple specified database queries, selecting the corresponding plan from the multiple plans for executing the selected one of the multiple database queries. The selection of the corresponding plan enables the selection to substantially maximize the probability of completing at least the specified portion of the multiple specified database queries while complying with the cost cap threshold.

[0028] A third aspect of the present invention may provide an apparatus comprising a module for executing the method according to one or more aspects described herein.

[0029] In a fourth aspect, a device is provided, wherein the device comprises: a memory for storing a program; and a processor for executing the program stored in the memory, and when the program stored in the memory is executed, the processor is used to execute the method according to one or more aspects described herein.

[0030] In a fifth aspect, a computer-readable medium is provided, wherein the computer-readable medium stores a program code executed by a device, wherein the program code is used to execute the method described in one or more aspects described herein.

[0031] In a sixth aspect, a chip is provided, wherein the chip includes a processor and a data interface, and the processor reads instructions stored in a memory through the data interface to execute the method described in one or more aspects described herein.

[0032] Other aspects of the present invention provide apparatus and systems for executing the methods described in the first aspect and other aspects disclosed herein. For example, a wireless station and an access point may be configured with a machine-readable memory containing instructions, and when the machine-readable memory is executed by a processor of these devices, the device executes the method described in one or more aspects described herein.

[0033] Embodiments are described above in conjunction with various aspects of the present invention, and these embodiments can be implemented based on these aspects. It will be appreciated by those skilled in the art that embodiments can be implemented in conjunction with the aspects describing these embodiments, but can also be implemented together with other embodiments of this aspect. When the embodiments are mutually exclusive or incompatible with each other, this is obvious to those skilled in the art. Some embodiments can be described in conjunction with an aspect, but can also be applied to other aspects, which is obvious to those skilled in the art. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Further features and advantages of the present invention will become apparent from the following detailed description taken in conjunction with the accompanying drawings, in which:

[0035] Figure 1 A cardinality estimator according to the prior art is shown.

[0036] Figure 2 A process for training a learning cost estimator according to the prior art is shown.

[0037] Figure 3 Cost estimation using a learned cost estimator according to the prior art is shown.

[0038] Figure 4 A method for selecting a database query plan according to an embodiment of the present invention is shown.

[0039] Figure 5A The process of constructing probabilistic cost information for a plan according to an embodiment of the present invention is shown.

[0040] Figure 5B The probability distribution based on a set of probability interval pairs according to an embodiment of the present invention is shown.

[0041] Figure 5C Plan selection based on probabilistic cost distribution graphs of two query plans according to an embodiment of the present invention is shown.

[0042] Figure 6 A risk attitude based plan selector according to an embodiment of the present invention is shown.

[0043] Figure 7 An example of processing a query to generate and compare multiple query plans, and selecting and executing a query plan according to an embodiment of the present invention is shown.

[0044] Figure 8 A process for determining the order of multiple queries in a workload and a plan for each query according to an embodiment of the present invention is shown.

[0045] Fig. 9A process for determining the order of multiple queries in a workload and a plan for each query according to another embodiment of the present invention is shown.

[0046] Fig.10 A method for executing a single query according to an embodiment of the present invention is shown.

[0047] Fig.11 A method for executing multiple queries according to an embodiment of the present invention is shown.

[0048] Fig.12 is a schematic diagram of an electronic device that can perform any or all operations of the above methods and features described explicitly or implicitly herein according to different embodiments of the present invention.

[0049] It should be noted that throughout the drawings, like features are identified by like reference numerals. DETAILED DESCRIPTION

[0050] Database Management System (DBMS), such as MySQL, PostgreSQL, Oracle, is widely used for the organization, retrieval, and analysis of data. Users can interact with the DBMS by entering queries. Given a query, there may be multiple logically equivalent plans that can be used to interact with the database to obtain the query results. In order to determine the plan with the lowest execution cost, the DBMS can use a component called a cost estimator. The cost estimator can estimate the cost of each plan and select the one with the lowest or minimum estimated cost to execute. The cost can be associated with overhead, runtime, memory usage, etc. or a combination thereof.

[0051] Those skilled in the art will appreciate that the performance of a DBMS may depend on the quality of its cost estimator. Using an inaccurate cost estimator may result in plans with high actual costs (e.g., runtime) being selected as execution plans, which may degrade the performance of the DBMS. At a high level, existing cost estimators can be divided into two categories: conventional cost estimators and learned cost estimators.

[0052] Conventional cost estimators mainly refer to statistically based cost estimators that utilize necessary statistical information (e.g., the number of distinct values ​​of a table column). Conventional cost estimators can estimate the cardinality (result size) of a (sub)query and calculate the cost of a plan based on the estimated cardinality and system resource variables (e.g., CPU speed). Those skilled in the art will appreciate that a (sub)query can refer to a query that appears within another query and can be implemented to support the query.

[0053] On the other hand, a learned cost estimator refers to a method that can map a plan to its cost using a machine learning (ML) model. A learned cost estimator can provide more accurate estimates in a variety of datasets and scenarios.

[0054] The DBMS integrated with the conventional cost estimator uses a structure to maintain the necessary statistics of the underlying data. For example, these statistics can be presented in the form of a histogram. In one embodiment, the conventional cost estimator can include a cardinality estimator that uses these statistics to estimate the result size of the (sub)query.

[0055] Figure 1 A cardinality estimator is shown. In one embodiment, the cardinality estimator 100 may include a plurality of auxiliary structures 102. The auxiliary structures may include a histogram that computes the number of tuples in a column (or columns) that fall within a particular range of values, or random tuples sampled from the underlying database (on which the selectivity of the query is approximated), etc. As shown, the cardinality estimator 100 may compute the cardinality (result size) 104 of a query 106.

[0056] Thereafter, the general cost estimator may calculate (estimate) the cost of a given plan based on one or more of the following: system resource variables (e.g., CPU and IO speeds), result sizes from the cardinality estimator, and other necessary information (e.g., table widths). This process may be repeated for multiple candidate plans, and then the plan with the lowest or minimum estimated cost may be selected as the plan for executing or answering the query.

[0057] As mentioned above, the learning cost estimator can use machine learning techniques to perform cost estimation. In one embodiment, estimating the cost of a given plan is a typical regression task, where the plan is input and the estimated cost is output. The regression model can be trained so that given a plan, the estimated cost can be sufficiently close (e.g., as close as possible) to the actual cost of the corresponding plan.

[0058] Figure 2 The process of training a learning cost estimator is shown. The process 200 may include providing the plan 202 to a feature extractor 204 to generate a vector 206. The process 200 may also include providing the vector 206 to a model 208 to generate an estimated cost 210. Thereafter, the estimated cost and the actual cost (e.g., runtime) 214 may be provided to a loss function 212 to generate an output for updating the model 208. Accordingly, the model 208 may be trained to develop a learning cost estimator. It will be appreciated by those skilled in the art that an appropriate feature extractor 204 and loss function 212 may be used in the process 200. The process is consistent with a typical machine learning process for training the model 208.

[0059] After successfully training the learned cost estimator, one can use e.g. Figure 3 Use this learning cost estimator to estimate the cost of a given program. Figure 3 Cost estimation using a learning cost estimator is shown. In one embodiment, the plan 302 can be provided to the learning cost estimator 304 to generate the cost 306. For example, the learning cost estimator 304 can refer to the trained model 208.

[0060] Those skilled in the art will appreciate that a learned cost estimator can be adapted to actual data and, therefore, can provide more accurate estimates than conventional cost estimators based on statistics.

[0061] While the cost estimation problem has been studied for decades and various estimators have been designed, existing cost estimators can be affected by uncertainty. Uncertainty quantifies the potential difference between the actual cost and the estimated cost, as well as the probability of such a difference occurring. Uncertainty is a priori value determined before the actual cost is observed. Uncertainty can be provided in the form of a probability distribution over a set of similar probability values, such as a probability-cost value pair. These uncertainties can lead to suboptimal or even catastrophic plan choices.

[0062] For example, suppose Plan A and Plan B are two plans to execute the same query. The estimated costs (in this example, runtime) of Plan A and Plan B are 3 minutes and 5 minutes, respectively. Due to uncertainty, the actual costs of Plan A and Plan B may differ from their estimated costs. For example, the actual cost of Plan A may be, for example, 7 minutes, while the actual cost of Plan B may be 6 minutes. Correspondingly, the errors (or deviations), which represent the difference between the estimated cost and the observed actual cost, are 4 minutes and 1 minute, respectively. Therefore, selecting a plan with a low estimated cost (such as Plan A) may result in longer query processing time (actual cost of 7 minutes) in practice.

[0063] Although uncertainty is almost impossible to avoid due to the complexity of data and workloads, understanding this information can help determine which plans have more credible performance (i.e., the actual cost is closer to the estimated cost), as well as other potential benefits described in this article. Referring to the above example, assume that in the first scenario, the uncertainty of plan A makes it 80% likely that its error is 4 minutes or less, and the uncertainty of plan B makes it 80% likely that its error is 1 minute or less. Comparing the uncertainty of plan A (maximum error of 4 minutes, probability of 80%) with the uncertainty of plan B (maximum error of 1 minute, probability of 80%), it can be determined that the more credible plan is plan B. Similarly, in the second scenario, the uncertainty of plan A makes it 90% likely that its error is 4 minutes or less, while the uncertainty of plan B makes it 80% likely that its error is 4 minutes or less. Then, comparing the uncertainties of the plans in the second scenario, it can be determined that the more credible plan is plan A. Accordingly, the credibility of a plan (or the most credible plan among multiple plans) can be determined based on a combination of the amount of deviation (between the estimated cost and the actual cost) and the probability of the deviation.

[0064] As described above, those skilled in the art will appreciate that, in some embodiments, the cost of a plan may refer to the amount of time it takes the DBMS to execute the plan. The execution of a plan may refer to the operations performed by the DBMS to answer a query. This may include: receiving the query and any additional information (such as a query prompt), performing data retrieval and processing, and returning the results of the query. Thus, for example, the amount of time it takes the DBMS to execute the plan may refer to the amount of time it takes the DBMS to return the query results. More generally, the cost incurred by the execution plan may refer to the cost (such as time, number of operations, and combinations thereof, etc.) incurred by the DBMS in the process of returning the query results. Credibility may refer to predictability, for example, related to the deviation from uncertainty, which describes the likelihood and degree to which the actual cost deviates from the estimated cost. It is noteworthy that in the embodiments of the present disclosure, the probability distribution of the query cost is calculated, rather than the probability distribution of the predicate selectivity. No assumptions are made about the type of probability distribution (such as a uniform distribution or a beta distribution).

[0065] Although uncertainty is a common problem in DBMS and uncertainty information is helpful in plan selection, existing cost estimators typically only output a single value representing the estimated cost without providing an indication of the uncertainty associated with the estimated cost.

[0066] Some techniques, such as the Robust Query Optimizer, aim to quantify the "predictability" of cardinality estimates and strike a balance between performance and predictability during plan selection. In such techniques, high predictability can indicate that the actual cardinality of a query is likely to be closer to the estimated cardinality. These techniques involve maintaining random tuples of a table in the DBMS and running a given query on these random tuples. These techniques also involve using Bayes' rule to compute a probability distribution over the actual selectivity of that query given the selectivity observed from the random tuples. The probability distribution of the selectivity of a query can be used interchangeably with the cardinality.

[0067] The user can then specify a threshold α in the range [0, 1] and use the αth quantile (e.g., the 30% quantile) of the selectivity distribution of the query as the estimated selectivity of the query. The cost of each plan is then calculated based on the selectivity estimate corresponding to the αth quantile. The plan with the lowest estimated cost is used as the execution plan. In these techniques, smaller α assigns higher weights to performance and plans with low costs, so plans with high uncertainty are prioritized, while larger α assigns higher weights to predictability, and plans with low uncertainty are prioritized. Although these techniques may consider the trade-off between query performance and uncertainty, these techniques may encounter problems that limit their application in modern advanced DBMSs.

[0068] For example, these techniques may make certain assumptions about the selectivity distribution, such as following a uniform distribution or a beta distribution, or require knowledge of the actual selectivity distribution. Those skilled in the art will appreciate that these assumptions and requirements may be impractical for many scenarios. In addition, these techniques focus on quantifying the uncertainty of cardinality estimates and using this information to infer the uncertainty of cost estimates. However, such a strategy may only be applicable to conventional cost estimators, which first map queries / plans to cardinalities and then map them to cost values. Accordingly, these techniques may not be applicable to learned cost estimators, which directly map plans to cost values.

[0069] Embodiments described herein can address the challenge of quantifying the uncertainty of a given cost estimate and utilizing this information for credible plan selection. Embodiments described herein can also utilize uncertainty information by enabling DBMS users to specify a "risk attitude" based on the uncertainty information to participate in the plan selection process. In some embodiments, the risk attitude can refer to a preference generated by a user between plans with low cost and low uncertainty when there is a conflict.

[0070] Embodiments described herein can quantify the uncertainty of a particular cost estimate (e.g., accurately or relatively accurately). Various embodiments can achieve this substantially regardless of query complexity, data distribution, estimator type (conventional estimator or learning estimator), or a combination thereof. Embodiments described herein can facilitate uncertainty-aware plan selection and identify more credible plans by integrating uncertainty information into plan selection strategies.

[0071] In the embodiments described herein, users can also participate in the plan selection process. For example, in some embodiments, uncertainty information can be presented to a DBMS user, who can select a plan based on a desired level of uncertainty or a combination thereof. In some embodiments, a user can customize his or her "risk attitude" during the plan selection process. For example, when a conflict (or choice) arises between low cost and low uncertainty, a user can indicate a preference generated by the user between low cost and low uncertainty.

[0072] The embodiments described herein may provide a method for selecting a database query plan. Figure 4 A method for selecting a database query plan according to an embodiment of the present invention is shown. Method 400 may include generating multiple plans for potential execution 402 by a DBMS, each of the multiple plans 402 being used to generate the same response to the same specified database query (e.g., query 1 in multiple queries 420) when executed by the DBMS. Each plan may be considered equivalent in terms of the final query response it generates. A plan generator may be used to generate multiple plans 402. The plan generator may be configured in a conventional manner that is readily understood by those skilled in the art.

[0073] The method 400 may also include generating a cost distribution for each plan in the plurality of plans 402, the cost distribution including a set of probability-cost value pairs. In one embodiment, the cost estimator 404 may be calibrated to quantify the uncertainty associated with each plan in the plurality of plans 402. An uncertainty calibrator 412 receiving an output from the cost estimator 404 may utilize statistical tools such as conformal techniques to construct a cost distribution 406 corresponding to the plurality of plans 402. The cost estimator 404 may be a conventional cost estimator or a learned cost estimator. In some embodiments, the uncertainty calibrator may include a cost estimator as a component thereof.

[0074] As those skilled in the art will readily appreciate, conventional cost estimators or learning cost estimators typically only output a single estimated cost associated with an input plan. Uncertainty calibrator 412 is used to generate a cost distribution using at least in part the output of the cost estimator. The cost distribution includes multiple estimated (e.g., upper bound) costs associated with the input plan, and associated probability information, such as a cost distribution including probability cost value pairs.

[0075] In one embodiment, each probability cost value pair in the set of probability cost value pairs may indicate: if and when the plan is executed, (as one value in the probability cost value pair) an estimated upper limit cost of the associated plan, and (as another value in the probability cost value pair) the probability that the actual cost of the plan will be equal to or less than the estimated upper limit cost. Accordingly, embodiments may provide additional information (i.e., quantification of uncertainty) in addition to estimated costs (e.g., estimated runtime of the plan). For example, a probability cost value pair (p, v) may be interpreted as an indication that the cost of executing the plan is expected to not exceed v when the probability is p. Thus, an estimated upper limit cost (e.g., an estimated maximum amount of time) is associated with a corresponding probability that the estimated upper limit cost will be adhered to (i.e., the actual cost is less than or equal to the estimated upper limit cost).

[0076] The method 400 may also include selecting a plan 410 from the plurality of plans 402 based at least in part on an evaluation of a set of probability-cost value pairs (e.g., cost distribution 406) of the plurality of plans via a plan selector 414. The evaluation may involve a comparison between the plurality of probability-cost value pairs, the comparison being performed based on a particular criterion. The criterion may depend on certain preferences, such as user preferences. Depending on the criterion, the evaluation may be performed based on a comparison of probability values, cost values, or a combination thereof. For example, referring to Figure 7 Table 708 in , and as explained later in this article, the criterion can be: the plan should be the plan most likely to be completed within 7 seconds. According to Table 708, the probability of P1 being completed within 7 seconds is 80%, while the probability of P2 being completed within 7 seconds is 60%, so P1 can be selected.

[0077] The method 400 may also include causing the DBMS to execute the selected plan 410, such as by determining an appropriate query hint and providing the query hint to the DBMS. Causing the DBMS to execute the selected plan also includes submitting a query to the DBMS. Causing the DBMS to execute the selected plan may also include submitting auxiliary information (such as a query hint) to the DBMS to influence the DBMS to execute the selected plan, which is readily understood by those skilled in the art.

[0078] In some embodiments, the selection of plan 410 may be based on the user's risk attitude (user-generated preferences) 408. Accordingly, in an embodiment, the user may customize their risk attitude, and based on the user's risk attitude, plan 410 may be selected based on an assessment of the user's risk attitude in combination with cost distribution 406. Accordingly, the selected plan 410 may be the plan among plans 402 that best satisfies the user's risk attitude.

[0079] In some embodiments, a user may indicate their risk attitude based on one or more of: a time threshold for a single query or a single workload, a percentile constraint for a workload, and a preference between an interactive mode (a first mode) and an execution mode (a second mode). These thresholds, constraints, and modes are described elsewhere herein.

[0080] In the embodiments described herein, a cost distribution 406 may be constructed or generated. In one embodiment, the cost estimator 404 may be calibrated so that for a given plan, an uncertainty calibrator (including or associated with a cost estimator) may output a cost distribution corresponding to the plan. In this sense, "calibration" may refer to "packaging" (e.g., enhancing) the cost estimator with a conformal technique so that the estimated error of the estimator may be quantified. The estimated error may then be used to construct a cost distribution associated with the uncertainty information. In some embodiments, conformal techniques may be used in conjunction with the cost estimator 404, and relevant parameters may be varied to construct the cost distribution 406. Those skilled in the art may appreciate that conformal techniques may refer to statistical tools designed to quantify the uncertainty of a regression model. When constructing a cost distribution, some embodiments may assume that a cost estimator (e.g., as a conventional cost estimator or a machine learning-based cost estimator) is already provided and that a query workload is available.

[0081] In some embodiments, one or more conformal techniques may be initialized prior to generating the cost distribution. Initialization may be performed based on multiple examples. A person skilled in the art may be aware of various conformal techniques that may be initialized prior to deployment. In one embodiment, initializing a conformal technique may include providing a set of tuples to a device running a conformal technique, each tuple reflecting an instance of: a plan, an estimated cost of the plan, and an actual incurred cost of the plan. The more tuples provided to the device, the better the conformal technique can provide cost estimates for additional plan inputs.

[0082] In some embodiments, as part of initializing the conformal technique, a probability level α (e.g., 90%) may be specified for the conformal technique, and then the conformal technique may be run to capture a mapping from the plan (fed as input to the conformal technique) to the error or deviation (absolute difference between estimated cost and actual cost) of the plan. The more tuples fed to the conformal technique, the more accurate the mapping captured.

[0083] Accordingly, in one embodiment, the result of initializing the conformal technique is that, given a substantially arbitrary plan, the conformal technique is used to predict the uncertainty of the cost estimator output (estimated cost) for that plan. In some embodiments, the uncertainty of the cost estimator estimate can be an interval output by the conformal technique. In some embodiments, the interval can be in the following format, for example, "When the probability is α, the actual cost is expected to fall within the interval (C-τ, C+τ), where C is the average estimated cost (e.g., provided by the cost estimator).

[0084] In some embodiments, to generate a cost distribution, a conformal technique may be used to wrap the cost estimator. As described above, the conformal technique may assign an interval to each cost estimate, which indicates the cost range within which the cost is expected to fall within a specified probability level α. Accordingly, by changing the value of the specified probability level α (e.g., 90%, 80%, 70%, etc.), an embodiment may construct multiple corresponding cost ranges (intervals) for a given cost estimate. This may be used to form a corresponding planned cost distribution.

[0085] It should be noted that, in the above, an estimated cost C is generated. The estimated cost is provided as an input to the conformal technique. The estimated cost can be generated by a cost estimator (e.g., cost estimator 404). The conformal technique operates in part based on the estimated cost, so the starting point for generating probability cost value pairs is to generate the estimated cost.

[0086] In various embodiments, as described elsewhere herein, an upper bound of a cost range (interval) provided by a conformal technique and a specified probability level leading to the cost range may be provided as a probability-cost-value pair.

[0087] The conformal technique may be implemented by an estimation module. The estimation module may receive a probability level α and invoke a conformal technique as described above to provide a cost range, or at least an upper bound of the cost range. That is, the estimation module may provide a cost value such that when the probability is α, the expected cost incurred by the plan is less than or equal to the cost value.

[0088] Figure 5AA process for constructing probabilistic cost information for a plan according to an embodiment of the present invention is shown. In one embodiment, a plan 502 (for a given query) can be fed to a cost estimator 404 to generate an estimated cost 504. Thereafter, one or more probability levels α can be provided to a conformal technique 506 (e.g., a device running a conformal technique) as well as the plan 502 and the estimated cost 504. As shown, at least two probability levels α1 and α2 are provided to the conformal technique. For each probability level in the one or more probability levels α, the conformal technique can be used to indicate an interval. The explanation is that when the specified probability level is α, the actual cost of the plan 502 (e.g., when running) is expected to fall within the interval. Accordingly, the cost distribution 508 is a probability set of values ​​that can be determined based on one or more intervals generated as shown. Figure 5A An embodiment may correspond to Figure 4 Generation of a cost distribution 406 of . In some embodiments, conformal techniques can only return the maximum cost (i.e., the upper bound of the interval), rather than the interval. In some embodiments, based on the interval, a probability cost value pair can be generated, for example, by indicating the upper bound of the interval as the cost portion of the probability cost value pair and indicating the associated probability level α as the probability portion of the probability cost value pair. The cost distribution may include multiple such probability cost value pairs.

[0089] It should be noted that, in an embodiment, if a sufficient number of probability interval pairs are calculated, a cost distribution (as described above) can be generated. A probability interval pair refers to an interval in which the cost may decrease (for example, between a minimum value and a maximum value) and the associated probability that the cost decreases within this interval. Assume that the first probability interval pair indicates that the probability of falling into the interval [l1, u1] at runtime is 1–α1, and the second probability interval pair indicates that the probability of falling into the interval [l2, u2] at runtime is 1–α2, and α1<α2, then the probability of falling into the interval [l1, l2] or [u2, u1] is (α2-α1) / 2. Using more α values ​​can provide a finer-grained distribution, as shown in reference Figure 5B described.

[0090] Figure 5B A probability distribution based on a set of probability interval pairs according to an embodiment of the present invention is shown. In one embodiment, a set of probability interval pairs can be indicated according to Table 520. The explanation of the table is that, under a specified probability, the cost will fall within a specified interval. Based on the set of probability interval pairs, a fine-grained distribution can be determined, such as represented by a histogram 530 and a curve distribution 540. An illustrative example of generating a fine-grained distribution is as follows. Referring to Table 520, the first probability interval pair in the table can be written as probability (70% = 1–α1) and interval [l1, u1] = [1, 5]. Therefore, it can be determined that α1 is 0.3.

[0091] Similarly, the second probability interval pair in Table 520 can be written as probability (30% = 1 – α2) and interval [l2, u2] = [2, 3]. Thus, α2 can be determined to be 0.7. Assuming α1 < α2, l1 < l2, u2 < u1, the probability that the runtime falls into the interval [l1, l2] or [u2, u1] can be determined according to (α2 – α1) / 2. Determining these intervals and their corresponding probabilities can be as follows: [l1, l2] = [1, 2], [u2, u1] = [3, 5], (α2 – α1) / 2 = (0.7 – 0.3) / 2 = 0.2. Correspondingly, two additional probability interval pairs can be determined based on the first probability interval pair and the second probability interval pair in Table 520. These two additional probability interval pairs can be, respectively, a third probability interval pair indicating that the probability of the runtime falling into the interval [1, 2] is 20%, and a fourth probability interval pair indicating that the probability of the runtime falling into the interval [3, 5] is also 20%. Then, these determined probability interval pairs can be used to generate a histogram, such as histogram 530.

[0092] Other additional probability interval pairs can be determined using the α to generate a finer-grained distribution, as shown in the curve distribution 540.

[0093] The embodiments described herein can provide an enhanced process for cost estimation based on conformal techniques. The embodiments can also use conformal techniques to quantify the uncertainty of a regression model. In some embodiments, the quantification can be performed without unrealistic assumptions about the data distribution and workload patterns.

[0094] The embodiments described herein can also use the uncertainty calibrator 412 to construct the cost distribution of a plan (among multiple plans associated with a query). The cost distribution can more comprehensively and accurately describe the potential outcomes of the plan (e.g., in terms of the expectation of the cost incurred) and help select a more credible plan.

[0095] In the embodiments described herein, uncertainty information can be utilized for plan selection. The form of the uncertainty information can be probability-cost value pairs, intervals into which the cost and the associated probability expectation fall, probability distributions of the expected cost, etc., or a combination thereof. In the embodiments described herein, the user can participate in the plan selection process by customizing the risk attitude to balance between a plan with a low cost and a plan with low uncertainty (e.g., low variance in the basic probability distribution).

[0096] Given the cost distribution of candidate plans, a DBMS user may prefer plans with different profiles (e.g., the shape of the cost probability distribution). Such preferences may be based on the user's requirements, preferences, or constraints. Accordingly, in an embodiment, a user may customize the user's risk attitude by indicating one or more requirements, preferences, constraints, or a combination thereof. An embodiment may be configured to make plan selections based on the cost distribution and the user-indicated requirements.

[0097] For example, refer to Figure 5C , plan A has a cost distribution from a1 to a2, whose average is equal to (a1+a2) / 2, and plan B has a cost distribution from b1 to b2, whose average is equal to (b1+b2) / 2, and the average of plan B is higher than the average of plan A as shown in the figure. The variance of plan B is lower than the variance of plan A. Assume that the user wants to complete the query execution with a maximum cost τ, in this case, plan B is preferred because the probability of exceeding τ under plan B (e.g., where the probability can be expressed as area 560 under the cost distribution of plan B between τ and b2) is less than the probability of exceeding τ under plan A (e.g., where the probability can be expressed as area 560+570 under the cost distribution of plan A between τ and a2), as shown in the figure. In other words, plan B is preferred because the probability of cost below τ under plan B is higher than the probability of cost below τ under plan A.

[0098] Figure 6 A plan selector 414 based on risk attitude (based on user-generated preferences) according to an embodiment of the present invention is shown. The plan selector 414 can select a plan based on different modes. For example, the plan selector 414 can select a plan based on the default mode 602 (i.e., when operating in this mode). The default mode 602 can indicate that a plan is to be selected based on the minimum estimated cost. For example, when the user does not indicate any constraints, the default mode can be activated. These modes can be selected by the user as a user-generated preference.

[0099] In some embodiments, plan selector 414 can select a plan based on full control mode 604. In full control mode 604, the cost distribution (e.g., associated probability cost value vs. cost distribution 406) of all plans (e.g., multiple plans 402) can be presented to the user, and the user can select a plan (e.g., plan 410) based on the user's preferences, which can reflect risk attitude. Accordingly, in embodiments, the user has control and flexibility to select a desired plan at the expense of additional effort on the part of the user in analyzing the cost distribution or related data. These embodiments can be suitable for queries where performance is critical.

[0100] In some embodiments, plan selection can be based on a per-query threshold 606. For example, a user can specify or indicate a maximum cost threshold for a single query (e.g., 2 minutes). The maximum cost threshold can indicate the need to return query results without invoking a cost that exceeds the maximum cost threshold (e.g., query results should be returned within two minutes). Accordingly, the plan selector 414 can select a plan that returns results with the lowest probability of exceeding the maximum cost threshold, assuming that such a plan is available. In other words, the selected plan can be the plan with the highest probability of having a cost less than or equal to the maximum cost threshold, which is also referred to as the maximum acceptable single query cost. In addition, the maximum cost threshold can be user-generated, or more generally can be based on user-generated preferences.

[0101] In some embodiments, a query workload (a batch of multiple queries) is considered rather than a single query, and plan selection is performed for some (or all) queries in the workload. In these embodiments, for each query in a batch of multiple queries, the generation of the plan and the generation of the probability cost value pair are performed in the same general manner as specified above for the case of a single query. Then, one or more queries are selected, and for each selected query, a corresponding plan is selected. Query selection and plan selection are based at least in part on the evaluation of the generated probability cost value pair set. Finally, by submitting the query (e.g., sequentially, simultaneously, or a combination thereof) to the database together with a "hint" or other applicable means for influencing the DBMS, the DBMS is caused to execute the selected query and plan.

[0102] In some embodiments, plan selection can be based on query workload threshold criteria 608. For example, a user can indicate a threshold for workload (i.e., a batch of queries), and plan selection can be based on the goal of completing as many queries as possible while trying (with sufficient probability) to maintain the cost at or below the specified threshold. Further details of embodiments of query workload threshold criteria are presented elsewhere herein, such as with reference to Figure 8 .

[0103] In some embodiments, plan selection may be based on query workload percentile criteria 610. For example, a user may indicate a percentage x and a duration t, and plan selector 414 may select a plan such that, for a workload, at least x% of queries will complete within duration t with sufficient probability. Further details of embodiments of query workload percentile criteria are presented elsewhere herein, such as with reference to Fig. 9 .

[0104] In some embodiments, plan selection may be based on an indication of whether plan selection will be made in accordance with an interactive mode or an execution mode 612. In some embodiments, a user may switch between an interactive mode and an execution mode when performing query processing, so that such indication is provided by a user-generated preference indication of which of these modes should be used. In interactive mode, a user may express a desire to avoid long-running plans, and therefore prefer queries with low uncertainty. That is, a user may express a preference for greater certainty in cost rather than lower cost. In execution mode, a user may express concern about the total cost (e.g., runtime) of a query batch, and therefore a user may prefer plans with low costs. In some embodiments, interactive mode and execution mode may be provided as separate options that can be independently selected. Operations in interactive mode are described elsewhere in this document.

[0105] Accordingly, in an embodiment, a plan may be selected based on one or more user preferences and a cost distribution so that the selected plan is the most desirable plan for each query. In some embodiments, a user may specify one or more requirements using any available options.

[0106] In embodiments described herein, cost distribution information may be utilized by a user indicating the user's requirements during the plan selection process. In some embodiments, the behavior of a query or workload may be predictable and controllable, which may provide more reliable DBMS performance. Furthermore, in embodiments, the user's requirements may be met in a variety of scenarios regarding query processing, which may also provide flexibility for the user to select plans for ad hoc tasks.

[0107] In the embodiments described herein, the cost distribution of the query plan can be generated by calibrating the cost estimator (which can include both the conventional cost estimator and the learning cost estimator) using conformal technology. The embodiments can also provide various options for the user to indicate the risk attitude and requirements about the query performance. In the embodiments, the query plan can also be selected according to the risk attitude and requirements of the user.

[0108] Figure 7An example of processing a query to generate and compare multiple query plans, and selecting and executing a query plan according to an embodiment of the present invention is shown. Processing a query may involve a plan generator 704, a cost estimator 404, an uncertainty calibrator 412, and a plan selector 414. In one embodiment, a user (or other source) may indicate a query 702 for processing. Processing 700 may include passing the query 702 to a plan generator 704, which is used to obtain (e.g., generate) several logically equivalent plans 706 to execute the query, which are represented as P1, P2, ..., Pn. These plans 706 may read the database tables involved in the query 702 in different orders, or use various scan operators (e.g., sequential scans, index scans) or join operators (e.g., merge joins, hash joins, nested loops), but if executed by a DBMS, they all provide the same results.

[0109] In some embodiments, process 700 may also include passing plans 706 (e.g., P1, P2, ..., Pn) to cost estimator 404. Cost estimator 404 may provide cost information 707 for each plan. For example, the cost estimator may predict that P1 will take 6 seconds to complete, P2 will take 7 seconds to complete, etc. The cost estimator may be a conventional cost estimator or a machine learning based cost estimator and may return a single cost value for each plan it provides.

[0110] In some embodiments, the process 700 may also include providing each plan along with its estimated cost to the uncertainty calibrator 412. The uncertainty calibrator 412 may assign a plurality of cost intervals to each plan based on the characteristics of each plan (e.g., which tables / attributes / tuples are involved, which scan / join operators are involved, the join order, etc.) and the estimated cost. Each of the plurality of intervals may be based on a different corresponding probability level α. Accordingly, the uncertainty calibrator may output a cost distribution, such as a distribution table 708. Referring to the distribution table 708, where the probability level α is 20% and 40%, P2 may process the query 702 faster than P1. However, when the probability level α is 60% and 80%, P1 may process the query 702 faster than P2. The distribution table indicates a plurality of probability-cost value pairs. For example, the probability-cost value pair (20%, 4s) indicates that when the probability is 20%, P1 may process the query within 4 seconds, the probability-cost value pair (40%, 3s) indicates that when the probability is 40%, P2 may process the query within 3 seconds, and so on.

[0111] In some embodiments, processing 700 may also include inputting a cost distribution (e.g., distribution table 708) (or an associated probability-cost value pair) into a plan selector 414, which integrates user preferences regarding query performance. For example, a user may prefer a plan that is likely to complete in 7 seconds. According to the distribution table, P1 has an 80% probability of completing in 7 seconds, while P2 has a 60% probability of completing in 7 seconds, so P1 may be more ideal and is therefore selected. The user may also specify other preferences as described elsewhere herein. The plan selector outputs the selected plan 710.

[0112] Assume that P1 is the plan preferred by the DBMS user. In one embodiment, P1 can be transferred to a set of "query hints" that describe the join order, join operator type, and scan operator type of P1. The query hint set can then be fed to the DBMS. The provided query hint set can be executed as part of causing the DBMS to execute the selected plan. Those skilled in the art will appreciate that hints are a feature supported by most DBMSs for users to specify desired plans. Accordingly, query 702 can then be passed to the DBMS, and the DBMS will select P1 as the execution plan. More generally, a set of query hints 714 can be provided to the DBMS, for example, via a plan selector, wherein the query hints 714 cause or tend to cause the DBMS to execute the selected plan 710.

[0113] In some embodiments, plan selection can be based on a workload Q, i.e., a batch of queries, such that plan selection is based on completing as many queries as possible while complying with a cost cap threshold. For example, when the cost is based on time, plan selection can be based on completing as many queries as possible within a duration or amount of time τ. That is, a batch of queries is presented, and a subset of the batch of queries is selected, and for each query in the subset, a plan is selected, where the subset and the corresponding plan meet the goal. For example, the goal can be represented by a workload runtime threshold. Because the cost distribution (e.g., 406, 508, and 708) can describe the runtime in a probabilistic manner, the exact number of queries that can be completed while complying with the cost cap threshold (e.g., within a certain duration τ) may not be known. However, in an embodiment, the expected number of queries to be completed can be increased while complying with the cost cap threshold.

[0114] Accordingly, in one embodiment, the workload runtime threshold goal can be formalized by modeling it as an integer programming problem, thereby obtaining a simple but intuitive plan selection heuristic that can solve the optimization problem.

[0115] Considering a specific query Qi, ni can represent different potential plans for Qi, The runtime of the jth plan (j∈[1,ni]) can be expressed as can be the corresponding runtime distribution. That is, is a probability distribution A random variable of type . All runtime variables that can be used to organize Qi's plans. Let Ci be a binary vector, if the jth plan is selected as the execution plan of Qi, then Ci[j] is 1, and all other bits are 0. Therefore, increasing the probability that a workload can be completed while complying with a given threshold τ can be based on solving the following optimization problem:

[0116] maximize

[0117]

[0118] in, |S τ (1,…,m)|≥|S|. In other words, S τ (t1,…,tm) is the maximum set of subscripts in the range [1,m] such that the sum of the values ​​in {t1,…,tm} indexed by these subscripts does not exceed τ.

[0119] The constraint of equation (1) can ensure that a plan is selected for each query. In one embodiment, each selected plan Pi can be assigned a "local" cost (e.g., time) threshold ti, which represents the maximum cost that the plan should incur, such as the amount of time the plan should complete. In order to complete more plans within τ, in one embodiment, plans associated with smaller ti can be executed first.

[0120] For a particular configuration of local thresholds, t1,…,tm, it is possible to consider only S τ (t1,…,tm) indexes, since these queries are expected to have the lowest cost (e.g., fastest), and adding any remaining plans would likely violate the overall threshold constraint. τ The probability that a plan indexed by (t1,…,tm) can be completed while complying with its corresponding local threshold can be calculated as Therefore, the expected number of plans that can be completed while respecting τ is

[0121] The goal is to select the best plan for each query (choose Ci) such that the expected number of plans that can be completed in all potential configurations of the local threshold can be maximized while respecting τ.

[0122] Optimizing equation (26) can be viewed as an integer programming problem (selecting a plan identifier (ID) for each query), which is known to be NP-complete. Since enumerating all combinations of plans may be expensive and non-exhaustive, embodiments can provide an efficient and effective heuristic method for plan selection.

[0123] To maximize the expected number of plans completed within τ, two questions need to be answered: (1) in what order should queries be processed, and (2) which plan should be selected for each query. Figure 8 The illustrated process 800 can answer both of these questions simultaneously. Figure 8 A process for determining the order of multiple queries in a workload and a plan for each query according to an embodiment of the present invention is shown.

[0124] exist Figure 8 In an embodiment, assuming that an estimator (reference input) at probability levels q1, q2, ..., qk (sorted in descending order) has been constructed, the model corresponding to probability q1 can be used to estimate the cost of each candidate plan for the query in workload Q, and select the plan with the minimum estimated cost for each query. The selected plans can be sorted in ascending order of their costs (so that plans with lower costs can be executed first). The number of plans that can be completed within τ can be calculated (assuming it is k). Since at the current probability level, only q (expressed as a percentage) plans can be completed while complying with their estimated costs (for example, within their estimated runtime), in one embodiment, the expected number of plans that can be completed while complying with τ can be approximated as k*q. By traversing all probability levels, a set of plans that can make the maximum expected number of plans that may be completed while complying with τ can be determined.

[0125] In some embodiments, plan selection may be based on workload percentile targets. For example, given a workload of m queries, labeled Q1, ..., Qm, a goal may be to process or complete at least M (M∈[1,m]) queries. It will be appreciated by those skilled in the art that it may not be possible to determine whether a provided percentile target can be met. However, in embodiments, the probability that a percentile target can be met may be increased.

[0126] In one embodiment, the workload percentile target can be modeled as an integer programming problem, and the notation defined herein can be reused, including Ci and Vi. Accordingly, increasing the probability that at least M queries can be completed within a given threshold τ can be based on solving the following optimization problem:

[0127] maximize

[0128]

[0129] The constraint of equation (2) ensures that every query Select at most one plan, the total number of plans selected can be M For the other mM queries, essentially any metric for plan selection may be employed, and these queries may be executed following the M plans selected by equation (2) without affecting the probability that the percentile target can be met.

[0130] The objective of equation (2) can be described as follows. If a plan is selected for a query Qi, a "local" threshold ti can be assigned to the plan, and the probability that the selected plan will complete within ti can be calculated. This can be calculated for all queries, and the sum of all local thresholds τ can be ensured. Therefore, the integral over all possible local threshold combinations can be the probability of meeting the workload percentile target.

[0131] Since solving equation (2) is equivalent to answering an integer programming problem (selecting a plan ID for each query), a heuristic method for plan selection can be designed. In one embodiment, a process 900 is provided that can determine the order in which queries are processed and which execution plan to use for each query. Fig. 9 A process for determining the order of multiple queries in a workload and a plan for each query according to another embodiment of the present invention is shown.

[0132] exist Fig. 9 In an embodiment, it can be assumed that estimators at probability levels q1, q2, ..., qk (sorted in descending order) have been constructed. In one embodiment, the model corresponding to probability q1 can be used to estimate the cost of each candidate plan for a query in the workload, and the plan with the minimum estimated cost can be selected for each query. The selected plans can be sorted in ascending order of their costs (so that plans with lower costs can be executed first to maximize the probability of meeting the goal).

[0133] If the total cost of the first M plans in the sorted sequence is less than τ, then these plans can be executed in the same order. However, if the total cost is greater than τ, it means that the percentile target cannot be met at the current probability level, and the above process can be repeated using the model corresponding to the next probability level.

[0134] Fig.10A method for executing a single query according to an embodiment of the present invention is shown. In one embodiment, method 1000 can be performed by, for example, electronic device 1200 or a computer similar to electronic device 1200. Additionally or alternatively, the method (and other methods described herein) can be performed by a DBMS, which can include one computer or multiple networked computers. At 1002, the method can include: generating multiple plans for potential execution by the DBMS (e.g., plans 402 and 706), and when executed by the DBMS, each of the multiple plans is used to generate the same response to the same specified database query (e.g., query 1 or query 702 in query 420). It should be noted that the computer or DBMS can receive a query, and in response to receiving the query, the computer or DBMS can generate multiple plans as described above and elsewhere in this document.

[0135] At 1004, the method may also include: generating a set of probability cost value pairs (e.g., 406, 508, and 708) for each of the multiple plans, wherein each probability cost value pair indicates an estimated upper limit cost of the plan if and when the plan is executed, and the probability (e.g., α) that the cost of the plan is equal to or less than the estimated upper limit cost. At 1006, the method may also include: selecting one of the multiple plans based at least in part on an evaluation of the set of probability cost value pairs for the multiple plans. The selection may be performed by the plan selector 414. At 1008, the method may also include: causing the DBMS to execute the selected one of the multiple plans. The DBMS may execute the selected plan and return the result to the query. Therefore, the method may include providing the result to the query, such as to a computer or a user. Similarly, an associated device may be used to provide the result to the query. Similarly, as described above, in various embodiments, the method may be performed by the DBMS. In this case, the DBMS may receive a query, generate a plan (as at 1002), generate probability cost value pairs (as at 1004), select one of the plans (as at 1006), and cause itself to execute the selected plan (as at 1008).

[0136] In some embodiments, the cost of a plan may be the amount of time it takes the DBMS to execute the plan. In some embodiments, the estimated upper bound cost of a plan may be the estimated maximum amount of time it takes the DBMS to execute the plan.

[0137] In some embodiments, generating the set of probability-cost value pairs may include: generating, for each of the plurality of plans, a corresponding estimated cost of the plan (e.g., estimated cost 504, cost 707). In some embodiments, generating the set of probability-cost value pairs may also include: for each of the plurality of plans, and for each probability value in the set of probability values, providing the probability value and the plan to an estimation module, which may be part of an uncertainty calibrator. Specifically, the estimation module may be a device that implements the conformal technique as described herein, which is implemented by performing operations according to the conformal technique itself, or calling another device to perform the conformal technique on its behalf.

[0138] In some embodiments, generating the set of probability-cost value pairs may also include: for each plan in the plurality of plans, and for each probability value in the set of probability values, subsequently receiving a cost value from an estimation module such that when the probability is equal to the probability value, the expected cost incurred by the plan is less than or equal to the cost value. For example, such information received from the estimation module may be generated by an applied conformal technique.

[0139] In some embodiments, generating the probability cost value pair set may further include: for each plan in the plurality of plans and for each probability value in the probability value set, setting a probability cost value pair in the probability cost value pair set to pair the cost value with the probability value. That is, the output of the conformal technique is used to generate the probability cost value pair, for example Figure 4 and Figure 5A Cost distribution of 406 and 508 and Figure 7 Of which 708.

[0140] In some embodiments, the estimation module may be run based on a conformal technique, such as 506. In some embodiments, the conformal technique may be pre-initialized based on a plurality of examples, wherein each example indicates a DBMS query plan, an estimated cost of the DBMS query plan, and an actual cost incurred by executing the DBMS query plan.

[0141] In some embodiments, selecting one of the multiple plans can also be based on a user-generated preference (e.g., risk attitude 408). In some embodiments, selecting one of the multiple plans includes: presenting an output to a user, and receiving an input from the user, the input being responsive to the output, wherein the output indicates a set of probability-cost value pairs, each probability-cost value pair indexing to a corresponding one of the multiple plans. For example, cost distribution 406, 508, or 708 can be provided to the user, and the user indicates a preference (e.g., risk attitude 408) based on the cost distribution preferred by the user. In some embodiments, selecting one of the multiple plans can also be based on a user-selected one of the multiple plans, and selecting one of the multiple plans can also include: selecting a user-selected one of the multiple plans.

[0142] In some embodiments, selecting one of the plurality of plans may further include selecting one of the plurality of plans that has a highest probability of having a cost less than or equal to an indicated maximum acceptable single query cost. In some embodiments, the maximum acceptable single query cost is based on user-generated preferences (e.g., per-query threshold 606).

[0143] In some embodiments, the method may further include: operating in the first mode or the second mode according to the instruction. In some embodiments, the first mode is characterized by higher certainty in cost rather than lower cost, and the second mode is characterized by lower cost rather than higher certainty in cost. For example, the first mode may refer to an interactive mode, and the second mode may refer to an interactive mode. Figure 6 In some embodiments, the indication is obtained from user-generated preferences. The user can indicate the user's preference for selecting a plan by the plan selector 414 through the interactive mode or execution mode 612.

[0144] In some embodiments, in the first mode (interactive mode), selecting one of the multiple plans may include: selecting a probability level greater than 50% (e.g., α>50%). In some embodiments, in the first mode, selecting one of the multiple plans may also include: comparing the multiple plans based on the estimated upper limit costs corresponding to the multiple plans in the estimated upper limit costs paired with the selected probability level. For example, the plan selector 414 may select the plan with the lowest upper limit cost among all plans with α greater than 50% at a certain level. In some embodiments, in the first mode, selecting one of the multiple plans may also include: selecting one of the multiple plans that has the corresponding lowest estimated upper limit cost in the estimated upper limit costs paired with the selected probability level.

[0145] In some embodiments, in the second mode, selecting one of the plurality of plans includes selecting one of the plurality of plans that has the lowest estimated cost. In this case, the estimated cost may take precedence over the probability level α (e.g., when the plan selector 414 is running in the execution mode).

[0146] In some embodiments, selecting one of the multiple plans may include selecting a probability level greater than 50%. In some embodiments, selecting one of the multiple plans may further include comparing the multiple plans based on the estimated upper limit costs corresponding to the multiple plans in the estimated upper limit costs paired with the selected probability level. For example, the plan selector 414 may select the plan with the lowest upper limit cost among all plans with α>50%. In some embodiments, selecting one of the multiple plans may further include selecting one of the multiple plans that has the corresponding lowest estimated upper limit cost among the estimated upper limit costs paired with the selected probability level. In some embodiments, the selected probability level is greater than one of the following: 60%, 70%, 80%, 90%, 95% and 99%. Therefore, for each selected probability, the plan selector 414 may select the plan with the lowest estimated upper limit cost among the plans that meet the probability level α.

[0147] In some embodiments, the lowest estimated cost is generated by comparing the respective costs of multiple plans, the respective costs being generated independently of the set of probability-cost value pairs. For example, the plan selector may only consider the estimated cost (eg, 504 or 707) when selecting a plan.

[0148] Fig.11 Another method for executing multiple queries according to an embodiment of the present invention is shown. In one embodiment, method 1100 can be executed by a computer similar to electronic device 1200. At 1102, the method may include: for each specified database query in a plurality of specified database queries (e.g., 420), generating multiple plans for potential execution by the DBMS (e.g., plans 402 and 706), wherein, when executed by the DBMS, each of the multiple plans is used to generate the same response to one of the multiple specified database queries (e.g., query 1 or query 702 in query 420). In some embodiments, the method 1100 may include the DBMS receiving multiple specified database queries. The method 1100 may also include: in response to the reception, the DBMS generates multiple plans for potential execution by the DBMS for each of the received multiple specified database queries.

[0149] At 1104, the method may also include, for each specified database query in a plurality of specified database queries, generating a set of probability-cost value pairs (e.g., 406, 508, and 708) for each plan in a plurality of plans, wherein each probability-cost value pair indicates an estimated upper limit cost of the plan, and a probability (e.g., α) that the cost of the plan is equal to or less than the estimated upper limit cost, if and when the plan is executed.

[0150] At 1106, the method may also include: (e.g., for each of a plurality of specified database queries) subsequently selecting: one or more of the plurality of specified database queries; and for each selected of the plurality of specified database queries, selecting a corresponding plan from a plurality of plans for executing the selected one of the plurality of database queries. For example, this may be applied to a case where a plurality of queries (e.g., 420) are input. In these cases, for each query in queries 420 (e.g., query 1, query 2, ...), a plan is selected from a plurality of plans, each of the plurality of plans generating the same response to its corresponding query. In some embodiments, the selection may be based at least in part on an evaluation of a set of probability cost value pairs for the plurality of plans and for the plurality of specified database queries.

[0151] At 1108 , the method may also include, for each specified database query in the plurality of specified database queries, causing the DBMS to execute each selected database query in the plurality of database queries using a corresponding plan in the plurality of plans.

[0152] In some embodiments, the selection may also be based on: an indication of a cost cap threshold, and an indication that as many of the specified database queries as possible are to be completed while complying with the cost cap threshold. For example, this may refer to selecting a plan based on a workload threshold 608. Plan selection may refer to Figure 8 Thus, for example, plan selection can be based on completing as many queries as possible within a duration or amount of time τ.

[0153] In some embodiments, making a selection may include: selecting one or more specified database queries from a plurality of specified database queries. In some embodiments, making a selection may also include: for each selected specified database query from a plurality of specified database queries, selecting a corresponding plan from a plurality of plans for executing a selected specified database query from a plurality of database queries, such that the selection substantially maximizes a combination of: the number of the plurality of specified database queries that can be completed with a probability of meeting or exceeding a specified probability level while adhering to a cost cap threshold; and the specified probability level. For example, a batch of queries is presented, and a subset of the batch of queries is selected, and for each query in the subset, a plan is selected, wherein the subset and the corresponding plan meet the objective.

[0154] In some embodiments, the selection may also be based on: an indication of a cost cap threshold, and an indication that at least a specified portion of a plurality of specified database queries are to be completed while complying with the cost cap threshold. This may refer to selecting a plan based on a workload percentile criterion 610, such as Fig. 9900 is further described in . In some embodiments, selecting may include selecting one or more specified database queries from a plurality of specified database queries.

[0155] In some embodiments, making a selection may also include: for each selected specified database query from a plurality of specified database queries, selecting a corresponding plan from a plurality of plans for executing a selected specified database query from the plurality of database queries, such that the selection substantially maximizes the probability of completing at least a specified portion of the plurality of specified database queries while complying with a cost upper limit threshold. Thus, for a given workload of m queries, labeled Q1,…,Qm, the goal may be to process or complete at least M (M∈[1,m]) queries, where M is a specified portion of the workload. The plan selector 414 may consider both the probability level and the estimated upper limit cost when making a plan selection to ensure that at least x% of the queries are completed within the duration with sufficient probability.

[0156] The embodiments described herein are not necessarily limited to DBMS applications, but rather, the embodiments may be used for other tasks with cost-based candidate evaluation. For example, the embodiments described herein may be applicable to selecting a data transmission path in a network. In one embodiment, path selection may be based on transmission speed, for example, the fastest path for data transmission may be selected. In one embodiment, the transmission speed of each candidate path between two points in the network may be predicted, and the uncertainty of the prediction for each candidate path may be quantified based on the embodiments described herein. In one embodiment, relevant information (i.e., predicted transmission speed and corresponding uncertainty information) may be presented to a user, and the user may select a path based on the user's preferences or customizations (e.g., a path selector based on risk attitude, similar to Figure 6 Risk attitude based plan selector in .

[0157] Accordingly, in one embodiment, a method may be provided that is based on a cost-based candidate evaluation task. The method may include calibrating a cost calculation component using a conformal technique to construct a cost distribution. The method may also include specifying a risk attitude. For example, a method similar to Figure 6 The method may further include: selecting a most desirable candidate option from a plurality of candidate options based on the specified risk attitude.

[0158] Fig.121 is a schematic diagram of an electronic device 1200 that can perform any or all operations of the above methods and features explicitly or implicitly described herein according to different embodiments of the present invention. For example, a computer equipped with network capabilities can be configured as the electronic device 1200. The electronic device 1200 can be used to implement one or more embodiments described herein. For example, the electronic device 1200 can be used to implement one or more components described herein, including but not necessarily limited to: a plan generator 704, a cost estimator 404, an uncertainty calibrator 412, a plan selector 414, or a DMBS (as the case may be). A plurality of separate electronic devices can be provided, each of which is used to implement one or more components described herein in a networked computing environment.

[0159] As shown, electronic device 1200 may include processor 1210, such as central processing unit (CPU) or special processor, such as graphics processing unit (GPU) or other such processor unit, memory 1220, non-transient mass storage 1230, input and output interface 1240, network interface 1250, and transceiver 1260, all of which are coupled by bidirectional bus 1270 communication. According to some embodiments, any or all of the elements in the elements may be utilized, or only a subset of the elements may be utilized. In addition, electronic device 1200 may include multiple instances of some elements, such as multiple processors, multiple memories or multiple transceivers. In addition, the elements of the hardware device may be directly coupled to other elements without the need for a bidirectional bus. In addition to processors and memories, other electronic components such as integrated circuits may be used to perform the required logical operations.

[0160] The memory 1220 may include any type of non-transient memory, such as static random access memory (SRAM), dynamic random access memory (DRAM), synchronous DRAM (SDRAM), read-only memory (ROM), or any combination thereof. The mass storage element 1230 may include any type of non-transient storage device, such as a solid-state drive, a hard disk drive, a disk drive, an optical drive, a USB drive, or any computer program product for storing data and machine executable program code. According to some embodiments, the memory 1220 or the mass storage device 1230 may record thereon statements and instructions executable by the processor 1210 for performing any of the above-mentioned method operations.

[0161] Embodiments of the present invention may be implemented using electronic hardware, software, or a combination thereof. In some embodiments, the present invention is implemented by one or more computer processors that execute program instructions stored in a memory. In some embodiments, the present invention is implemented in part or in whole in hardware, such as using one or more field programmable gate arrays (FPGAs) or application specific integrated circuits (ASICs) to quickly perform processing operations.

[0162] It should be understood that although specific embodiments of the technology have been described herein for illustrative purposes, various modifications may be made without departing from the scope of the technology. Accordingly, the specification and drawings are to be regarded only as an illustration of the invention as defined by the appended claims, and are intended to cover any and all modifications, variations, combinations or equivalents within the scope of the invention. Specifically, a computer program product or program element for storing machine-readable signals, or a program storage or storage device such as a magnetic wire, tape, disk or light, optical tape or optical disk is provided, within the scope of the present technology, for controlling the operation of a computer according to the method of the present technology and / or constructing some or all of its components according to the system of the present technology.

[0163] The actions associated with the methods described herein may be implemented as coded instructions in a computer program product. In other words, a computer program product is a computer readable medium on which software codes are recorded to perform the methods when the computer program product is loaded into a memory and executed on a microprocessor of a wireless communication device.

[0164] In addition, each operation of the method can be performed on any computing device (e.g., personal computer, server, PDA, etc.), and can be performed according to one or more program elements, modules or objects or a part of one or more program elements, modules or objects generated from any programming language (e.g., C++, Java, etc.). In addition, each operation or a file or object that implements each operation can be performed by dedicated hardware or a circuit module designed for this purpose.

[0165] Through the description of the above embodiments, the present invention can be implemented only by hardware, or by software and necessary general hardware platforms. Based on this understanding, the technical solution of the present invention can be embodied in the form of a software product. The software product can be stored in a non-volatile or non-transient storage medium, and the non-volatile or non-transient storage medium can be a compact disc read-only memory (CD-ROM), a USB flash drive, or a removable hard disk. The software product includes many instructions that enable a computer device (a personal computer, a server, or a network device) to perform the method provided in an embodiment of the present invention. For example, such execution may correspond to a simulation of the logical operation described herein. According to an embodiment of the present invention, the software product may additionally or alternatively include a plurality of instructions that enable a computer device to perform operations for configuring or programming a digital logic device.

[0166] Although the present invention has been described with reference to specific features and embodiments of the present invention, it is apparent that various modifications and combinations of the present invention may be formulated without departing from the present invention. Accordingly, the specification and drawings are only considered as an explanation of the present invention as defined by the appended claims, and are intended to cover any and all modifications, variations, combinations or equivalents within the scope of the present invention.

Claims

1. A method, characterized in that The method comprises a computer: generating a plurality of plans for potential execution by a database management system (DBMS), wherein each of the plurality of plans is operable to generate the same response to the same specified database query when executed by the DBMS; generating a set of probability-cost-value pairs for each plan of the plurality of plans, wherein each probability-cost-value pair indicates an estimated upper bound cost for the plan and a probability that the cost of the plan is equal to or less than the estimated upper bound cost if and when the plan is executed; selecting one of the plurality of plans based at least in part on an evaluation of the set of probability cost value pairs for the plurality of plans; The DBMS is caused to execute the selected one of the plurality of plans.

2. The method according to claim 1, characterized in that The cost of the plan is the amount of time it takes the DBMS to execute the plan, and the estimated upper limit cost of the plan is the estimated maximum amount of time it takes the DBMS to execute the plan.

3. The method according to claim 1, characterized in that Generating the set of probability-cost-value pairs includes generating, for each of the plans in the plurality of plans, a respective estimated cost for the plan.

4. The method according to claim 1, characterized in that: Generating the set of probability-cost-value pairs further comprises, for each of the plurality of plans: For each probability value in the probability value set: providing the probability value and the plan to an estimation module; subsequently receiving a cost value from the estimation module such that when the probability is equal to the probability value, the expected cost incurred by the plan is less than or equal to the cost value; A probability-cost-value pair in the set of probability-cost-value pairs is set to pair the cost value with the probability value.

5. The method according to claim 4, characterized in that The estimation module operates based on a conformal technique.

6. The method according to claim 5, characterized in that The conformal technique is pre-initialized based on a plurality of examples, wherein each example indicates a DBMS query plan, an estimated cost of the DBMS query plan, and an actual cost incurred by executing the DBMS query plan.

7. The method according to claim 1, characterized in that The selecting one of the plurality of plans is also based on user-generated preferences.

8. The method according to claim 1, characterized in that The selecting one of the plurality of plans comprises: presenting an output to a user, and receiving an input from the user, the input being responsive to the output, wherein the output indicates the set of probability-cost-value pairs, each of the probability-cost-value pairs indexing to a corresponding one of the plurality of plans; The selecting one of the plurality of plans is also based on a user-selected one of the plurality of plans, and the selecting one of the plurality of plans includes selecting the user-selected one of the plurality of plans.

9. The method according to claim 1, characterized in that: The selecting one of the plurality of plans includes selecting one of the plurality of plans wherein a probability that the plan has a cost less than or equal to an indicated maximum acceptable single query cost is highest.

10. The method according to claim 9, characterized in that The maximum acceptable single query cost is based on user-generated preferences.

11. The method according to claim 1, characterized in that: The method further includes operating in the first mode or the second mode according to the instruction; The first mode is characterized by higher cost certainty rather than lower cost, and the second mode is characterized by lower cost rather than higher cost certainty; Wherein, in the first mode, selecting one of the multiple plans includes: Choose a probability level greater than 50%; comparing the plurality of plans based on estimated ceiling costs corresponding to the plurality of plans among the estimated ceiling costs paired with the selected probability level; selecting a plan among the plurality of plans, wherein the plan has a corresponding lowest estimated ceiling cost among the estimated ceiling costs paired with the selected probability level; Wherein, in the second mode, selecting one of the multiple plans includes selecting one of the multiple plans, wherein the plan has a lowest estimated cost.

12. The method according to claim 11, characterized in that The indication is obtained from user generated preferences.

13. The method according to claim 1, characterized in that The selecting one of the plurality of plans comprises: Choose a probability level greater than 50%; comparing the plurality of plans based on estimated ceiling costs corresponding to the plurality of plans among the estimated ceiling costs paired with the selected probability level; A plan among the plurality of plans is selected, wherein the plan has a corresponding lowest estimated ceiling cost among the estimated ceiling costs paired with the selected probability level.

14. The method according to claim 13, characterized in that The selected probability level is greater than one of: 60%, 70%, 80%, 90%, 95% and 99%.

15. The method according to claim 11, characterized in that The lowest estimated cost is generated by comparing respective costs of the plurality of plans, wherein the respective costs are generated independently of the set of probability-cost value pairs.

16. A method, characterized in that The method comprises a computer: For each of the multiple specified database queries: generating a plurality of plans for potential execution by a database management system (DBMS), wherein each of the plurality of plans, when executed by the DBMS, is used to generate the same response to the one of the plurality of specified database queries; generating a set of probability-cost-value pairs for each plan of the plurality of plans, wherein each probability-cost-value pair indicates an estimated upper bound cost for the plan and a probability that the cost of the plan is equal to or less than the estimated upper bound cost if and when the plan is executed; subsequently selecting: one or more of the plurality of specified database queries; and for each selected one of the plurality of specified database queries, a corresponding one of the plurality of plans for executing the selected one of the plurality of database queries; wherein the selecting is based at least in part on an evaluation of the set of probability cost value pairs of the plurality of plans and the plurality of specified database queries; The DBMS is caused to execute each selected one of the plurality of database queries using the corresponding one of the plurality of plans.

17. The method according to claim 16, characterized in that The selection is further based on: an indication of an upper cost threshold, and an indication that as many of the plurality of specified database queries as possible are to be completed while complying with the upper cost threshold; The selecting comprises: selecting one or more specified database queries from the plurality of specified database queries; and for each selected one of the plurality of specified database queries, selecting the one corresponding one of the plurality of plans for executing the selected one of the plurality of database queries, such that the selection substantially maximizes a combination of: the number of the plurality of specified database queries that can be completed with a probability of meeting or exceeding a specified probability level while adhering to the cost upper limit threshold; and the specified probability level.

18. The method according to claim 16, characterized in that The selection is further based on: an indication of an upper cost threshold, and an indication that at least a specified portion of the plurality of specified database queries are to be completed subject to the upper cost threshold; The selecting comprises: selecting one or more specified database queries from the plurality of specified database queries; and for each selected one of the multiple specified database queries, selecting the corresponding one of the multiple plans for executing the selected one of the multiple database queries, such that the selection substantially maximizes the probability of completing at least the specified portion of the multiple specified database queries while complying with the cost upper limit threshold.

19. A device, characterized in that: The device comprises: processor; a non-transitory computer-readable memory having machine-readable instructions stored thereon, wherein when the machine-readable instructions are executed by the processor, causes the apparatus to: generating a plurality of plans for potential execution by a database management system (DBMS), wherein each of the plurality of plans is operable to generate the same response to the same specified database query when executed by the DBMS; generating a set of probability-cost-value pairs for each plan of the plurality of plans, wherein each probability-cost-value pair indicates an estimated upper bound cost for the plan and a probability that the cost of the plan is equal to or less than the estimated upper bound cost if and when the plan is executed; selecting one of the plurality of plans based at least in part on an evaluation of the set of probability cost value pairs for the plurality of plans; The DBMS is caused to execute the selected one of the plurality of plans.

20. A device, characterized in that: The device comprises: processor; a non-transitory computer-readable memory having machine-readable instructions stored thereon, wherein when the machine-readable instructions are executed by the processor, causes the apparatus to: For each of the multiple specified database queries: generating a plurality of plans for potential execution by a database management system (DBMS), wherein each of the plurality of plans, when executed by the DBMS, is used to generate the same response to the one of the plurality of specified database queries; generating a set of probability-cost-value pairs for each plan of the plurality of plans, wherein each probability-cost-value pair indicates an estimated upper bound cost for the plan and a probability that the cost of the plan is equal to or less than the estimated upper bound cost if and when the plan is executed; subsequently selecting: one or more of the plurality of specified database queries; and for each selected one of the plurality of specified database queries, a corresponding one of the plurality of plans for executing the selected one of the plurality of database queries; wherein the selecting is based at least in part on an evaluation of the set of probability cost value pairs of the plurality of plans and the plurality of specified database queries; The DBMS is caused to execute each selected one of the plurality of database queries using the corresponding one of the plurality of plans.

Citation Information

Patent Citations

  • Data query method and device

    CN110069522A

  • Query plan acquisition method and device and data query method and device

    CN110909023A

  • Model increment updating method and system

    CN112860303A

  • Query optimization method in distributed query engine and apparatus thereof

    US20170270162A1

  • Learning Optimizer for Shared Cloud

    US20190303475A1