Predicate-Weighted Histogram Generation for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inefficiencies in generating histograms for large data sets, leading to suboptimal query performance due to inaccurate representation of data distribution, especially when the number of buckets is capped, resulting in information loss and increased query processing time.
Innovation Solution
A system and method for generating weighted histograms by capturing query predicate literals, calculating weight vectors, and merging histogram rows based on cost functions that consider information loss and predicate weights, thereby optimizing the number of buckets and improving data distribution representation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the number of histogram buckets is increased to improve data distribution representation, then measurement precision is improved, but device complexity increases
Solution Approach 1:
The patent changes the parameter of histogram bucket weights by introducing predicate-based weighting. Instead of uniform buckets, each bucket is assigned a weight reflecting its importance to query predicates. This allows fewer buckets to achieve better representation accuracy by concentrating resolution on predicate-relevant regions while maintaining manageable complexity.
2Device complexity
If the number of histogram buckets is capped to reduce device complexity, then device complexity is reduced, but measurement precision deteriorates
Solution Approach 1:
The patent applies local quality by making different histogram buckets have different weights based on predicate relevance. Buckets corresponding to frequently queried predicate values receive higher weights and finer resolution, while less important buckets have lower weights. This non-uniform quality distribution achieves accurate representation with fewer total buckets.
3Device complexity
If traditional histograms are used without predicate weighting, then device complexity is reduced, but loss of information increases
Solution Approach 1:
The patent performs preliminary action by pre-calculating predicate weights and incorporating them into histogram generation. The system analyzes query predicates in advance, determines which data regions are most relevant, and weights histogram buckets accordingly before query execution. This preliminary weighting prevents information loss by ensuring critical data regions are accurately represented.
4Loss of information
If predicate-weighted histograms are generated to reduce information loss, then loss of information is reduced, but device complexity increases
Solution Approach 1:
The patent applies self-service by having the histogram generation process automatically analyze query predicates and determine optimal bucket weights without manual intervention. The system services itself by using its own query workload information to configure its internal representation, reducing information loss while managing complexity through automation rather than manual tuning.
Data Source
AI summary
Embodiments of the present invention provide a method, computer program-product, and system for generating predicate-weighted histograms in a database management system. Further, the methods, computer program-products and systems in accordance with the present invention generate histograms that are biased towards the predicate literals of the queries that are submitted to the database management system. The resulting histograms will improve query performance by generating histograms with greater resolution near predicate literals that represent the queries submitted to the database management system.