Re: hashjoins vs. Bloom filters (yet again)

On Tue Jul 28, 2026 at 10:32 AM -03, Tomas Vondra wrote:
> Hi,
>
> Here's a v8, with a bunch of changes on top of v7 from last week. The
> changes are related mostly to generating the build join relids, so
> hopefully it's not too disruptive w.r.t. your work on selecting filter
> combinations, building paths etc.
>

I was just about to send a v8 with the building paths changes from v5
and the heuristic 4 from the SIGMOD paper with some benchmark results.
I'll work to port it to work on top on your v8 but I'll share the
benchmark results because I think that it's interesting to know.

Benchmark results:

TPC-H scale 10, warm cache, max_parallel_workers_per_gather = 0, vacuum
analyze beforehand.

Based on your benchmark runs I think your machine is much faster than
mine, so I ran v7 first and then v8 (my v8) on the same machine to
compare. Single runs turned out too unstable to compare, so the numbers
below are the median of 3 runs per query. The OFF/ON delta is the thing
to look at, not the absolute times.

Planning time stayed in a better shape compared with the old v5 patchset
in all runs (no path explosion) so I just put the execution time on
these tables.

v7 (median execution, seconds):

Q OFF ON exec delta
Q3 43.4 22.7 -48%
Q5 34.9 26.1 -25%
Q7 25.1 20.1 -20%
Q8 11.9 11.7 ~0% (noisy)
Q9 219 220 ~0% (very noisy, +/-30%)
Q10 30.7 23.4 -24%
Q18 57.9 72.1 +25%
Q21 31.6 49.3 +56%

v8 (median execution, seconds):

Q OFF ON exec delta
Q3 44.7 23.6 -47%
Q5 35.7 35.3 -1%
Q7 24.7 22.1 -11%
Q8 14.0 14.1 ~0% (noisy)
Q9 243 257 +6% (very noisy, +/-30%)
Q10 31.4 23.6 -25%
Q18 58.2 72.1 +24%
Q21 35.2 49.5 +41%

A few observations:

1. My v8 keeps the big wins on Q3 (-47%) and Q10 (-25%), same as v7.

2. The main v8-vs-v7 difference is that my v8 realizes fewer filters and
so wins less: Q5 goes from -25% (v7) to about flat (my v8), and Q7 from
-20% to -11%. v7 builds filters on more relations, while my v8 applies a
single combined filter set per relation with no fallback, so when that
combination can't be fully realized in the chosen join order the whole
filtered scan is dropped. That's the apply all at once (Heuristic 4)
trade-off on real queries; I think keeping a small number of fallback
combinations (the full set plus the single most-selective filter)
instead of only the maximal one would recover this, but I'm not sure,
maybe it's worth a try.

3. Q18 and Q21 regress under both v7 and my v8 by about the same amount
(Q18 ~+24%, Q21 +56% on v7 vs +41% on my v8), so they don't look specific
to my changes - they seem to be the feature interacting badly with my
hardware. Since you didn't see regressions on your Ryzen, it'd be great
if you could re-run these on your machine so we can compare.

4. Q9 is too noisy on my machine to conclude (the OFF baseline alone
swings 180-290s between runs). While looking at it, though, I noticed a
possible estimation issue:

Q9 builds a composite, multi-relation filter - (l_suppkey, l_partkey)
from partsupp JOIN part. Its selectivity is estimated at ~95 surviving
lineitem rows (of 60M) when it actually passes 3.28M - a ~35,000x
underestimate - which pushes the planner to a nested loop with an index
scan on orders (costed for 95 loops, runs 3.28M). It comes out of
find_interesting_bloom_filters for multi-rel build sides, and vacuum
analyze doesn't change it, so it looks like a formula issue rather than
stale stats. Restricting to single-relation build sides
(bloom_filter_pushdown_max_build_relids = 1) avoids it. I'm not sure how
much this contributes to the Q9 noise, but the estimate itself looks
wrong.

Sorry for the delay on posting this, I was trying to improve the
benchmark numbers but didn't managed do it properly yet, so I decided to
post as is anyway. I'm attaching my v8 (which is based on your v7) just
for the record but I'll read your new v8 patchset to try to share some
feedback and also port my current changes to work on top of your v8,
let's see if we can get better benchmark results.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com