Query Optimizer Resource Estimation via Etalon Hypergraph Similarity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimizers face challenges in determining the best optimization algorithm for query optimization in diverse environments with varying resources, as existing methods struggle to pre-estimate complexity and resource consumption effectively, especially in environments with limited resources like mobile devices.
Innovation Solution
The system employs a query optimizer that selects an optimization algorithm based on available resources and pre-computed properties of algorithms, using a calibration process to estimate CPU time and memory requirements, and applies dynamic resource estimation through similarity measures with etalon queries to predict performance and resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a query optimizer uses complex optimization algorithms to improve query optimization quality, then the optimization performance improves, but the resource consumption (CPU time and memory) increases
Solution Approach 1:
The system performs preliminary actions by pre-computing properties of multiple optimization algorithms and storing them in a properties table before actual query optimization. This includes pre-calculating metrics such as the number of enumeration nodes, memory consumption, and optimization time for each algorithm, so that when a query arrives, the optimizer can quickly select an appropriate algorithm without performing expensive real-time analysis
Solution Approach 2:
The system changes parameters by using multiple different optimization algorithms with varying resource consumption characteristics. Based on the pre-computed properties and available resources, the system selects algorithms that optimize the balance between query optimization quality and resource consumption, rather than always using the most complex algorithm
2Measurement precision
If the system pre-estimates resource consumption for all optimization algorithms to enable better selection, then the algorithm selection accuracy improves, but the preprocessing time and memory usage increase
Solution Approach 1:
The system performs resource consumption estimation in advance during a calibration phase, storing the results in a properties table. This preliminary action includes computing metrics such as enumeration node count, memory usage, and optimization time for each algorithm on representative queries, so that during actual operation, the system can quickly retrieve and use these pre-computed values without repeating the expensive calculations
Solution Approach 2:
The system creates simplified representations (copies) of the resource consumption characteristics by pre-computing and storing key metrics in a properties table. Instead of performing full resource consumption analysis for each query, the system uses these pre-computed copies of algorithm properties to make rapid selection decisions
3Adaptability or versatility
If the query optimizer adapts to diverse platform environments with varying resources, then the versatility improves, but the complexity of the optimization process increases
Solution Approach 1:
The system applies local quality by determining available resources specifically for the query optimization process on the current platform, rather than assuming fixed resource availability. It uses these locally-determined resource characteristics to select optimization algorithms that are appropriate for that specific environment, allowing the system to adapt to diverse platforms from mobile devices with limited resources to server-class installations with abundant resources
Solution Approach 2:
The system introduces dynamics by making the optimization algorithm selection adaptive and flexible based on determined available resources. The properties table stores multiple algorithm options with their resource consumption characteristics, and the system dynamically selects from these options based on the current platform's resource availability, rather than using a fixed optimization approach
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for performing resource estimation for query optimization. An embodiment operates by generating a subplan for which an optimization process may be invoked, predicting performance and resource consumption for optimizing the subplan by measuring similarity between a hypergraph of the subplan and one or more etalon queries having known performance and resource consumption properties, selecting an algorithm for optimizing the subplan from a plurality of optimization algorithms based on the performance and resource consumption properties, and generating an optimized access plan using the selected algorithm.