... BTW, another thought occurred to me yesterday: it seems like the
existing code hasn't thought through its behavior for multiple foreign
keys very carefully. That is, suppose we have both "A.J references B.K"
and "A.X references B.Y", as separate FKs not a single multicolumn FK.
The current code goes to some lengths to decide that one of these is
better than the other and then ignore the other. Why? Seems to me
that in such a case you want to behave more nearly as you would for a
multicolumn FK, that is discard all the join quals matched to either FK
in favor of a single selectivity estimate based on the number of rows in
the referenced table. Discarding only the A.J = B.K clause and then
multiplying by the independent selectivity of A.X = B.Y is surely just as
wrong as what we've historically done for multicolumn FKs. (Correcting
for nulls would take a bit of thought, but I wouldn't be surprised if it
ends up being the same as for the multicolumn-FK case, at least to within
the precision we can hope to get with the available stats for nulls.)
regards, tom lane