David Rowley <david.row...@2ndquadrant.com> writes: > I think your wires are crossed to what this patch actually does. A > unique index could only prove that no more than 1 rows exists. This > goes to prove that exactly 1 exists, then will reduce that estimate by > any other join conditions which were not matched to a foreign key.

BTW, I thought some more about this, and I believe the patch is a few bricks shy of a load in this respect. An FK constraint will enforce that a referencing row matches exactly one referenced row only if all the referencing columns are marked NOT NULL. If any nulls are allowed, then we are back to the unique-index situation, ie we can only conclude that there is at most one matching row. I do not think this means that we must dial the patch back to only considering FKs that have NOT NULL on all their columns. If we could estimate the fraction of referencing rows that have any nulls, we could still arrive at a selectivity estimate that's better than we get when disregarding the FK altogether: instead of 1/num_referenced_rows it'd be fraction-of-referencing-rows-without-nulls/num_referenced_rows, since the rows containing nulls are guaranteed to have 0 matches rather than 1. However, since the statistics we have at hand only tell us the fraction of nulls in each column separately, making a fraction-with-any-nulls estimate for a multi-column FK is going to be pretty spongy. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers