Hash Join Runtime Filter Selection Using Build-Phase Table Statistics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing Hash Join queries in SQL databases face performance degradation due to inaccurate determination of when to use Runtime filters, leading to increased resource consumption and decreased query performance, especially in distributed systems where global estimation inaccuracies are amplified.
Innovation Solution
A method to determine the suitability of Runtime filters by using actual table statistical information from the hash data table constructed during the build phase, allowing for accurate assessment of performance improvement and consumption, thereby optimizing Hash Join queries.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If Runtime filter is applied to all Hash Join queries, then query performance is improved for suitable cases, but resource consumption increases and performance deteriorates for unsuitable cases
Solution Approach 1:
The patent changes the parameter of filter applicability from a fixed blanket application to a dynamic decision based on estimated data distribution parameters (selectivity, correlation coefficient). By evaluating these parameters and comparing against thresholds, the system adapts the Runtime filter application to match the actual data characteristics, applying the filter only when parameters indicate it will be beneficial.
Solution Approach 2:
The patent introduces a feedback mechanism where the system estimates data distribution parameters, evaluates whether Runtime filter applicability conditions are met, and adjusts its behavior accordingly. The cost model provides feedback about the expected performance impact, allowing the system to make informed decisions about filter application rather than blindly applying it to all queries.
2Reliability
If Runtime filter is applied blindly without evaluation, then optimization effect is achieved for suitable queries, but additional performance consumption and system burden occur for unsuitable queries
Solution Approach 1:
The patent performs preliminary evaluation of data distribution parameters (selectivity, correlation coefficient) before applying the Runtime filter. By estimating these parameters in advance and comparing them against predefined thresholds, the system determines in advance whether the filter is likely to be beneficial, preventing performance degradation from premature or inappropriate filter application.
Solution Approach 2:
The patent transforms the decision-making process by introducing parameter-based evaluation (selectivity, correlation coefficient) that changes the system's behavior from blind application to conditional application. The parameters serve as indicators that trigger different optimization strategies based on the actual data characteristics.
3Ease of operation
If global estimation is used in distributed systems, then Runtime filter determination is simplified, but inaccuracy is amplified across multiple computing nodes
Solution Approach 1:
The patent segments the estimation process by computing data distribution parameters independently at each computing node based on local data characteristics. Instead of relying on a single global estimation that gets amplified across nodes, each node performs its own parameter evaluation (selectivity, correlation coefficient) on its local data, ensuring accurate local decisions without propagation of global estimation errors.
Data Source
Figure 1~2
Figure 3a~4
Figure 5
AI summary
The present disclosure provides a query method for a data table in a database, an apparatus, a computer device and a storage medium. The method includes: acquiring a data table query instruction, the data table query instruction is used to perform a Hash Join query operation on target join columns of a plurality of data tables in the database; constructing, in a build phase of the Hash Join, a hash data table according to tabular data corresponding to a target join column of a first data table among the plurality of data tables; determining, based on table statistical information of the hash data table, whether to construct a runtime filter for filtering a second data table in a probe phase of the Hash Join; and if so, determining a data query result with the constructed runtime filter based on the hash data table and the second data table.