Query Plan Cost Estimation With Runtime Node Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization systems in database management systems (DBMS) face inaccuracies in estimating data statistics for query plans, leading to sub-optimal query plans 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
Implement a system and method for runtime statistics feedback that collects and applies data statistics from previously executed query plans to improve the estimation of data statistics for subsequent query plans, using a dictionary to map node statistics and iteratively refine cost-based query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost-based query optimization uses estimated statistics for query plan selection, then query optimization can be performed efficiently, but estimation errors propagate and amplify leading to sub-optimal query plans
Solution Approach 1:
The patent implements feedback by collecting actual runtime statistics from executed query plans and using them to refine future cost estimations. The system monitors actual resource consumption (I/O, CPU, memory) and cardinalities during query execution, then feeds this information back to the query optimizer to improve subsequent cost-based decisions, creating a continuous improvement loop that reduces estimation errors over time
Solution Approach 2:
The patent performs preliminary action by pre-collecting and storing statistics during query plan execution before these statistics are needed for future optimization decisions. The system proactively gathers runtime data on intermediate result set cardinalities and resource usage during execution, preparing this information in advance so it can be immediately applied to improve the next query optimization cycle without waiting for explicit feedback requests
2Productivity
If query plan cache reuses cached query plans, then database management system efficiency improves, but inaccurate cost estimation causes selection of non-optimal cached plans
Solution Approach 1:
The system applies feedback by monitoring the actual performance of cached query plans when they are executed, comparing estimated costs versus actual resource consumption, and using this information to update statistics that affect future query plan selections from the cache, ensuring cached plans remain optimal as data distributions change
Solution Approach 2:
The patent changes parameters by dynamically updating statistics such as cardinalities and resource consumption metrics based on actual runtime observations. These parameter changes allow the query optimizer to adjust cost estimates for cached query plans, enabling more accurate selection among cached options even as underlying data characteristics evolve
3Measurement precision
If runtime statistics collection is implemented, then query plan cost estimation accuracy improves, but system overhead increases
Solution Approach 1:
The system implements self-service by having the query execution infrastructure automatically collect and report its own runtime statistics without requiring separate dedicated monitoring systems. The existing query execution machinery that already consumes resources to run queries also gathers statistical data as a byproduct, turning operational overhead into useful information gathering with minimal additional cost
Data Source
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.