Bloom Filter Query Planning for Cost-Aware Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems (DBMS) face challenges in selecting optimal query plans due to the lack of consideration of bloom filter (BF) costs during cost-based bottom-up query optimization, leading to suboptimal query plans.
Innovation Solution
Incorporate a bloom filter (BF) into the cost-based bottom-up query optimization process by generating query plans that include creating and applying BFs to data tables before joining, considering the costs of BF creation and application to improve the selection of optimal query plans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If bloom filter is applied to filter data before join, then the number of rows to be joined is reduced, but the cost of building and applying BF increases
Solution Approach 1:
The patent applies bloom filter before the join operation to pre-filter data rows, reducing the quantity of rows that need to be processed during the join. This preliminary filtering action is performed by creating a BF index on one table and using it to filter rows in the other table before the actual join, thereby reducing the search space and improving join efficiency.
2Measurement precision
If multiple query plans are generated with BF, then the optimality of query plan selection is improved, but the complexity of query optimization process increases
Solution Approach 1:
The patent segments the query optimization process into distinct parts: generating multiple candidate query plans with different BF configurations, evaluating each plan by estimating costs (including BF building and application costs), and selecting the optimal plan. This segmentation allows systematic exploration of different BF strategies while maintaining manageable complexity through structured evaluation.
Solution Approach 2:
The patent changes parameters such as whether to create BF, which table to create BF on, and whether to apply BF during the query optimization process. By varying these parameters across multiple query plans and evaluating them based on estimated costs (including BF operations), the system identifies optimal query plans that balance filtering effectiveness with operational cost.
3Measurement precision
If BF cost is considered in query plan selection, then the accuracy of cost estimation is improved, but the computational overhead for cost calculation increases
Solution Approach 1:
The patent incorporates feedback mechanisms where the cost-based optimizer evaluates query plans by estimating costs that include BF building and application operations. The system uses this cost feedback to guide query plan selection, continuously refining which plans are pursued based on their estimated computational and storage costs, thereby achieving accurate cost estimation while managing computational overhead through selective evaluation.
Data Source
AI summary
A method of incorporating a bloom filter (BF) into cost-based bottom-up query optimization includes: receiving a query including a request to join at least two data tables that include a first data table and a second data table; generating, in response to the request, at least two query plans that includes a first query plan including subplans to: create the BF based on the second data table, apply the BF to the first data table for filtering data of the first data table before the first data table is joined with another data table, and join the first data table with the another data table; submitting the at least two query plans to a cost-based bottom-up optimizer to obtain a target query plan; and providing, as a response to the received query, a set of data that is retrieved from a data retrieval system by executing the target query plan.