Database Query Optimizer Using Actual Cost Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Prior art query optimizers rely on cost models for selecting query execution plans, which can be inaccurate due to underestimated or overestimated resource usage, leading to inefficient query execution and system performance issues.
Innovation Solution
A computer-implemented method that generates a query execution plan using estimated cost information, executes steps to retrieve data, recalculates actual cost information, and enforces resource allocation rules based on actual costs, especially when confidence in estimated costs is low, to ensure accurate and timely resource management.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If resource allocation rules are enforced based on estimated cost information, then query execution can proceed without delay, but the accuracy of resource usage prediction deteriorates leading to inefficient query execution
Solution Approach 1:
The system performs preliminary enforcement of resource allocation rules using estimated cost information before query execution begins. This allows queries to be evaluated against resource constraints in advance, enabling fast decision-making about whether to execute queries without compromising system resource limits.
Solution Approach 2:
The system implements feedback by comparing actual resource usage against estimated resource usage after query execution. This feedback loop allows the system to learn from prediction errors and improve future cost estimates, resolving the contradiction between speed and accuracy over time.
2Measurement precision
If resource allocation rules are enforced after query execution, then accurate actual resource usage data is obtained, but system performance deteriorates due to reactive rather than proactive management
Solution Approach 1:
The system performs preliminary enforcement of resource allocation rules using estimated cost information before query execution begins. This allows queries to be evaluated against resource constraints in advance, enabling fast decision-making about whether to execute queries without compromising system resource limits.
Solution Approach 2:
The system implements feedback by comparing actual resource usage against estimated resource usage after query execution. This feedback loop allows the system to learn from prediction errors and improve future cost estimates, resolving the contradiction between speed and accuracy over time.
3Productivity
If resource allocation rules are enforced based on underestimated resource usage, then more queries can be executed, but system resources are overconsumed leading to performance issues
Solution Approach 1:
The system applies a safety margin or cushion to estimated resource usage calculations when enforcing resource allocation rules. This beforehand cushioning prevents overconsumption of system resources by being conservative in resource allocation decisions, ensuring reliability even when estimates are optimistic.
Solution Approach 2:
The system implements feedback by comparing actual resource usage against estimated resource usage after query execution. This feedback loop allows the system to learn from prediction errors and improve future cost estimates, resolving the contradiction between speed and accuracy over time.
4Reliability
If resource allocation rules are enforced based on overestimated resource usage, then system resources are protected, but legitimate efficient queries are rejected
Solution Approach 1:
The system dynamically adjusts resource allocation decisions by considering both estimated and actual resource usage patterns. Rather than using fixed conservative thresholds, the system adapts its resource management policies based on learned patterns from feedback, allowing efficient queries to proceed while protecting against resource abuse.
Solution Approach 2:
The system implements feedback by comparing actual resource usage against estimated resource usage after query execution. This feedback loop allows the system to learn from prediction errors and improve future cost estimates, resolving the contradiction between speed and accuracy over time.
Data Source
AI summary
A computer-implemented method, apparatus and article of manufacture for optimizing a database query. A query execution plan for the database query is generated using estimated cost information; one or more steps of the query execution plan are executed to retrieve data from a database stored on the computer system. Actual cost information is generated for each of the executed steps, and the estimated cost information is re-calculated using the actual cost information. One or more resource allocation rules defined on one or more steps of the query execution plan are executed, based on the estimated cost information, wherein the resource allocation rules include one or more defined actions. The estimated cost information may be re-calculated using the actual cost information when confidence in the estimated cost information is low, but the estimated cost information may not be re-calculated when confidence in the estimated cost information is high. In addition, the estimated cost information may be re-calculated using the actual cost information, only when the step has one or more resource allocation rules defined thereon.