Re: hashjoins vs. Bloom filters (yet again) - Mailing list pgsql-hackers

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