Query Plan Selection Using Predictive Uncertainty
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques in relational database management systems (RDBMS) rely solely on expected optimality based on estimated costs, which can lead to non-robust query execution plans due to inaccurate parameter estimations and poor correlations between estimated costs and actual execution times.
Innovation Solution
The proposed solution involves training a model to estimate execution times and associated uncertainties for each query execution plan. This model uses predictive uncertainties to select a query execution plan based on suboptimality risk, allowing for the selection of plans with higher costs if their uncertainty is low, and pruning plans with high uncertainty to improve model performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a query optimizer selects the cheapest query plan based on estimated cost, then the expected execution efficiency is improved, but the robustness of the query execution plan deteriorates due to inaccurate parameter estimations
Solution Approach 1:
The patent transforms the query plan selection from a single-point cost estimation approach to a distribution-based approach by changing the parameters from expected cost alone to expected cost plus uncertainty metrics. The cost model now outputs a distribution characterizing both the expected cost and uncertainty, allowing the optimizer to select plans that are robust to parameter estimation errors while maintaining execution efficiency.
Solution Approach 2:
The patent introduces uncertainty metrics as an intermediary between the cost estimation model and the query plan selection process. This intermediary layer provides a buffer that accounts for estimation inaccuracies, allowing the system to evaluate not just the expected cost but also the reliability of that estimate, thereby selecting more robust query plans.
2Productivity
If the search space of candidate plans is reduced by pruning, then the model performance and selection speed are improved, but the risk of eliminating potentially optimal plans increases
Solution Approach 1:
The patent changes the pruning criteria from single-point cost comparisons to distribution-based comparisons that incorporate uncertainty metrics. By evaluating plans based on their cost distributions rather than just expected costs, the system can more reliably identify and prune dominated plans while preserving plans that may be optimal under different parameter realizations, thus reducing the risk of eliminating potentially optimal plans.
Data Source
AI summary
Aspects of the invention include techniques for robust query execution plan selection. A non-limiting example method includes training a model to estimate, for an input including a query and one or more plans, an execution time for each plan and a respective uncertainty of the execution time. A new query and a search space including a plurality of candidate plans are input to the model. Given an estimated distribution for the execution time of each candidate plan, a suboptimality risk is computed for each candidate plan. A plan of the plurality of candidate plans is selected according to a plan selection policy. The plan selection policy includes at least one of: selecting a plan by assuming that plans have higher costs proportional to an estimated standard deviation of their respective uncertainty; and selecting a plan with minimum risk using model uncertainty, data uncertainty, or a total uncertainty.