Brandon Craig Rhodes <brandon@oit.gatech.edu> writes:
> (a) (slow)
> SELECT * FROM role_keys NATURAL LEFT JOIN role_person
> WHERE person = 28389;
> (b) (fast)
> SELECT * FROM role_keys NATURAL JOIN role_person
> WHERE person = 28389;
> Apparently PostgreSQL does not realize that the rows created for
> unmatched role_keys rows by the LEFT JOIN are guaranteed to be thrown
> out by the WHERE clause (their `person' fields will be null).
> [ and hence the LEFT JOIN could be reduced to a JOIN ]
Hmm ... you are right, there is no such logic in there. It seems like
a useful optimization, but I have an uncomfortable feeling that there's
something wrong with it. Can you point to a rigorous proof that this is
okay in complicated contexts such as nested outer joins?
regards, tom lane