Closed-loop predicate cost estimation for database query optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inefficiencies in optimizing request processing due to reliance on outdated or unreliable cost estimates for predicates, which can lead to suboptimal access plan selection and resource allocation.
Innovation Solution
A closed-loop method that creates a list of predicates, prunes those without actual costs, selects and processes access paths, and updates the data dictionary with actual predicate costs to adjust future estimates, ensuring accurate resource allocation and optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost models rely on mathematical constructs with estimates of row cardinalities based on assumptions about database table contents, then the system can select access plans, but the cost estimation accuracy deteriorates leading to suboptimal access plan selection
Solution Approach 1:
The system implements a feedback mechanism where actual predicate costs are captured during query execution and stored in the data dictionary. These actual costs are then used to adjust and refine future cost estimates, creating a closed-loop system that continuously improves estimation accuracy based on real performance data.
Solution Approach 2:
The system performs preliminary cost estimation using mathematical constructs and assumptions before query execution. After execution, actual costs are captured and used to adjust the preliminary estimates, preparing more accurate cost models for future query optimization.
2Ease of operation
If the system uses estimated predicate costs for access path selection, then processing can proceed without actual cost data, but resource allocation efficiency deteriorates
Solution Approach 1:
The system maintains processing continuity by using estimated costs when actual costs are unavailable, while simultaneously capturing actual predicate costs during execution. This feedback loop gradually replaces estimates with actual measurements, improving resource allocation efficiency without interrupting processing operations.
Solution Approach 2:
The data dictionary serves as an intermediary structure that stores both estimated and actual predicate costs. The optimizer queries this intermediary to select access paths, using actual costs when available and falling back to estimates when necessary, thereby maintaining operational efficiency while improving resource allocation.
3Measurement precision
If the system captures and stores actual predicate costs during query execution, then future cost estimation accuracy improves, but system complexity increases
Solution Approach 1:
The system uses the existing data dictionary structure for multiple purposes: storing schema information, statistics, and now actual predicate costs. This multi-functional use of existing infrastructure improves cost estimation accuracy without proportionally increasing system complexity.
Solution Approach 2:
The system automatically captures actual predicate costs during normal query execution and stores them in the data dictionary without requiring external intervention. This self-service mechanism improves future cost estimation accuracy while minimizing additional operational complexity.
4Speed
If predicates are pruned from the list when actual cost is not stored or cannot be estimated, then processing speed improves, but optimization quality deteriorates
Solution Approach 1:
The system performs preliminary pruning of predicates lacking cost data to maintain processing speed, while simultaneously capturing actual costs during execution. This preliminary action followed by cost capture ensures fast processing while progressively improving optimization quality as more actual costs become available.
Data Source
AI summary
Processing of a request is optimized. The request has one or more predicates. A list of the one or more predicates in the request is created and predicates for which an actual cost has not been stored or for which a cost cannot be estimated are pruned from the list. An access path for the each of the predicates is selected. The request is processed using the selected access paths producing one or more actual predicate costs. The one or more actual predicate costs are stored.