Cardinality Estimation Using Automatic Summary Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database management systems face challenges in generating accurate cardinality estimates for complex queries due to the assumption of independent predicates and the inability to handle complex comparison operators effectively, leading to inefficient query execution plans.
Innovation Solution
The use of statistics from automatic summary tables to improve cardinality estimates by applying adjustment factors that account for predicate dependence, allowing for more accurate cost estimation and optimal query execution plan selection.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional cardinality estimation methods are used based on independent predicate assumptions, then the estimation process is simple and fast, but the accuracy of cardinality estimates deteriorates for complex queries with correlated predicates
Solution Approach 1:
The patent pre-computes statistics on automatic summary tables that capture correlated predicates before query execution. These pre-computed statistics include cardinality information for predicate combinations, which are stored and reused during query optimization. This preliminary action eliminates the need for complex runtime calculations while improving accuracy.
Solution Approach 2:
The patent introduces automatic summary tables as intermediary structures that bridge the gap between simple independent predicate assumptions and complex correlated predicate realities. These summary tables contain pre-aggregated statistics that mediate between base table statistics and complex query requirements, providing accurate cardinality estimates without full complexity.
2Measurement precision
If statistics on automatic summary tables are collected and used, then the accuracy of cardinality estimates improves, but the overhead of collecting and storing statistics increases
Solution Approach 1:
Statistics on automatic summary tables are collected in advance during database maintenance windows or idle periods, rather than during query execution. This preliminary collection minimizes impact on query performance while ensuring accurate statistics are available when needed.
Solution Approach 2:
The system selectively collects statistics only on automatic summary tables that are actually used by queries, rather than maintaining statistics on all possible table combinations. Unused statistics are discarded, reducing overhead while preserving accuracy for relevant queries.
3Measurement precision
If complex comparison operators are handled with traditional methods, then the processing is straightforward, but the cardinality estimates for such queries become inaccurate
Solution Approach 1:
Automatic summary tables serve as intermediaries that pre-compute cardinality information for complex comparison operators. These tables capture the effects of complex predicates (such as range queries, pattern matching, and arithmetic comparisons) in advance, allowing the optimizer to reuse accurate results without re-implementing complex analysis logic.
Solution Approach 2:
The patent creates copies of data in automatic summary tables that are optimized for specific query patterns involving complex comparison operators. These copies contain pre-processed information that accurately reflects the selectivity of complex predicates, eliminating the need for the optimizer to directly handle the computational complexity of analyzing such operators.
Data Source
AI summary
A technique for optimizing execution of a query that accesses data stored on a data store connected to a computer. Statistics on one or more pre-defined queries are used to determine an optimal query execution plan for the query. In particular, improved cardinality estimates are generated for one or more query execution plans for the query using statistics of one or more pre-defined queries that vertically overlap the query. These cardinality estimates are used to make more accurate cost estimates, thus improving the likelihood of determining the optimal query execution plan.