X-Original-To: pgsql-sql@postgresql.org Received: from Exchange.JTI (exchange.jevic.com [12.0.244.210]) by postgresql.org (Postfix) with ESMTP id 5FF02475FC6 for ; Mon, 17 Mar 2003 15:34:42 -0500 (EST) Received: by EXCHANGE with Internet Mail Service (5.5.2655.55) id ; Mon, 17 Mar 2003 15:34:41 -0500 Message-ID: <3BCE0BA7092FD311BE570008C75DB4F80A46ABCD@EXCHANGE> From: Carmen Sarlo To: 'Tom Lane' Cc: "'dev@archonet.com'" , pgsql-sql@postgresql.org Subject: Re: Poor performance on a right join Date: Mon, 17 Mar 2003 15:34:40 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2655.55) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2ECC4.A27E0C30" X-Archive-Number: 200303/466 X-Sequence-Number: 12505 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C2ECC4.A27E0C30 Content-Type: text/plain; charset="iso-8859-1" I found that when I do an inner join, I get better performance. Carmen -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, March 17, 2003 3:31 PM To: Carmen Sarlo Cc: 'dev@archonet.com'; pgsql-sql@postgresql.org Subject: Re: [SQL] Poor performance on a right join Carmen Sarlo 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 ------_=_NextPart_001_01C2ECC4.A27E0C30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Poor performance on a right join

I found that when I do an inner join, I get better perfor= mance.

Carmen


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


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= =3Db.CUST_CODE) where
> (b.PHONE =3D '847-478-2100') order by a.call_date d= esc;

This query is rather pointless as-is: the WHERE clause wi= ll eliminate
any null-extended rows produced by the RIGHT JOIN (becau= se null b.PHONE
cannot satisfy the '=3D' condition).  So you may as= well reduce the right
join to a plain join.  That will result in a much b= etter 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 itsel= f, but 7.3 is
not, and certainly not 7.2 ...

             = ;           reg= ards, tom lane

------_=_NextPart_001_01C2ECC4.A27E0C30--