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

On Wed, May 4, 2016 at 2:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> My other design-level complaint is that basing this on foreign keys is
> fundamentally the wrong thing. What actually matters is the unique index
> underlying the FK; that is, if we have "a.x = b.y" and there's a
> compatible unique index on b.y, we can conclude that no A row will match
> more than one B row, whether or not an explicit FK relationship has been
> declared. So we should drive this off unique indexes instead of FKs,
> first because we will find more cases, and second because the planner
> already examines indexes and doesn't need any additional catalog lookups
> to get the required data. (IOW, the relcache additions that were made in
> this patch series should go away too.)

Without prejudice to anything else in this useful and detailed review,
I have a question about this. A unique index proves that no A row
will match more than one B row, and I agree that deriving that from
unique indexes is sensible. However, ISTM that an FK provides
additional information: we know that, modulo filter conditions on B,
every A row will match *exactly* one row B row, which can prevent us
from *underestimating* the size of the join product. A unique index
can't do that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company