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

Hi Ilia,

Thanks for the v4.

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.

For 0002, I do not have a new semantic counterexample. The JOIN_INNER and equal-list-length restriction still looks reasonable to me.

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

Xiangxin