Re: PATCH: use foreign keys to improve join estimates v1

On 7 April 2015 at 13:41, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:

>
> (1) The current patch only does the trick when the FK matches the
> conditions perfectly - when there are no missing columns (present
> in the FK, not covered by a condition).
>
>
Hi Tomas,

I did glance at this patch a while back, but just thinking on it again.

I think if you find any quals that are a part of *any* foreign key between
the 2 join tables, then you should be never assume these quals to reduce
the number of rows. I believe this should be the case even if you don't
fully match all columns of the foreign key.

If we modify your example a little, let's say your foreign key between fact
and dim is made from 3 columns (a,b,c)

If we do:

EXPLAIN SELECT * FROM fact f JOIN dim d USING (a,b);

Then we should always (under normal circumstances) find at least one
matching row, although in this case since the join qual for c is missing,
we could find more than 1 matching row.

Without digging too deep here, I'd say that the best way to do this would
be to either have calc_joinrel_size_estimate() build a list of restrictinfo
items of all quals that are NOT part of any foreign key and pass that
trimmed list down to clauselist_selectivity() for selectivity estimates. Or
perhaps a better way would be just to teach clauselist_selectivity() about
foreign keys. Likely clauselist_selectivity() would just have to skip over
RestrictInfo items that are part of a foreign key.

Regards

David Rowley