Database Query Cost Estimation via Dynamic Learning Models
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern database systems face inefficiencies due to inaccurate estimation of computational costs for database queries, which can lead to excessive resource expenditure and inefficiencies, especially with complex queries and uncertain data distribution.
Innovation Solution
A computer-implemented method that generates learning models for each access plan of a query to predict execution costs, selects the lowest cost access path, and executes the query accordingly, improving estimation accuracy and resource allocation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional cost estimation methods are used for database queries, then the system is simpler to implement, but the estimation accuracy deteriorates leading to excessive resource expenditure
Solution Approach 1:
The patent replaces traditional mechanical cost estimation methods (based on fixed rules and heuristics) with machine learning models that dynamically predict query costs. These models are trained on historical query execution data to learn complex patterns and provide more accurate cost estimates without requiring explicit programming of estimation rules.
Solution Approach 2:
The system implements feedback loops where actual query execution costs are measured and used to retrain and refine the machine learning models. This continuous feedback mechanism improves estimation accuracy over time by adapting to changing data distributions and query patterns, while the feedback infrastructure adds systematic complexity to the system.
2Productivity
If multiple access plans are generated and evaluated, then the quality of query execution improves, but the computational overhead increases
Solution Approach 1:
The system performs preliminary actions by pre-training machine learning models on historical query data before actual query optimization occurs. This allows the models to be ready for rapid inference when queries need optimization, reducing the time required to evaluate multiple access plans while maintaining high-quality selections.
Solution Approach 2:
The patent changes the parameters used for access plan evaluation from traditional heuristic metrics to machine learning model predictions. By transforming the estimation parameters into learned features from historical data, the system can more efficiently compare multiple access plans and select optimal ones, improving query execution efficiency while managing optimization time through better parameter utilization.
Data Source
AI summary
A computer-implemented method to estimate computing cost of database queries. The method includes receiving a query. The method also includes generating one or more access plans for the query. The method further includes dynamically generating a learning model based on each of the generated access plans. The method includes estimating, by the learning model and for each of the one or more access plans, a predicted cost to execute the query. The method includes selecting, based on the predicted cost, a first access path associated with a lowest cost of the predicted costs and executing the query according the selected access path.