Parametric Query Plan Selection Using Cached Plans and Query Logs
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional query processing systems face inefficiencies and high costs due to the generation of customized execution plans for each query, especially in systems with limited processing resources, and caching execution plans introduces suboptimal performance for some queries.
Innovation Solution
A parametric query optimization system uses machine learning to train a plan selection model that predicts the best execution plan from a cache based on predicate selectivities, reducing the need for optimizer calls and optimizing execution performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If customized execution plans are generated for each query, then query execution efficiency is improved, but processing cost and time increase
Solution Approach 1:
The system pre-generates multiple candidate execution plans for parametric query templates before actual queries are executed. These pre-generated plans are stored in a plan cache, allowing the system to quickly select from pre-prepared options rather than generating plans from scratch for each query execution.
Solution Approach 2:
The system creates and stores copies of execution plans for different parameter value combinations in a plan cache. When a query is received, the system searches for matching cached plans based on predicate selectivities and parameter values, returning cached copies when matches are found, thereby avoiding repeated plan generation.
2Loss of energy
If execution plans are cached for all queries, then processing cost is reduced, but query performance decreases
Solution Approach 1:
The system generates and caches multiple execution plans with different optimization characteristics for the same parametric query template, each optimized for different predicate selectivity ranges. When a query is received, the system selects the most appropriate plan from the cache based on the actual predicate selectivities, ensuring locally optimal performance for each query's specific characteristics.
Solution Approach 2:
The system dynamically selects execution plans from the cache based on the actual predicate selectivities of incoming queries. Rather than using a single static plan for all queries, the system adapts its plan selection to match the specific characteristics of each query, choosing from multiple cached plans that are optimized for different selectivity scenarios.
3Productivity
If multiple execution plans are cached, then query performance is improved, but device complexity increases
Solution Approach 1:
The system segments the plan cache into multiple tables, with each table storing execution plans optimized for specific predicate selectivity ranges or query patterns. This segmentation allows for more organized storage and faster retrieval by matching query characteristics to appropriate segments, reducing the complexity of searching through all plans.
Solution Approach 2:
The system designs the plan cache to serve multiple functions: storing pre-generated execution plans, indexing plans by predicate selectivity ranges, and providing rapid retrieval based on query characteristics. The cached plans are structured to be universally applicable across different parameter value combinations while maintaining organization that simplifies management.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
The present disclosure relates to systems, methods, and computer-readable media for optimizing selection of a cached execution plan to use in processing a parametric query. For example, systems described herein involve training a plan selection model that makes use of machine learning to identify an execution plan from a set of pre-selected execution plans based on predicted cost of executing a query instance in accordance with the selected execution plan (e.g., relative to predicted costs of executing the query instance using other pre-selected execution plans). This application describes features related to lowering costs associated with selecting the execution plan in a way that will continue to be more accurate overtime based on training and refining the plan selection model.