Database Query Optimization Using Reusable JIT Planner Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization systems in databases face inefficiencies due to the continuous generation of Just in Time (JIT) statistics, which consume resources and negatively impact user query execution performance, especially in enterprise data warehouses with high volumes of user queries.
Innovation Solution
Implement a mechanism to cache and reuse JIT statistics by analyzing JIT metadata, using machine learning models to predict query patterns and generate statistics in advance, thereby reducing the need for real-time JIT statistic queries and optimizing resource utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If JIT statistics are continuously generated for query optimization, then query planning accuracy is improved, but system resource consumption increases and user query execution performance deteriorates
Solution Approach 1:
The patent pre-generates JIT statistics during idle periods or in advance before queries are executed. This preliminary action allows the statistics to be ready when needed, improving query planning accuracy without causing resource contention during actual query execution. The statistics are computed beforehand and stored for later reuse.
Solution Approach 2:
Instead of continuously generating JIT statistics for every query, the patent implements periodic generation at intervals or during specific conditions (e.g., when statistics are stale or when resource availability changes). This periodic approach reduces overall resource consumption while maintaining adequate query planning accuracy.
2Measurement precision
If JIT statistics are generated on-demand for each query, then query optimization accuracy is improved, but query execution time increases
Solution Approach 1:
The system pre-computes and caches JIT statistics before queries are executed. When a query arrives, the optimizer can directly use the pre-computed statistics without performing time-consuming data scans, thus maintaining high selectivity estimation accuracy while minimizing query execution time overhead.
Solution Approach 2:
The patent creates copies of JIT statistics and stores them in caches or metadata structures. These copied statistics can be quickly retrieved and reused across multiple queries without re-computing, significantly reducing the time penalty associated with on-demand statistic generation.
3Reliability
If JIT statistics queries are executed frequently to maintain accurate statistics, then query plan quality is improved, but system throughput decreases
Solution Approach 1:
The patent implements periodic updates of JIT statistics based on data change detection or time-based intervals rather than frequent on-demand queries. This reduces the number of statistics generation operations, maintaining adequate query plan quality while preserving system throughput by minimizing interruptions to normal query processing.
Solution Approach 2:
The system monitors data modification patterns and uses feedback to determine when JIT statistics need updating. By detecting actual data changes and triggering statistics regeneration only when necessary, the system maintains high query plan quality while avoiding unnecessary statistics queries that would reduce system throughput.
Data Source
AI summary
Computer implemented methods, systems, and computer program products include program code executing on a processor(s) that obtains a user query. The program code selects just in time (JIT) statistics relevant for use by a cost optimizer to generate a plan file for executing the user query by determining, based on JIT metadata in a JIT statistics cache, if JIT statistics generated responsive to an earlier event are these JIT statistics, and based on determining that the JIT statistics generated responsive to the earlier event are these JIT statistics, selects these JIT statistics to generate the plan file. The program code utilizes the cost optimizer to generate the plan file based on these JIT statistics. The program code executes the user query in the target database based on the plan file.