Partition-Level Selectivity Estimation for Query Plan Accuracy
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization methods in large data systems face inefficiencies due to misleading table-level statistics and the overhead of histogram management or data sampling, leading to prolonged processing times and increased costs.
Innovation Solution
Implement partition-granular selectivity estimation techniques to accurately calculate and utilize fine-grained statistics from table partitions, combining them for improved query plan optimization and reduced computational costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If table-level statistics are used for query optimization, then query compilation can proceed with available statistics, but the statistics become misleading and accuracy deteriorates as table size increases
Solution Approach 1:
The patent divides the table into multiple partitions and maintains separate statistics for each partition. This segmentation allows the system to capture data distribution characteristics at a finer granularity, preventing the loss of accuracy that occurs when aggregating all data into single table-level statistics. The query optimizer can then combine partition-level estimates to achieve accurate overall selectivity estimation even for large tables.
2Measurement precision
If histograms are built to improve selectivity estimation accuracy, then measurement precision improves, but the complexity of building and managing histograms increases
Solution Approach 1:
The patent extracts only the essential statistical features needed for selectivity estimation from each partition (such as min, max, null counts, and distinct value counts) rather than building complete histograms. This extraction approach captures the necessary information for accurate estimation while avoiding the complexity of full histogram construction, maintenance, and updates.
3Measurement precision
If data sampling is used to obtain statistics, then measurement precision can improve, but additional queries are required which increases overhead cost and time
Solution Approach 1:
The patent computes partition-level statistics during data loading or ETL processes, performing the statistical analysis in advance before queries are executed. This preliminary computation ensures that accurate statistics are already available when query compilation occurs, eliminating the need for additional sampling queries and reducing query compilation time.
4Productivity
If partition-granular selectivity estimation is implemented, then query processing efficiency improves, but the complexity of calculating and combining partition statistics increases
Solution Approach 1:
The patent merges partition-level selectivity estimates by applying simple combinatorial logic based on the query predicate and partition characteristics. This combining process is designed to be computationally efficient, using straightforward mathematical operations rather than complex algorithms, thereby maintaining query processing efficiency while achieving accurate results.
Data Source
AI summary
A query engine can use partition-granular level statistics to optimize query performance. A query can reference a table with a plurality of partitions and include a predicate. A partition-granular selectivity estimate for the predicate can be generated based on statistics stored regarding the plurality of partitions of the table. A query plan can be generated based on partition-granular selectivity estimate to optimize query processing.