From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Brandon Craig Rhodes <brandon(at)oit(dot)gatech(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: improving a badly optimized query |
Date: | 2002-11-20 07:01:59 |
Message-ID: | 29070.1037775719@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Dalitz | 2002-11-20 07:06:50 | Re: [pgsql-general] Daily Digest V1 #2701 |
Previous Message | Justin Clift | 2002-11-20 06:49:05 | Re: [GENERAL] Request for reference sites |