Permutation-Based Query Plan Generation for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems (DBMS) face inefficiencies in query optimization due to the limited scope of training data, which constrains the identification of more efficient query plans, as they are typically trained on historical data that only includes previously used methods, missing potentially more efficient approaches.
Innovation Solution
A permutation-based machine learning system that generates all possible query plans for a given query and adds these variations to the training corpus, allowing the DBMS to identify and select query plans that were never employed before, thereby expanding the scope of efficient query optimization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the DBMS is trained only on historical data from previously used query methods, then the training process is simple and fast, but the scope of identifiable efficient query plans is limited
Solution Approach 1:
The system performs preliminary generation of permuted query requests before the actual query execution. By pre-generating multiple permutations of query plans and storing them in the training corpus, the system prepares a broader range of potential solutions in advance, enabling the DBMS to identify more efficient query plans without limiting itself to only historically used methods
Solution Approach 2:
The system creates copies of original query requests by applying permutations to generate variant query plans. These permuted copies are added to the training corpus, allowing the DBMS to learn from multiple variations of query approaches rather than relying solely on historical data from single execution paths
2Reliability
If the DBMS evaluates all possible query plans, then the most efficient plan can be identified, but the processing time and resource consumption increase significantly
Solution Approach 1:
The system extracts and evaluates only the most promising query plans from the permuted set by using the DBMS optimizer to assess each permuted request. Rather than exhaustively evaluating all possible plans, the system selectively processes a manageable subset of permuted queries, extracting the most efficient options while avoiding the time cost of complete enumeration
Solution Approach 2:
The system performs partial evaluation by generating a comprehensive set of permuted query plans but evaluating only a representative subset during training. This partial action approach provides sufficient diversity for effective optimization without the excessive time cost of evaluating every single permutation, achieving a practical balance between thoroughness and efficiency
3Ease of manufacture
If the training corpus includes only historical query data, then the data collection process is simple, but the machine learning model lacks exposure to diverse query optimization strategies
Solution Approach 1:
The system merges historical query data from the database with newly generated permuted query requests to create an enhanced training corpus. By combining these two sources, the system maintains the simplicity of collecting historical data while adding diverse query optimization strategies through permutation, achieving both ease of data collection and improved model versatility
Solution Approach 2:
The permuted query generation mechanism serves multiple functions: it diversifies the training corpus, exposes the model to various query execution strategies, and maintains compatibility with historical data. This universal approach allows the same permutation technique to enhance training data quality across different query types and database scenarios
Data Source
AI summary
A DBMS query-optimization module receives a corpus of training data that contains data-access requests, such as SQL queries. Each request specifies data to be accessed but does not specify a query plan that the database should use to generate the requested data. The module identifies, in each received request, parameters, such as join methods and access methods, that can vary among query plans; and variables that cannot be assigned values until the query is actually processed. The system generates a set of queries, each of which implements a distinct query plan, that specify every viable permutation of values of the parameters and of the variables. The generated queries are added to the received corpus, which is forwarded to a machine-learning module in order to train the query-optimizer to select query plans that incur the lowest resource costs when servicing a particular type of query.