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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
