To: Carmen Sarlo <SarloC@Jevic.com>
Cc: "'dev@archonet.com'" <dev@archonet.com>, pgsql-sql@postgresql.org
Subject: Re: Poor performance on a right join 
In-reply-to: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE> 
References: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE>
Comments: In-reply-to Carmen Sarlo <SarloC@Jevic.com>
	message dated "Fri, 14 Mar 2003 09:15:34 -0500"
Date: Mon, 17 Mar 2003 15:30:40 -0500
Message-ID: <8129.1047933040@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Carmen Sarlo <SarloC@Jevic.com> writes:
> EXPLAIN ANALYZE SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM 
> PHONE as b right join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where
> (b.PHONE = '847-478-2100') order by a.call_date desc;

This query is rather pointless as-is: the WHERE clause will eliminate
any null-extended rows produced by the RIGHT JOIN (because null b.PHONE
cannot satisfy the '=' condition).  So you may as well reduce the right
join to a plain join.  That will result in a much better plan, because
then the WHERE clause can be pushed down to the scan of b.

7.4 will be bright enough to make that deduction by itself, but 7.3 is
not, and certainly not 7.2 ...

			regards, tom lane