(1) The current patch only does the trick when the FK matches the conditions perfectly - when there are no missing columns (present in the FK, not covered by a condition).
Hi Tomas,
I did glance at this patch a while back, but just thinking on it again.
I think if you find any quals that are a part of *any* foreign key between the 2 join tables, then you should be never assume these quals to reduce the number of rows. I believe this should be the case even if you don't fully match all columns of the foreign key.
If we modify your example a little, let's say your foreign key between fact and dim is made from 3 columns (a,b,c)
If we do:
EXPLAIN SELECT * FROM fact f JOIN dim d USING (a,b);
Then we should always (under normal circumstances) find at least one matching row, although in this case since the join qual for c is missing, we could find more than 1 matching row.
Without digging too deep here, I'd say that the best way to do this would be to either have calc_joinrel_size_estimate() build a list of restrictinfo items of all quals that are NOT part of any foreign key and pass that trimmed list down to clauselist_selectivity() for selectivity estimates. Or perhaps a better way would be just to teach clauselist_selectivity() about foreign keys. Likely clauselist_selectivity() would just have to skip over RestrictInfo items that are part of a foreign key.