Re: hashjoins vs. Bloom filters (yet again)

On Wed, Jun 3, 2026 at 6:07 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote
> Right, there's a general concept of a "filter", and Bloom filters are
> just one example of that. And maybe we could build other types of
> filters more suitable for the scan. But I think it'll still be tied to a
> hash join, because what other nodes / joins can build the filter?

Even if only hash joins build Bloom filters (or some other kind of
filters), those filters can be pushed through other joins. For
example, consider:

Hash Join
Nested Loop
-> Seq Scan on a
-> Index Scan on b
-> Hash
-> Seq Scan on c

If the hash join to table c builds a Bloom filter, it's perfectly
valid to test against that filter before doing the index probe into
table b.

The hard part is the planning. I feel like doing this at path-to-plan
time, after costing decisions have been made, is probably going to be
a tough sell. In a plan tree with many joins, pushing down the Bloom
filtering (or other filtering) decision to the lowest possible level
could drastically change the row count estimates, and thus the
costing, for a whole bunch of intermediate nodes, potentially meaning
that the best plan is something quite different from what we
estimated. On the other hand, if we try to do the "right" thing at
planning time -- meaning constructing separate paths for each possible
place where we could put the filter -- so that we can do costly
properly, that sounds super-expensive, which will be really bad for
queries that are supposed to have short execution times, and also not
great for cases where the runtime is longer but our estimates are
inaccurate, so that the extra planning effort is an expensive way of
deciding what to do at random.

i haven't hard time to read the paper to which you linked, but I do
sort of feel like knowing what the practical experience has been in
other systems might be important. Theoretically there are good
arguments for and against additional planning effort, but what happens
in practice is not clear to me.

--
Robert Haas
EDB: http://www.enterprisedb.com