Query Execution Plan Optimization via Micro-Partitioning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in maintaining responsive query execution times and efficient resource utilization due to the need to scan large volumes of data, which can lead to prolonged query response times and increased computing resource requirements.
Innovation Solution
The implementation of a network-based data warehouse system that utilizes micro-partitioning and metadata-based pruning to quickly identify and access relevant data, reducing the need to scan non-pertinent data and optimizing query execution through the responsive plan engine, which modifies plans based on statistics changes while imposing friction to avoid frequent changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional database systems scan large volumes of data to execute queries, then comprehensive data analysis is achieved, but query response times increase and computing resource requirements increase
Solution Approach 1:
The patent divides the database into micro-partitions at the storage layer and logical partitions at the query layer. This segmentation allows the system to process only relevant partition subsets rather than scanning entire tables, reducing query response time while maintaining analysis completeness through systematic partition evaluation.
Solution Approach 2:
The patent pre-computes and stores partition subset metadata including statistics about data distribution, value ranges, and partition relationships. This preliminary action enables the query optimizer to quickly identify relevant partitions without scanning irrelevant data, thereby reducing query response time while ensuring comprehensive data analysis.
2Measurement precision
If traditional database systems scan large volumes of data to execute queries, then comprehensive data analysis is achieved, but computing resource usage increases
Solution Approach 1:
By segmenting data into micro-partitions and organizing them into logical partitions, the system avoids processing irrelevant data segments. This reduces computing resource usage by focusing computational effort only on partition subsets that contain relevant data, while still achieving comprehensive analysis through systematic partition evaluation.
Solution Approach 2:
The patent extracts and utilizes partition subset metadata (statistics, data distribution information, value ranges) to identify and retrieve only the necessary partition subsets for query execution. This extraction approach eliminates unnecessary data scanning and processing, reducing computing resource usage while maintaining complete data analysis through targeted partition selection.
3Productivity
If the responsive plan engine frequently modifies query plans based on statistics changes, then query execution optimization is improved, but system stability deteriorates due to frequent plan changes
Solution Approach 1:
The patent implements dynamic query plan adjustment by monitoring statistics changes and modifying execution plans only when beneficial. The system dynamically evaluates whether plan changes will improve query performance before applying them, balancing optimization needs with system stability through conditional plan modification based on actual performance impact.
Solution Approach 2:
The patent applies friction to query plan changes by requiring performance improvement verification before accepting new plans. This preliminary anti-action prevents unnecessary or harmful plan changes by checking whether the new plan actually improves query execution, thereby maintaining system stability while still allowing beneficial optimizations.
Data Source
AI summary
The subject technology tracks a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query. The subject technology selects a first query plan among the plurality of query plans. The subject technology determines a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan. The subject technology attempts to execute a first query using the first query plan. The subject technology determines that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan. The subject technology executes the first query corresponding to the first query plan at a subsequent time using the second query plan.