Re: join ordering

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.

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.
        regards, tom lane