Query Plan Cost Estimation Through Runtime Statistics Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization techniques in database management systems (DBMS) face inaccuracies in estimating data statistics for query plans, leading to sub-optimal plan selection due to errors in estimating data statistics, especially for internal nodes, which can propagate and amplify estimation errors, resulting in inefficient resource usage.
Innovation Solution
Implementing a system and method for runtime statistics feedback that collects and applies accurate data statistics from previously executed query plans to improve cost-based query optimization by using a dictionary to map and propagate statistics across query trees, ensuring more precise cost estimation for alternative query plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost-based query optimization uses estimated statistics for query plan selection, then query optimization speed is improved, but measurement precision of data statistics deteriorates
Solution Approach 1:
The patent implements a feedback mechanism where actual execution statistics from previously executed query plans are collected and fed back to the query optimizer. This feedback loop allows the system to use accurate historical execution data to improve future cost estimates, resolving the contradiction between using fast estimates and achieving measurement precision.
Solution Approach 2:
The system performs preliminary actions by collecting and storing execution statistics during query plan execution. These pre-collected statistics are then available for future cost-based optimization decisions, eliminating the need to wait for actual execution to gather accurate data while maintaining optimization speed.
2Measurement precision
If accurate runtime statistics are collected and applied from previous query executions, then measurement precision of cost estimation is improved, but device complexity increases
Solution Approach 1:
The system implements self-service by automatically collecting, storing, and applying execution statistics without requiring external intervention. The query optimizer autonomously uses the accumulated runtime statistics to improve cost estimates, reducing the need for manual configuration or external data sources while maintaining high measurement precision.
3Productivity
If query plan cache stores compiled query plans for reuse, then productivity is improved, but reliability of optimal plan selection deteriorates due to estimation errors
Solution Approach 1:
The patent uses feedback from actual query plan executions to update and refine the statistics used for cost-based optimization. This continuous improvement loop ensures that cached query plans are selected based on increasingly accurate historical data, maintaining reliability while preserving the productivity benefits of query plan caching.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A computer implemented method can execute a first query plan for a query, obtain statistics for internal nodes of a first query tree representing the first query plan, receive a second query tree representing a second query plan for the query, search for a matching internal node of the first query tree for a selected internal node of the second query tree, and responsive to finding the matching internal node of the first query tree, apply the statistics for the matching internal node of the first query tree to the selected internal node of the second query tree for estimating cost of the second query plan during query optimization of the query. Related systems and software for implementing the method are also disclosed.