On Mon, Apr 13, 2009 at 7:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> This isn't a very good plan. What we should do is first join the
>> values expression against bar, and then join the resulting rows
>> against foo. The optimizer doesn't want to do that, and I think the
>> reason is because it knows that the left join might introduce null
>> values into the result of (VALUES (...) LEFT JOIN bar) which would
>> then cause the join against foo to produce different results.
>
> Exactly. Inner and outer joins don't commute in general.
>
>> But in
>> practice, since foo.id is not null and = is strict, it's equivalent to
>> the following, which the planner handles much better.
>
> Nonsense; those conditions are not sufficient to prove what you wish.
> I think it is actually true given that the foreign key relationship
> together with the not null on foo_id (NOT foo.id) implies that every row
> of bar must have a join partner in foo; but not without that.
Yeah, good point.
> If we had any FK analysis in the optimizer (which we don't at present)
> I think the deduction you'd really want is that foo can be removed from
> the query altogether, because actually every row of bar must have
> *exactly* one join partner in foo, and we don't care about the values of
> foo otherwise.
The way I set up this particular example, that's true, but suppose foo
had another column which the SELECT pulled into the output. In that
case, the FK analysis wouldn't permit removing the join altogether,
but it would permit reordering it. I think that:
A inner join B on Pab = A leftjoin B on Pab
...given that Pab is a set of equality constraints setting columns of
A equal to the columns of B to which they are mapped by a foreign key
constraint, and given further that at least one of these columns is
NOT NULL in A.
In some cases this can be a big win, because it means that this join
can commute with either inner joins or left joins (but once we commute
it with a left join it turns into a plain left join, and we can't go
back to handling it as an inner join).
...Robert