Re: foreign-key inference & join removal

On Oct 19, 2009, at 03:44 , Robert Haas wrote:

> Suppose we define a new join type called "inner_or_left_join". This
> means that we've proven that every outer row has at least one join
> partner, so that we'll get the same results whichever way we implement
> it. We can prove this for either inner joins or left joins, or for
> right joins with the sides reversed, by checking that:
>
> (1) The inner rel is a baserel with no restriction clauses.
> (2) All the join clauses are merge-joinable.
> (3) There is a table on the outer side of the join with a foreign key
> constraint referencing the inner table, such that the columns of the
> foreign key constraint and the chosen equality operators exactly match
> up with the join clauses (no extra columns, no extra join clauses).
> (4) All the relevant columns of the outer table are NOT NULL.

While considering this, have you given any thought to the points in http://archives.postgresql.org/pgsql-hackers/2009-07/msg01555.php
?

(In short, there are other properties --- e.g. that there is *exactly*
one row in B for each in A, uniqueness is kept, etc --- you can deduce
from foreign key relationships, which is useful for more than join
ordering. The example I gave involved removing Distinct and pushing
Limit through a join)

--
Alex Brasetvik