Re: hashjoins vs. Bloom filters (yet again)

On 7/28/26 16:27, Matheus Alcantara wrote:
> 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.
>

Yeah, sorry about that.

> 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.
>

It's also possible some of the differences are due to differences in
schema, i.e. we may have different indexes, foreign keys, ... and that
may lead to different plans.

> 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.
>

I believe this is the estimation issue I mentioned in my post, when
explaining the changes in 0013 and 0014. Do you see the poor estimate
even with "my" v8?

> 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.
>

Not sure why you're apologizing, It's a complex feature / research,
progress takes time. I don't think we have set any deadlines or
anything. I'm grateful I can collaborate on it with someone.

regards

--
Tomas Vondra