Inferred Predicates for Column-Store Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in estimating and optimizing query execution plans, particularly for queries involving joins of column-store formatted tables, as they rely on pre-stored optimizer statistics which may not accurately reflect the distribution of values, leading to suboptimal resource usage and increased processing costs.
Innovation Solution
Incorporating inferred predicates based on column dictionaries of dictionary-compressed columns to modify the join condition, allowing for the generation of alternative query execution plans and providing selectivity, cost evaluation, and estimated row processing, thereby optimizing the query execution process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If pre-stored optimizer statistics are used to estimate query execution cost, then the query optimization process is simple and fast, but the accuracy of cost estimation deteriorates for column-store formatted tables
Solution Approach 1:
The system pre-computes and stores selectivity metrics and value distribution information in column dictionaries during data loading or maintenance operations. This preliminary action enables accurate cost estimation during query optimization without requiring complex real-time analysis, thus resolving the contradiction between accuracy and complexity.
Solution Approach 2:
Column dictionaries serve as an intermediary structure that bridges the gap between raw column-store data and query optimization needs. The dictionaries contain pre-analyzed statistics including value frequencies, distinct value counts, and selectivity metrics, allowing the query optimizer to make accurate cost estimates without directly analyzing the underlying data distribution.
2Reliability
If all possible query execution plans are generated and evaluated, then the optimal plan is found, but the processing time and resource consumption increase
Solution Approach 1:
The system changes the parameter used for cost estimation from generic pre-stored statistics to dictionary-based selectivity metrics. By using the intersecting dictionary information to calculate actual selectivity values, the system can more accurately predict the cost of different execution plans, allowing the optimizer to confidently select the best plan without exhaustively evaluating all possibilities.
Solution Approach 2:
The patent replaces the mechanical approach of generating and evaluating all possible execution plans with a more intelligent approach using dictionary-based cost estimation. The column dictionaries provide pre-computed value distribution information that enables the optimizer to evaluate plans more efficiently by substituting detailed data analysis with pre-analyzed metadata.
3Productivity
If joins of column-store tables are performed using conventional methods, then the join operation is straightforward, but the number of records processed is excessive leading to poor performance
Solution Approach 1:
Instead of processing all records in the join operation, the system uses column dictionary information to identify and process only the relevant subset of records. By leveraging pre-computed selectivity metrics and value distribution data from dictionaries, the join operation can skip unnecessary record comparisons and focus only on matching rows, thus reducing the quantity of records processed while maintaining join correctness.
Data Source
AI summary
A system includes reception of a query comprising a join operation on a first table and a second table and a join condition associated with the join operation, determination of a first table column of the first table and a second table column of the second table associated with the join condition, determination of an inferred predicate of the query, the inferred predicate associated with a first column dictionary of the first table column and a second column dictionary of the second table column, determination of a cost of using the inferred predicate to perform the join operation, determination of a plurality of query execution plans to execute the join operation using the inferred predicate, and determination of a cost of each of the plurality of query execution plans based on the cost of using the inferred predicate to perform the join operation.