Re: Poor performance on a right join

<p><font size="2">I found that when I do an inner join, I get better performance.</font><p><font
size="2">Carmen</font><br/><p><font size="2">-----Original Message-----</font><br /><font size="2">From: Tom Lane [<a
href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br/><font size="2">Sent: Monday, March 17, 2003
3:31PM</font><br /><font size="2">To: Carmen Sarlo</font><br /><font size="2">Cc: 'dev@archonet.com';
pgsql-sql@postgresql.org</font><br/><font size="2">Subject: Re: [SQL] Poor performance on a right join </font><br
/><p><fontsize="2">Carmen Sarlo <SarloC@Jevic.com> writes:</font><br /><font size="2">> EXPLAIN ANALYZE SELECT
a.CALL_DATE,a.OPERATOR_ID, a.CUST_CODE FROM </font><br /><font size="2">> PHONE as b right join CALLHIST as a on
(a.CUST_CODE=b.CUST_CODE)where</font><br /><font size="2">> (b.PHONE = '847-478-2100') order by a.call_date
desc;</font><p><fontsize="2">This query is rather pointless as-is: the WHERE clause will eliminate</font><br /><font
size="2">anynull-extended rows produced by the RIGHT JOIN (because null b.PHONE</font><br /><font size="2">cannot
satisfythe '=' condition).  So you may as well reduce the right</font><br /><font size="2">join to a plain join.  That
willresult in a much better plan, because</font><br /><font size="2">then the WHERE clause can be pushed down to the
scanof b.</font><p><font size="2">7.4 will be bright enough to make that deduction by itself, but 7.3 is</font><br
/><fontsize="2">not, and certainly not 7.2 ...</font><p>                        <font size="2">regards, tom lane</font>