Database Partition Pruning Using Correlated Column Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in data processing and pruning operations due to the lack of effective methods for utilizing statistics to determine which partitions to include or exclude in query processing, especially in distributed environments, leading to unnecessary data loading and processing costs.
Innovation Solution
A system that maintains partition profiles with data range values for each column, using a statistics module to update these profiles during data manipulation operations and applying a buffer to reduce remote calls and improve efficiency in pruning partitions, allowing for accurate determination of necessary partitions for query processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database processes all partitions for every query, then complete data coverage is ensured, but data processing time and resource consumption increase significantly
Solution Approach 1:
The system pre-calculates and stores partition profiles containing data range information for each partition before queries are executed. This preliminary preparation enables the query processor to quickly determine which partitions are relevant without performing time-consuming analysis during query execution, thus reducing query processing time while maintaining complete data coverage.
Solution Approach 2:
The database table is divided into multiple partitions, each with its own profile containing data range information. This segmentation allows the system to evaluate partitions independently and selectively process only those partitions that are relevant to the query, avoiding the need to scan all partitions and significantly reducing processing time while ensuring all relevant data is accessed.
2Ease of operation
If the database loads all partition data into memory, then data accessibility is improved, but memory consumption and processing costs increase
Solution Approach 1:
Partition profiles with data range information are pre-computed and stored in a statistics cache before queries are executed. This preliminary action enables the system to quickly identify and load only the necessary partitions into memory based on query requirements, improving data accessibility while minimizing memory consumption by avoiding loading unnecessary partition data.
3Measurement precision
If the database updates partition statistics after every data manipulation operation, then data accuracy is maintained, but system performance and throughput decrease
Solution Approach 1:
Instead of updating partition statistics after every single data manipulation operation, the system updates statistics periodically or in batches. This partial action approach maintains sufficiently accurate partition profiles for query optimization purposes while significantly reducing the overhead on data manipulation operations, thereby maintaining system throughput and productivity.
4Measurement precision
If the database performs detailed statistical analysis during query processing, then partition selection accuracy is improved, but query execution time increases
Solution Approach 1:
The system pre-calculates partition profiles containing data range information and stores them in a statistics cache before queries are executed. This preliminary action provides the query processor with ready-to-use statistical information, enabling accurate partition selection without performing time-consuming statistical analysis during query execution, thus improving query execution time while maintaining partition selection accuracy.
Data Source
AI summary
A system includes a database having one or more tables having multiple partitions. The database includes memory modules to store the multiple partitions, a query processor, a data manipulation language (DML) processor configured to process operations to manipulate data in one or more of the partitions and a statistics module. For each partition, the statistics module maintains a partition profile and stores the partition profile in a statistics cache. The partition profile includes at least a first interval of data range values per column for one or more columns. The statistics module updates at least the first interval of a partition profile for a partition with new data range values responsive to the DML processor processing an operation to manipulate data in the partition. The database includes a partition pruning module that is configured to include partitions responsive to a received query based on the partition profile.