Re: foreign-key inference & join removal

On Mon, Oct 19, 2009 at 8:54 AM, Alex Brasetvik <alex(at)brasetvik(dot)com> wrote:
>
> 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)

It's in the back of my mind, but I think join removal & join
reordering are the biggest wins here. Pushing a LIMIT through a join
doesn't really help by itself because, under the pull model PostgreSQL
uses, the lower nodes will only be evaluated to the extent necessary
to satisfy the LIMIT. Getting rid of DISTINCT ON could be very
useful, but I think it's probably something of a corner case, since
normally you won't bother to include DISTINCT ON in the first place if
it's not doing anything.

...Robert