Selectivity Estimation Using Recursive Newton's Method
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query optimization methods, such as the maximum entropy method, face inefficiencies due to high computational complexity and slow convergence in modern database systems when estimating selectivity for complex predicate conjuncts, making them unsuitable for modern database management systems.
Innovation Solution
An efficient implementation of a Newton's method-based algorithm for entropy maximization is provided, utilizing a recursive procedure to avoid redundant computations and achieve lower computational complexity, which can be executed on both CPU and GPU, with additional performance benefits from multi-threaded GPU implementation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the maximum entropy method is used to estimate selectivity for predicate conjuncts, then measurement precision is improved, but device complexity and processing time increase due to high computational complexity
Solution Approach 1:
The patent segments the computation of selectivity estimates by organizing predicates into groups and using a hierarchical approach. Instead of computing all pairwise interactions simultaneously, the method divides the problem into smaller sub-problems that can be solved independently and then combined, reducing the overall computational complexity from O(n^2) to O(n log n).
Solution Approach 2:
The patent performs preliminary computations by pre-calculating selectivity estimates for individual predicates and storing them in a data structure. This preliminary action allows the entropy maximization algorithm to work with pre-processed data, avoiding redundant computations and reducing the complexity of the main estimation process.
2Measurement precision
If the iterative scaling algorithm is used to solve entropy maximization, then measurement precision is improved, but productivity decreases due to very slow convergence
Solution Approach 1:
The patent changes the parameters of the optimization algorithm by using a modified Newton's method with a specific Hessian matrix approximation. This parameter change in the algorithm's convergence behavior allows for faster convergence compared to the standard iterative scaling method, while still achieving the same measurement precision for selectivity estimates.
3Productivity
If Newton's method is used to solve entropy maximization, then productivity is improved compared to iterative scaling, but device complexity increases due to high computational complexity
Solution Approach 1:
The patent applies segmentation to the Hessian matrix computation by exploiting the structure of the entropy maximization problem. The Hessian matrix is computed in a segmented manner that avoids the full O(n^2) complexity, reducing it to O(n log n) by only computing necessary elements and using efficient matrix operations.
Solution Approach 2:
The patent substitutes the traditional mechanical iterative scaling approach with a Newton's method that uses gradient and Hessian information. This substitution replaces the slow mechanical iteration with a more efficient mathematical approach that converges faster while managing computational complexity through smart algorithm design.
4Measurement precision
If selectivity estimation is performed for complex predicate conjuncts, then measurement precision is improved, but loss of time increases due to excessive processing time
Solution Approach 1:
The patent performs preliminary computations by pre-calculating and storing selectivity estimates for individual predicates and their interactions. This preliminary action creates a cache of pre-computed values that can be reused during query optimization, avoiding redundant computations and significantly reducing processing time for complex predicate conjuncts.
Solution Approach 2:
The patent segments the computation of selectivity estimates by organizing predicates into groups and using a hierarchical approach. Instead of computing all pairwise interactions simultaneously, the method divides the problem into smaller sub-problems that can be solved independently and then combined, reducing the overall computational complexity from O(n^2) to O(n log n).
Data Source
AI summary
A system includes determination of a query comprising z predicates, determination of known selectivities of each of a set of conjuncts of the z predicates, and estimation of selectivities of all conjuncts of the z predicates based on the known selectivities and a recursive determination of Cx, where x represents selectivities of all complete conjuncts of the z predicates and C is a complete design matrix complete design matrix (ci,j)=1 if i⊆j, else 0, having indices [0, 2z−1].