Database Cardinality Estimation Using Pre-computed Functions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing cardinality estimation algorithms in database systems produce large errors for real-world datasets, leading to slow queries and unpredictable performance due to their inability to accurately predict the number of rows returned by queries.
Innovation Solution
The database engine employs a training or learning phase in offline mode using various table sizes and samples to improve the accuracy of cardinality estimates, which are then used during real-time query processing. This involves parsing queries, building operator trees, and applying optimization techniques like join reordering based on pre-computed functions and Guaranteed Error Estimator bounds to generate more efficient execution plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If existing cardinality estimation algorithms are used, then query execution can proceed, but large errors are produced leading to slow queries and unpredictable performance
Solution Approach 1:
The system performs preliminary actions by pre-computing cardinality estimation functions and storing them in a cache during system initialization or maintenance periods. These pre-computed functions are derived from analyzing historical query patterns and table statistics, allowing the query optimizer to quickly retrieve accurate cardinality estimates without performing complex calculations during query execution, thus improving both accuracy and performance
Solution Approach 2:
The system changes parameters by using multiple different cardinality estimation functions selected based on the specific query characteristics, table types, and data distributions. Instead of using a single fixed algorithm, the system dynamically selects appropriate estimation functions (e.g., linear regression, polynomial fitting, or machine learning models) that are best suited for the current query context, thereby improving estimation accuracy across diverse workloads
2Productivity
If complex optimization techniques are applied to improve query performance, then execution efficiency increases, but the complexity of the query optimization process increases
Solution Approach 1:
The query optimization process is segmented into distinct phases: initial cardinality estimation using pre-computed functions, join order determination based on these estimates, and execution plan generation. The cardinality estimation itself is segmented into multiple selectable functions that can be independently optimized and cached. This segmentation allows each component to be optimized separately and reduces the overall complexity by breaking down the monolithic optimization process into manageable, independent modules
Solution Approach 2:
Complex computational work is performed in advance during system initialization, maintenance windows, or lazy evaluation moments when the database is not under heavy query load. Pre-computed cardinality estimation functions are calculated offline using historical data and stored in caches. During actual query execution, the optimizer simply retrieves these pre-computed values, dramatically reducing the computational complexity and execution time of the optimization process
Data Source
AI summary
A method implements optimization of database queries by computing domain cardinality estimates. A client sends a database query to a server. The method parses the query to identify data columns. For each of the data columns, the method computes a lower bound and an upper bound of distinct data values using a pre-computed table size. The method also computes a patch factor by applying a pre-computed function to a ratio between a number of distinct data values that appear exactly once in a data sample and a number of distinct data values in the sample. Based on the patch factor, the lower bound, and the upper bound, the method computes an estimate of distinct values for each of the data columns. The method subsequently generates an execution plan for the query according to the computed estimates, executes the execution plan, and returns a result set to the client.