Query Optimization Using Reinforcement Learning Agents
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Cost-based query optimizers in database management systems face challenges due to the need for a large number of accurate inputs, often resulting in suboptimal query execution as many inputs are not available or stale, leading to default values and assumptions.
Innovation Solution
The implementation of reinforcement learning in query optimization, where a query agent observes the execution of queries and modifies the access path based on feedback to incrementally advance towards the optimal execution plan without relying on accurate input values, allowing for iterative improvement of query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If cost-based query optimization is used, then query performance can be optimized through systematic evaluation of candidate access paths, but the system requires a very large number of accurate inputs that are often not available or stale
Solution Approach 1:
The patent implements feedback mechanisms where the query optimizer uses actual query execution results and performance data to continuously refine and update cost estimates and access path selections. This feedback loop allows the system to improve query performance over time without requiring complete and accurate input data, as the system learns from real execution outcomes.
Solution Approach 2:
The query optimizer performs self-adjustment by automatically updating its internal cost models and statistics based on observed query execution patterns and performance metrics. This self-service capability enables the system to maintain effective query optimization without external intervention to provide accurate input parameters, as it autonomously adapts to changing data characteristics.
2Ease of operation
If default values and assumptions are used for missing inputs, then the query optimizer can proceed with execution, but the accuracy of cost estimation deteriorates
Solution Approach 1:
The system performs preliminary actions by proactively collecting and analyzing query execution data and performance metrics before they are needed for optimization decisions. This advance preparation allows the optimizer to have accurate cost estimates ready when queries need to be executed, eliminating the need to rely on default values and assumptions.
Solution Approach 2:
The patent replaces the traditional mechanical approach of manually providing accurate input parameters with an automated learning system that infers cost estimates and access path preferences from observed execution patterns. This substitution transforms the optimization process from one requiring precise manual inputs to one that automatically adapts based on empirical data.
3Productivity
If the cost-based query optimizer loops through many candidate access paths, then thorough optimization is achieved, but the computational complexity and time required increases
Solution Approach 1:
The query optimizer applies partial action by evaluating only the most promising candidate access paths rather than exhaustively analyzing all possible paths. This selective approach achieves sufficient optimization thoroughness for practical purposes while significantly reducing the time and computational resources required, as the system focuses on the subset of access paths most likely to yield performance improvements.
Data Source
AI summary
According to an aspect, a computer-implemented method for improving query performance in a databased management system (DBMS) includes receiving, by a query optimizer of the DMBS, a query for execution. The method also includes creating, by the query optimizer, an initial access path for the query based on state and executing the query based on the initial access path. The method further includes observing, by a query agent of the DBMS, the execution of the query and modifying at least one of the state based on a determination by the query agent that a change to the initial access path would improve the execution of the query.