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

On 7 April 2016 at 12:23, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> For 0002
>

For find_best_foreign_key_quals() how can this ever match 2 FKs with
different keys?
The fkrel references the foreignrel, which has a single PK. How can the FK
have a different number of columns to the PK?
Assuming that is possible/makes sense, I don't see why it matters whether
we take the FK with the most keys or the least keys and that isn't
documented.

This also affects your comments in clauselist_join_selectivity() about how
we handle overlapping matches from FKs in different directions. If we're
going to prove that there is a 1:1 match, why does that matter? That
section of code looks too much. I would be happy for now with dealing
correctly and simply with the common case where just one FK matches in
either direction.

Also, I see that the effects of this on outer joins are not documented.
Earlier you mention you hadn't thought of outer joins, so I need to check
whether outer joins are not handled at all, or whether we are assuming that
we can still use an estimate even if there is an outer join present.

Thoughts?

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services