Auxiliary Query Optimizer for High-Quality Plans Without Runtime Delay
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern database systems face challenges in optimizing complex queries due to the exponential growth of potential query plans, leading to performance issues and computational constraints, making it difficult to improve query performance without degrading existing systems.
Innovation Solution
Implementing an auxiliary query optimizer framework that generates and evaluates query plans outside the production environment, allowing for more thorough optimization using different techniques and settings, which can be decoupled from the inline query optimizer, thus reducing the risk of performance degradation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If an inline query optimizer is used to process queries in production, then query execution time is kept short, but the quality of query optimization is limited due to time constraints
Solution Approach 1:
The query optimization process is divided into two separate phases: a preliminary optimization phase that runs outside production hours to generate multiple candidate query plans, and a final selection phase that chooses the best plan quickly during production. This segmentation allows thorough optimization without impacting real-time query execution.
Solution Approach 2:
Query optimization is performed in advance during off-peak hours or in a preliminary stage, generating and evaluating multiple potential query plans before they are needed in production. This preliminary action allows the system to prepare optimized plans ahead of time, so that when queries are executed in production, the optimization is already complete.
2Productivity
If query optimizer changes are made to improve performance, then query performance may improve, but the risk of performance degradation increases
Solution Approach 1:
The system creates and maintains a library of pre-optimized query plans as reusable templates. Instead of modifying the production query optimizer directly, new optimization techniques are first implemented as separate, testable components that generate alternative plans. These alternative plans are then evaluated and selected, allowing improvements to be applied without directly altering the stable production system.
Solution Approach 2:
An intermediary layer is introduced between the query parser and the query executor, which serves as a buffer for testing and evaluating new optimization techniques. This intermediary allows experimental optimizations to be developed, tested, and validated in isolation before being integrated into the production system, thereby reducing the risk of performance degradation.
3Manufacturing precision
If the search space for query plans is expanded to consider more possibilities, then optimization quality improves, but computational complexity increases exponentially
Solution Approach 1:
The system performs extensive search and evaluation of potential query plans during off-peak hours or in a preliminary optimization stage, where computational resources are available and time constraints are relaxed. By completing the heavy computational work ahead of time, the system can generate high-quality optimized plans without impacting production performance.
Solution Approach 2:
The complex query plan generation and evaluation process is extracted from the production query execution path and placed in a separate preliminary optimization stage. This extraction allows the heavy computational tasks to be performed independently during off-peak hours, separating the optimization complexity from the execution time requirements.
Data Source
AI summary
Techniques and solutions are provided for improved query optimization, including for sub-portions of a query plan. A query is submitted to an inline query optimizer and at least one auxiliary query optimizer. The query is optimized by the inline query optimizer and the auxiliary query optimizer. A query processor can evaluate costs associated with query plans produced by the inline query optimizer and the auxiliary query optimizer and select a plan for execution that is most performant.