Re: improving a badly optimized query

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