Including Bloom Filters in Bottom-up Optimization

TL;DR

This work introduces BF-CBO, a two-phase bottom-up cost-based optimization framework that integrates Bloom filters into query planning to improve join ordering and predicate transfer. By marking Bloom filter candidates, collecting build-side deltas in a first pass, and costed Bloom-filter sub-plans in a second pass, BF-CBO expands the optimizer's capability while applying heuristics to control search space. Empirical results on a 100 GB TPC-H workload show BF-CBO achieving a 32.8% latency reduction over Bloom-filter post-processing and a 52.2% reduction versus no Bloom filters, albeit with higher planner overhead. The approach is implemented in GaussDB and demonstrates promising improvements, with future work focusing on multi-node setups, multi-column Bloom filters, and adaptive runtime strategies to further enhance optimization-time vs. run-time trade-offs.

Abstract

Bloom filters are used in query processing to perform early data reduction and improve query performance. The optimal query plan may be different when Bloom filters are used, indicating the need for Bloom filter-aware query optimization. To date, Bloom filter-aware query optimization has only been incorporated in a top-down query optimizer and limited to snowflake queries. In this paper, we show how Bloom filters can be incorporated in a bottom-up cost-based query optimizer. We highlight the challenges in limiting optimizer search space expansion, and offer an efficient solution. We show that including Bloom filters in cost-based optimization can lead to better join orders with effective predicate transfer between operators. On a 100 GB instance of the TPC-H database, our approach achieved a 32.8% further reduction in latency for queries involving Bloom filters, compared to the traditional approach of adding Bloom filters in a separate post-optimization step. Our method applies to all query types, and we provide several heuristics to balance limited increases in optimization time against improved query latency.

Figures (6)

• Figure 1: The join order for TPC-H query 12 without including Bloom filters during bottom-up CBO (panel a) uses the table o:orders with 150M rows as the build side of a hash join (HJ). The l:lineitem table with an estimated 2.8M rows and actual 3.1M rows after local predicate filtering is broadcast (BC) to each of the 48 computing threads used in this example. A Bloom filter is not applied during post-processing in this case because the probe side is a foreign key column referencing an unfiltered primary key column on the build side; a Bloom filter cannot filter any probe side rows in this case. When including Bloom filters in CBO (panel b), the join-input ordering is reversed so a Bloom filter can be built on l:lineitem and applied to o:orders, significantly reducing its estimated row count to 6.4M rows. The reduced row estimate for o:orders lowers the cost of this join-input ordering. The planner then selects the lowest cost plan as depicted, which maintains the new join-input ordering and redistributes (RD) both sides. The query runs with 49.2% lower latency when including Bloom filters in costing.
• Figure 2: The cardinality of a scan on $R_0$ after applying a Bloom filter built from a column of relation $R_1$ (denoted by the dashed arrow labeled BF($R_1$)) depends on the set of relations on the build side of the hash join in which that Bloom filter is created. The cardinality of $R_0$ is $|R_0 \hat{\ltimes} R_1|$ when the build side consists solely of relation $R_1$, as in panel (\ref{['fig:bf_req_inner_a']}). So in this case, the Bloom filter sub-plan for $R_0$ has property $\delta$ = $\{R_1\}$; it requires the relation $R_1$ be joined to it for the Bloom filter to be resolved. When the build side includes both $R_1$ and $R_2$ (panel \ref{['fig:bf_req_inner_b']}), the cardinality of $R_0$ after the Bloom filter is applied becomes $|R_0 \hat{\ltimes} (R_1,R_2)|$, which may be lower than that in panel (\ref{['fig:bf_req_inner_a']}). In this case, the Bloom filter sub-plan for $R_0$ has property $\delta$ = $\{R_1,R_2\}$; it requires both relations $R_1$ and $R_2$ to be joined to it for its Bloom filter to be resolved. Note that the build (inner) side of the hash join is depicted on the right side in our convention.
• Figure 3: During the first bottom-up pass, the join of $R_0$ to $(R_1,R_2)$ may have been observed, as depicted in panel (\ref{['fig:rule_demo_a']}). Our approach creates a sub-plan for the scan of $R_0$ with a Bloom filter built from $R_1$ and an estimated cardinality of $|R_0 \hat{\ltimes} (R_1,R_2)|$. The sub-plan has property $\delta$$=\{R_1,R_2\}$ because both $R_1$ and $R_2$ are required on the build side of the hash join (HJ) for the cardinality of that sub-plan to be accurate. During the second bottom-up pass, we forbid the join between sub-plans as depicted in panel (\ref{['fig:rule_demo_d']}) because $R_2$ does not appear on the build side of the HJ, violating the cardinality assumptions on the sub-plan of $R_0$. Panel (\ref{['fig:rule_demo_b']}) shows an allowed join because, even though $R_2$ does not appear on the build side of the HJ, $R_0$ is being joined with a sub-plan that requires a BF built from $R_2$ (i.e., the sub-plan of $R_1$ has property $\hbox{$\delta$}{}=\{R_2\}$). So, any filtering that $R_2$ does on $R_1$ will effectively be transferred to $R_0$ through the Bloom filter applied to $R_1$ (i.e., BF($R_2$)). The incomplete sub-plan in panel (\ref{['fig:rule_demo_b']}) may be completed in the next level of bottom-up optimization by a HJ with $R_2$ as shown in panel (\ref{['fig:rule_demo_c']}), which we consider to be equivalent to the join order in panel (\ref{['fig:rule_demo_a']}) (ignoring any Bloom filter false positives).
• Figure 4: Join orders obtained for the running example in Section \ref{['sec:methods']}. Observed input row counts are shown in bold on the left (outer/probe) and right (inner/build) sides of each join; the planner's row estimates are italicized. Post-processing application of Bloom filters (panel \ref{['fig:running_ex_bfp1']}), does not apply any Bloom filters. BF-CBO (panel \ref{['fig:running_ex_bfp2']}) modifies the join order to apply a Bloom filter on $t1$, significantly reducing the observed input row counts of each join.
• Figure 5: Latencies for TPC-H queries are shown normalized to each query without any Bloom filters applied (No BF, shown as dashed line). Adding Bloom filters during plan post-processing (BF-Post, shown in blue) reduces query latency by 28.8%. Including Bloom filters during bottom-up cost-based optimization (BF-CBO, shown in orange) improves query latency by a further 32.8% relative to BF-Post. TPC-H queries that did not apply any Bloom filters are omitted.