Re: hashjoins vs. Bloom filters (yet again)

On 7/21/26 18:24, Tomas Vondra wrote:
> ...
>
> FWIW I'm running some tests on TPC-H, to see which queries would benefit
> from this, how much, if there are issues, etc. Should have some numbers
> later this week.
>
Here's some results for TPC-H scale 10 and 50 (so relatively small, but
manageable on my test systems). I used the same schema/indexes as usual.
There may be a better schema, but what matters is the impact of enabling
Bloom filters.

The PDF shows results for the 22 TPC-H queries, for combinations of a
couple parameters:

- scale: 10GB vs. 50GB
- cold vs. warmed-up
- buffered I/O vs. direct I/O
- 0 vs. 4 parallel workers

The last four columns compare the runs with filters enabled vs.
disabled. Green = good/faster.

Overall, the results are pretty good. There's 5-6 queries with ~25%
speedup, Q9 gets ~5x faster (10x with direct I/O). There's a bit of
difference depending on the scale, direct I/O, etc.

Of course, this mostly ignores the planning overhead. It's analytics
benchmark, with multi-second queries, and the queries are relatively
simple, so there's not that many possible filters.

But overall it seems like a good result for a WIP patch. There are no
visible regressions - it's all within noise, considering there was a
single run per query / parameters.

While working on this, I noticed a couple more issues in handling paths
with filters - matching then to joins. I noticed because it caused weird
plan changes in initial benchmark runs - the plan changed, but the new
plan did not have any Bloom filters. I'm attaching Q9 plan as an example
(notice the second plan has lower cost, but no filters).

The reason is we ended up with "unresolved" filters, which however do
affect the cardinality/cost. The result is still correct, but the plan
is inefficient (just like after misestimate).

Patches 0010-0012 should address those (but there may be more issues).

0010 - add_partial_path keeps the paths sorted by total path, so that
code can do linitial() to get cheapest total path, but this needs to be
without filter. 0002 broke that, so e.g. Gather might used paths with
filters. That'd trip an assert, but there may be other callers.

0011 - compute_join_expected_filters was not handling filters built on
joins correctly (per my earlier report)

0012 - adds some checks paths don't have bogus filters (that should have
been resolved or discarded earlier), and fixes another bug in
compute_join_expected_filters this found

Other that that, there are no differences compared to v6. There may be
more bugs in compute_join_expected_filters, I think it needs a bit of
cleanup / rework after allowing filters on joins.

These fixes might reduce the number of paths we keep propagating, and
thus reduce the planning time a little bit, but I guess it's not enough
to solve the path explosion problem.

regards

--
Tomas Vondra