On 7/28/26 21:50, Matheus Alcantara wrote:
> On Tue Jul 28, 2026 at 12:29 PM -03, Tomas Vondra wrote:
>>
>> ...
>>> Benchmark results:
>>>
>>> [ ... ]
>>
>> 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?
>>
>
> Good catch on the schema - I was missing FK constraints. Adding them
> seems to make things look better.
>
> With the FKs present, Q9 is now a win rather than the regression I
> reported: ON vs OFF is 55.5s vs 171.5s, ~3x faster, and stable across
> three runs. And the join cardinality estimates are now good - lineitem
> JOIN (partsupp JOIN part) estimates ~1.79M (actual 3.27M), instead of
> the ~70 I had before. So I think that 0013/0014 is doing their job on
> the join-rel size once the FK exists. I'll port my changes to work on
> top of this v8 and let's see if the improvement remain.
>
> The one thing that seems it's not fixed is the filter's own scan-level
> selectivity: (l_suppkey, l_partkey) still estimates rows=71 on the
> lineitem scan (of 60M) while 3,281,805 rows actually survive (94.5%
> rejected). That rows=71 drives a nested loop into orders (3.28M index
> probes, costed for 71):
>
> Nested Loop (rows=71) (actual rows=3281805)
> -> Seq Scan on lineitem (rows=71) (actual rows=3281805)
> Bloom Filter 1: keys=(l_suppkey, l_partkey) rejected 94.5%
> -> Index Scan using orders_pkey on orders (loops=3281805)
>
> It's still 3x faster than no filter because the filter discards 94.5% -
> so it "happens to pick the right plan", but I think that it's on luck
> rather than a correct estimate. So it looks like the FK rework fixed the
> join-rel cardinality, but the filter's selectivity still comes from the
> non-FK-aware path.
>
Is this with the v8 I posted? There's not the "expected" field for the
filter, so I guess it doesn't have the filter estimate fix from my v8
patches. And the estimates/actuals seem similar to what I saw before.
For the record, this is what I see on 10GB data set for this join
-> Seq Scan on lineitem (cost=0.00..1725034.76 rows=3275719 width=29)
(actual time=0.037..2947.777 rows=3260827.00 loops=1)
Bloom Filter 2: keys=(l_suppkey, l_partkey) expected=5.5%
checked=59986051 rejected=56725225 (94.6%)
Buffers: shared hit=576 read=1124567
Which seems pretty spot on. It might also depend on other GUCs affecting
the filters, I'm using
set bloom_filter_pushdown_max = 10
set bloom_filter_pushdown_max_build_relids = 10
set bloom_filter_pushdown_max_build_sets = 10
Maybe we're not matching the filters to joins correctly (relids equality
vs. subset).
regards
--
Tomas Vondra
