Re: Regression in join selectivity estimations when using foreign keys

On 22 May 2017 at 16:10, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> I also just noticed that I don't think I've got ANTI join cases
> correct in the patch I sent. I'll look at that now.

I've attached an updated patch.

This one is much less invasive than my original attempt.

There are two fundamental changes here:

1) OUTER joins now use the foreign key as proof that the join
condition must match.
2) selectivity of nullfrac for null valued columns for OUTER joins is
no longer taken into account. This is now consistent with INNER joins,
which might not be perfect, but it's less surprising. If this is a
problem then we can consider applying something like my 0002 patch
above, however that can mean that nulls are double counted if there
are any other strict clauses which are not part of the foreign key
constraint, so that idea is not perfect either.

In addition to those two things, the poor selectivity estimation in my
original email is also fixed.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services