On Mon, Oct 19, 2026 at 4:08 PM zengxx <[email protected]> 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/
