Database Query Optimizer Evaluation System
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management system performance assessment tools conflate query optimization and query execution, failing to provide useful information for evaluating the quality of the optimization process independently of the execution engine, leading to ineffective and inefficient query optimizers.
Innovation Solution
A system and method that submit database queries to a database management system, execute multiple test plans, and calculate metrics to assess the effectiveness of the query optimizer by comparing the execution times of plans selected by the optimizer to those of alternative plans, providing insights into the optimizer's quality and efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If existing database management system performance assessment tools (e.g., TPC benchmarks) are used to evaluate query optimizer quality, then the execution time of the selected plan is measured, but the assessment conflates query optimization quality with query execution engine capabilities, failing to provide useful information for evaluating the optimization process itself
Solution Approach 1:
The patent segments the evaluation process into two distinct parts: (1) evaluating the query execution engine independently by measuring execution time of plans generated by different optimizers, and (2) evaluating the query optimizer independently by analyzing the quality of plans it generates. This segmentation allows each component to be assessed on its own merits without conflation, directly resolving the measurement precision problem while preventing information loss about optimization quality.
Solution Approach 2:
The patent introduces an intermediary evaluation framework that acts as a mediator between the query optimizer and query execution engine. This framework generates multiple test plans using different optimizers, executes them through the same execution engine, and compares results to isolate optimizer quality from execution engine capabilities. The intermediary framework enables independent assessment of both components simultaneously.
2Extent of automation
If the query optimizer selects a plan based on estimated cost, then the plan selection process is automated, but there is no mechanism to verify whether the selected plan is actually optimal or near-optimal
Solution Approach 1:
The patent implements a feedback mechanism where the query execution engine provides actual execution time information back to the evaluation framework. This feedback is used to verify whether the plan selected by the automated query optimizer was indeed optimal or near-optimal. The framework compares the selected plan's execution time against other generated plans, providing empirical validation of the automation's reliability and enabling identification of cases where the optimizer failed to select the best plan.
3Productivity
If the search space of possible plans is constrained to improve optimization efficiency, then the optimization process becomes faster, but the quality of the selected plan may deteriorate due to limited exploration of alternative plans
Solution Approach 1:
The patent applies partial action by generating a representative subset of test plans rather than exhaustively exploring the entire search space. The evaluation framework generates multiple plans with varying degrees of optimization, executes them, and uses statistical analysis to determine whether the selected plan is optimal or near-optimal. This approach provides sufficient exploration to verify plan quality without the prohibitive cost of exhaustive search, balancing productivity and precision.
Data Source
AI summary
In some embodiments, a system is provided, comprising: memory storing instructions that, when executed, cause a processor to: submit a first database query; receive a runtime to execute the first database query using a plan selected by a query optimizer; receive runtimes to execute the first database query using a plurality of test plans; determine, based on the runtimes, a metric indicative of the effectiveness of the query optimizer; and cause the metric indicative of the effectiveness of the query optimizer to be presented to a user.