Re: improving a badly optimized query

Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)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