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