Incremental Database Statistic Estimation for Unique Value Counts
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face inaccuracies in query planning due to poor estimation of unique value counts, leading to long query response times and low throughput, especially in large databases with changing data distributions.
Innovation Solution
The implementation of an incremental statistical estimator that updates unique value count statistics using multiple sampled datasets, allowing for a larger effective sample size by combining initial and subsequent samples, and determining the degree of change in data distribution to decide on incremental updates, thereby improving estimation accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If sampling is regularly updated to reflect changes in database content, then the estimation accuracy of database statistics is improved, but the query response time increases and throughput decreases
Solution Approach 1:
The patent implements dynamic sampling strategies that adapt to data distribution changes. The system monitors changes in database content and adjusts sampling frequency and intensity accordingly, performing more frequent sampling when data changes are detected and reducing sampling when data is stable, thereby balancing accuracy improvement with throughput maintenance
Solution Approach 2:
The system performs sampling on partial datasets rather than complete database scans. By using representative samples and incremental update approaches, the system achieves sufficient estimation accuracy without processing the entire database, thus maintaining query throughput while improving estimation precision
2Productivity
If poor estimates are made of database statistics, then query execution may use suboptimal plans, but the immediate computational cost is low
Solution Approach 1:
The system performs preliminary sampling and estimation activities before query execution to establish accurate database statistics. By pre-computing statistical measures and maintaining up-to-date metadata about data distributions, the system ensures that query optimizers have accurate information available, leading to better query execution plans and improved throughput
3Measurement precision
If larger samples are used to improve unique value count estimation, then estimation accuracy is improved, but the processing time and computational resources increase
Solution Approach 1:
The patent divides the sampling process into multiple incremental stages rather than requiring a single large sample. The system collects samples in batches, processes them incrementally, and updates estimates progressively. This segmentation allows the system to achieve accurate unique value count estimates through multiple smaller processing operations rather than one time-consuming large-scale sampling operation
Data Source
AI summary
A technique includes determining a statistic for values associated with at least one column of a database based at least in part on a first sample of the values; and determining a degree of change in a second sample of the values relative to the first sample; and estimating a count of unique values for the column(s). The estimation of the count of unique values includes selectively incrementally updating the statistic using the second sample based at least in part on the determined degree of change; and basing estimation of the count at least in part on the updated statistic. The technique further includes processing a query to the database based at least in part on the count.