Re: Improve Hash/Merge Join estimate accuracy when all predicates are Hash/Merge clauses

On Mon, Oct 19, 2026 at 4:08 PM zengxx <xiangxin_zeng(at)qq(dot)com> wrote:
> I rebuilt it and found a simple case where 0001 can be less accurate than master.
>
> CREATE TABLE p(k int PRIMARY KEY);
> CREATE TABLE f(id int PRIMARY KEY, k int REFERENCES p(k), aux int);
>
> INSERT INTO p SELECT i FROM generate_series(1, 10000) i;
> INSERT INTO f SELECT i, i, 1 FROM generate_series(1, 10000) i;
> INSERT INTO f SELECT 10000 + i, NULL, 2 FROM generate_series(1, 10000) i;
> ANALYZE p;
> ANALYZE f;
>
> SET enable_hashjoin = off;
> SET enable_nestloop = off;
> EXPLAIN (COSTS ON)
> SELECT count(*) FROM f JOIN p ON f.k = p.k WHERE f.aux = 1;
> SELECT count(*) FROM f JOIN p ON f.k = p.k WHERE f.aux = 1;
>
> The global nullfrac for f.k is 0.5, but after f.aux = 1 all remaining rows have non-NULL f.k. Master estimates 10000 rows, matching the actual result. v4 estimates 5000.
>
> So using the global stanullfrac can still be inaccurate when NULLs are correlated with filters on other columns. I am not sure what the right fix should be: conditional nullfrac, extended statistics, or just documenting the limitation. I'd appreciate feedback from people more familiar with selfuncs.

Thanks for the example. This illustrates a tradeoff in the patch.

Master effectively assumes that every referencing row surviving the
filters has a match, regardless of NULLs in the FK columns. This can
overestimate the join size when some of those rows have NULL FK
values.

The patch accounts for those NULLs, but uses the table-wide null
fraction, assuming that FK nullness is independent of filters on other
columns. This improves the estimate for an unfiltered referencing
table and for filters where that assumption holds. However, it can
underestimate when a filter preferentially selects non-NULL FK values,
as in your example. Conversely, if a filter selects mostly NULL FK
values, it can still overestimate.

Skipping the correction whenever there are filters would avoid your
regression, but would also lose the improvement for independent
filters. Estimating a conditional null fraction could address the
correlation, but would require additional work beyond the current
patch.

So the patch trades the current assumption that all surviving
referencing rows match for an independence assumption about FK
nullness and filters. I should make that limitation explicit in the
patch description and code comments.

> I would especially appreciate feedback from people more familiar with selfuncs and cost estimate.

I also agree that feedback from someone more familiar with this part
of the selectivity estimator would be helpful, particularly on whether
this tradeoff is acceptable or whether the correction needs a more
conservative scope.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/