Cardinality Estimation Models for Multi-Column Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current cardinality estimation methods in databases suffer from low accuracy and limited applicability, particularly in scenarios involving multi-column queries, leading to suboptimal execution plans and reduced query performance.
Innovation Solution
A method and apparatus that utilize a coordinator node to perform data sampling, extract distribution features, and train a cardinality estimation model using operators like Bayesian networks, autoregressive models, and neural networks to enhance accuracy and efficiency of cardinality estimation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional cardinality estimation methods are used, then the database can execute queries with basic functionality, but the accuracy of cardinality estimation is low
Solution Approach 1:
The patent introduces a coordinator node as an intermediary component that separates the estimation model training and management from the query execution nodes. The coordinator node collects statistics, trains estimation models, and distributes them to execution nodes, thereby improving cardinality estimation accuracy without significantly increasing the complexity of individual database nodes.
Solution Approach 2:
The patent divides the database system into multiple independent nodes (coordinator node and execution nodes) that can operate autonomously. Each node has specific responsibilities: the coordinator handles model training and management, while execution nodes handle query processing. This segmentation allows the system to achieve high estimation accuracy through distributed computation without overwhelming any single component.
2Measurement precision
If learned models are used for cardinality estimation, then estimation accuracy improves, but the computational overhead and training complexity increase
Solution Approach 1:
The patent performs cardinality estimation model training in advance during idle periods or off-peak times, so that trained models are ready for deployment before actual query execution begins. The coordinator node collects statistics and trains models beforehand, then distributes the trained models to execution nodes for efficient query processing, eliminating training time from the critical query execution path.
Solution Approach 2:
The system implements automatic model training and updating mechanisms where the coordinator node autonomously collects statistics from execution nodes, trains estimation models, and distributes them back to the network. This self-service approach minimizes manual intervention and allows the system to maintain accurate models without requiring continuous external management, reducing the perceived training time for users.
3Productivity
If simple statistical information is used for estimation, then the processing is fast, but the accuracy is insufficient for multi-column queries
Solution Approach 1:
The patent transforms the estimation approach by changing from using simple aggregate statistics to utilizing learned models that capture complex multi-column relationships. The system collects detailed column statistics (null ratios, distinct values, value ranges) and uses these to train models that can accurately estimate cardinality for multi-column queries, while the models are efficiently deployed to maintain fast query processing speeds.
Solution Approach 2:
The patent replaces traditional mechanical cardinality estimation methods (based on simple statistical formulas and histograms) with learned models that use machine learning algorithms. These models substitute the rigid mechanical calculation approach with more flexible, data-driven estimation that can capture complex relationships in the data, improving accuracy for multi-column queries while maintaining acceptable processing speeds through efficient model inference.
Data Source
Figure 1~2
Figure 3~5
Figure 6
AI summary
A cardinality estimation method and an apparatus are provided. The method includes: selecting a target model type from a plurality of model types based on a target distribution feature and mapping relationship information, where the mapping relationship information includes a mapping relationship between the plurality of model types and a predicted distribution feature, and the target distribution feature is extracted from sample data collected based on an analysis instruction (S201); and obtaining, based on the target model type, a target model corresponding to the target model type, where the target model is used to perform cardinality estimation on a query instruction of a database (S202).