Re: pg9.6 segfault using simple query (related to use fk for join estimates)

David Rowley <david(dot)rowley(at)2ndquadrant(dot)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