Optimized Predicate Transfer: Efficient Pre-Filtering on Multi-Join Queries

XJTU 2024 CSUC Submission15 Authors

31 Mar 2024 (modified: 03 Apr 2024)XJTU 2024 CSUC SubmissionEveryoneRevisionsBibTeXCC BY 4.0
Keywords: database query, join optimization
Abstract: Predicate Transfer is a novel method that optimizes join performance by pre-filtering tables to reduce join input sizes. Inspired by the Yannakakis algorithm, which employs semi-joins to pre-filter acyclic queries, Predicate Transfer generalizes Bloom join to multi-table joins, significantly improving filtering benefits and supporting various join graph topologies. However, the current heuristics used in the predicate transfer phase may not always yield optimal results. This paper proposes an optimized predicate transfer algorithm that further enhances performance by incorporating transfer schedule cost estimation and pruning techniques. The algorithm introduces a metric called predicate effectiveness, which measures the ratio of tuples filtered out to tuples probed by a filter. By systematically estimating the cost of different transfer schedules and pruning ineffective predicates based on the effectiveness threshold, the algorithm selects an optimal transfer schedule that achieves faster performance and better adaptability to various query patterns and data characteristics.
Submission Number: 15