Query Execution Plan Stitching for Cost Reduction
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In large-scale database systems, query execution plans frequently regress due to changes in indexes and statistics, making it challenging to debug and correct query execution plan regressions, especially in automated tuning environments, where reverting to cheaper previous plans may not capture more efficient combinations of subplans.
Innovation Solution
The approach involves stitching efficient subplans from multiple previously-executed query execution plans to construct a new, cheaper query execution plan, using a constrained search space and dynamic programming to combine subplans with observed execution costs, thereby reducing execution costs significantly.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If the query optimizer continuously creates new query execution plans using newly-created indexes and statistics, then the query execution plan utilizes the latest optimizations, but the execution cost may significantly increase compared to previously-executed plans (plan regression)
Solution Approach 1:
The system performs preliminary actions by maintaining a history of previously-executed query execution plans and their associated execution costs. Before accepting a new query execution plan from the optimizer, the system proactively checks whether cheaper alternative plans exist in the historical record that are still valid for the current query, thereby preventing plan regression before it occurs.
Solution Approach 2:
The system implements feedback mechanisms by continuously monitoring execution costs of query execution plans and using this information to guide future plan selection. The feedback loop compares the execution cost of newly-generated plans against historical plans and automatically selects the cheaper alternative when detected, creating a self-correcting system that adapts to changing database conditions.
2Loss of energy
If the system reverts to cheaper previously-executed plans when plan regression is detected, then execution cost is reduced, but more efficient query execution plans may be missed that could be constructed by combining subplans from multiple previous plans
Solution Approach 1:
The system segments the query execution plan into individual subplans (operators) and evaluates each subplan independently. By breaking down the complete query execution plan into its constituent parts, the system can selectively combine efficient subplans from different historical plans to construct a new, optimized query execution plan that achieves better performance than any single previous plan.
Solution Approach 2:
The system merges efficient subplans from multiple previously-executed query execution plans to construct a new composite query execution plan. This combining process selects the best subplan for each operator position from the historical record and assembles them into a coherent execution plan, achieving synergistic effects that surpass individual plans.
3Extent of automation
If automated plan correction is implemented to detect and correct query execution plan regressions, then plan regression issues are addressed automatically, but the risk of making execution costs worse due to automated correction actions increases
Solution Approach 1:
The system performs preliminary validation by checking whether candidate query execution plans from historical records are still valid for the current database configuration before applying them. This preliminary check includes verifying that required indexes and statistics exist, thereby preventing the application of invalid plans that could cause execution failures or unexpected behavior.
Solution Approach 2:
The system uses disposable, low-risk correction strategies by selecting query execution plans that have already been successfully executed in the past rather than generating entirely new plans. These historical plans serve as proven, low-risk alternatives that have demonstrated their validity and performance in similar conditions, reducing the risk associated with automated correction.
Data Source
AI summary
Systems, methods, and computer-executable instructions for creating a query execution plan for a query of a database includes receiving, from the database, a set of previously executed query execution plans for the query. Each previously-executed query execution plans includes subplans. Each subplan indicates a tree of physical operators. Physical operators that executed in the set of previously-executed query execution plans are determined. For each physical operator, an execution cost based is determined. Invalid physical operators from the previously-executed query execution plans that are invalid for the database are removed. Equivalent subplans from the previously-executed query execution plans are identified based on physical properties and logical expressions of the subplans. A constrained search space is created based on the equivalent subplans. A query execution plan for the query is constructed from the constrained search space based on the execution cost. The constructed query execution plan is not within the previously-executed query execution plans.