Re: Poor performance on a right join

I found that when I do an inner join, I get better performance.

Carmen

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, March 17, 2003 3:31 PM
To: Carmen Sarlo
Cc: 'dev(at)archonet(dot)com'; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Poor performance on a right join

Carmen Sarlo <SarloC(at)Jevic(dot)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