Re: hashjoins vs. Bloom filters (yet again)

On 7/30/26 21:00, Matheus Alcantara wrote:
> On Tue Jul 28, 2026 at 5:58 PM -03, Tomas Vondra wrote:
> ...
>
> TPC-H scale 10, warm cache, max_parallel_workers_per_gather=0,
> work_mem=256MB; medians of 3 runs, enable_hashjoin_bloom off vs on:
>
> query off (ms) on (ms) delta
>
> Q3 107623 107418 -0.2%
> Q5 20307 18433 -9.2%
> Q7 21815 19933 -8.6%
> Q8 18834 18824 ~0%
> Q9 42602 40292 -5.4%
> Q10 24180 20850 -13.8%
> Q18 264641 144729 -45.3% (was a regression)
> Q21 35614 37960 +6.6% (was ~+41%)
>
> The interesting part is that the sizing change did more than fix Q8. Q18
> has the same shape (a filter probing all of lineitem), so cheaper probes
> flipped it from a regression into a large win, and Q21 came down from
> roughly +41% to +6.6%.
>
> Q21 is the only regression left, at +6.6% instead of ~41% on previous
> version. I'm wondering if some other heuristic discussed on the paper
> could help, e.g Heuristic 5 (explicitly skipping a filter whose build
> side is too large to keep the bitset in cache).
>
I've been running some benchmarks on my v8 patches (from 28/7) and the
most recent v9 (from 30/7), and I see a fairly consistent improvements
in the 1-5% range (v9 over v8). This is on top of the (substantial)
improvement compared to master, and it has to come just from using s
smaller/cheaper filter. So that's nice.

I did not see any massive improvements in the ~50% range, but I also did
not see Q18 using a plans with filters, so it probably depends on the
schema/indexes, etc.

It may also depend on scale, etc. Attached is a spreadsheet with results
from scales 10GB, 50GB and 100GB, and different statistics target (based
on the observation that some queries flip between plans, which makes it
difficult to compare results). The results are with target 10000, where
the plans are stable, particularly on 50/100GB.

regards

--
Tomas Vondra