Including Bloom Filters in Bottom-up Optimization

2025-05-05

AI Summary

This paper addresses two key challenges in bottom-up, cost-driven query optimization: the difficulty of integrating Bloom filters and the exponential explosion of the search space. We propose the first general-purpose Bloom-aware optimization framework, supporting Bloom-aware join-order selection and cross-operator predicate propagation for arbitrary SQL queries—overcoming prior limitations that restrict Bloom filter usage to snowflake queries or post-optimization phases. Our approach extends the cost model to account for Bloom filter selectivity, models Bloom filter propagation across operators, enables dynamic predicate pushdown conditioned on Bloom filter presence, and incorporates heuristic pruning to effectively curb search-space growth. Evaluated on TPC-H 100GB, our framework reduces query latency by 32.8% compared to conventional post-optimization methods, with only moderate overhead in optimization time, thereby significantly improving end-to-end query performance and optimization efficiency.

Technology Category

• Search and Optimization: Distributed Search
• Data Mining & Knowledge Management: Intelligent Query Processing
• Reasoning under Uncertainty: Stochastic Optimization

Application Category

• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs
• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems

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.

Problem

• Incorporating Bloom filters in bottom-up query optimization
• Limiting optimizer search space expansion efficiently
• Improving join orders with predicate transfer

Innovation

• Incorporating Bloom filters in bottom-up optimization
• Efficiently limiting optimizer search space expansion
• Balancing optimization time and query latency