Salient Sampling for Database Query Size Estimation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional sampling techniques are inadequate for accurately estimating the size of query results in database management systems, especially when combining data from multiple tables using equi-joins, as they fail to maintain the correct distribution and size of the join results.
Innovation Solution
The method employs hash-based sampling, where hash functions are applied to the domains of columns in multiple tables to select a subset of rows based on numeric thresholds, creating a sample database table that accurately represents the full dataset, even across multiple dimensions, and adjusts for tables with lower dimensions by padding with values from the domain.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If conventional sampling techniques are used to estimate query result sizes, then the estimation process is simple and fast, but the accuracy of the estimation deteriorates, especially for join queries
Solution Approach 1:
The patent segments the sampling process by applying different hash functions to different columns that participate in join operations. Each column is sampled independently using its own hash function, and the samples are then combined through equi-joins. This segmentation allows the system to maintain accurate distribution characteristics for each join column while keeping the overall process manageable.
Solution Approach 2:
The patent extends conventional single-column sampling to multi-dimensional sampling by applying hash functions across multiple columns simultaneously. The sampling process operates in a multi-dimensional space defined by the join columns, where each dimension corresponds to a column. This dimensional extension enables accurate estimation of join result sizes by preserving the distribution characteristics across all dimensions.
2Productivity
If a small sample size is used to reduce computation time, then the processing speed improves, but the estimation accuracy deteriorates
Solution Approach 1:
The patent applies partial action by sampling only the specific columns that are relevant to the join operations, rather than sampling entire tables. By focusing computational resources on the critical join columns and applying hash-based sampling only where needed, the system achieves accurate estimation with minimal processing overhead, balancing speed and accuracy.
3Measurement precision
If multiple hash functions are applied to multiple columns to improve sampling accuracy, then the estimation precision improves, but the computational complexity increases
Solution Approach 1:
The patent implements preliminary action by pre-computing and storing hash values for join columns in hash indexes. When query estimation is needed, the system retrieves pre-computed hash values rather than computing them on-the-fly. This preliminary preparation significantly reduces the computational time required for sampling while maintaining the accuracy benefits of multi-column hash-based sampling.
Data Source
AI summary
Salient sampling for query size estimation includes identifying two or more columns in a database table that have corresponding columns in one or more other tables. One or more hash functions are applied to domains of each of the identified columns. A first hash function is applied to a domain of the first column and a second hash function to a domain of the second column. A subset of the rows in the database table is selected. The selecting includes selecting rows in the database table where results of the first hash function meet a first numeric threshold and selecting rows in the database table where results of the second hash function meet a second numeric threshold. A sample database table corresponding to the database table is created. The sample database table includes the selected subset of the rows in the database table.