Thread: Poor performance on a right join
When doing a join query I am getting a responce time of 3 seconds. The callhist table has 66000 rows and the phone table has 10000 rows. I have an indexes on callhist.call_date, callhist.operator_id, phone.phone, & phone.cust_code. Here's the SQL 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 = '555-555-5555') order by a.call_date desc; The postgres db is running on a 2 P3 700 processor server with 1GB of ram running Red Hat Linux 7.3. I am running PostgreSQL 7.2.2 Why is the query taking so long? What can I do to help the performance? Thanks in advance, Carmen
> When doing a join query I am getting a responce time of 3 seconds. The > callhist table has 66000 rows and the phone table has 10000 rows. I > have an indexes on callhist.call_date, callhist.operator_id, > phone.phone, & phone.cust_code. Here's the SQL > 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 = > '555-555-5555') order by a.call_date desc; > The postgres db is running on a 2 P3 700 processor server with 1GB of > ram running Red Hat Linux 7.3. I am running PostgreSQL 7.2.2 > > Why is the query taking so long? What can I do to help the > performance? We'll need the output of EXPLAIN ANALYSE SELECT ... Perhaps table definitions too. - Richard Huxton
<p><font size="2">Here's the EXPLAIN output:</font><p><font size="2">EXPLAIN ANALYZE SELECT a.CALL_DATE, a.OPERATOR_ID,
a.CUST_CODEFROM </font><br /><font size="2">PHONE as b right join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where
(b.PHONE</font><br /><font size="2">= '847-478-2100') order by a.call_date desc;</font><br /><font size="2">NOTICE:
QUERYPLAN:</font><p><font size="2">Sort (cost=14320.04..14320.04 rows=60466 width=68) (actual
time=5104.37..5104.3</font><br/><font size="2">8 rows=23 loops=1)</font><br /><font size="2"> -> Merge Join
(cost=0.00..8157.88rows=60466 width=68) (actual time=1396.24.</font><br /><font size="2">.5104.08 rows=23
loops=1)</font><br/><font size="2"> -> Index Scan using phone_custcode_idx on phone b (cost=0.00..345.34
r</font><br/><font size="2">ows=11395 width=28) (actual time=0.28..473.16 rows=11402 loops=1)</font><br /><font
size="2"> -> Index Scan using callhist_cust_idx on callhist a (cost=0.00..4667.4</font><br /><font
size="2">8rows=60466 width=40) (actual time=0.37..3717.76 rows=254386 loops=1)</font><br /><font size="2">Total
runtime:5104.58 msec</font><p><font size="2">EXPLAIN</font><p><font size="2">The table definitions are:</font><br
/><fontsize="2">callhist (</font><br /><font size="2"> callhist_id int4 DEFAULT
nextval('"callhist_callhist_id_seq"'::text)NOT NULL, </font><br /><font size="2"> name char(25) NOT NULL, </font><br
/><fontsize="2"> cust_code char(8) NOT NULL, </font><br /><font size="2"> action char(1) NOT NULL, </font><br /><font
size="2"> call_date timestamp NOT NULL, </font><br /><font size="2"> operator_id char(16) NOT NULL, </font><br /><font
size="2"> notes text, </font><br /><font size="2"> CONSTRAINT callhist_pkey PRIMARY KEY (callhist_id)</font><br
/><fontsize="2">) WITH OIDS;</font><p><font size="2">phone (</font><br /><font size="2"> phone char(12) NOT NULL,
</font><br/><font size="2"> cust_code char(8) NOT NULL, </font><br /><font size="2"> CONSTRAINT phone_pkey PRIMARY
KEY(phone, cust_code)</font><br /><font size="2">) WITH OIDS;</font><br /><p><font size="2">Carmen</font><p><font
size="2">-----OriginalMessage-----</font><br /><font size="2">From: dev@archonet.com [<a
href="mailto:dev@archonet.com">mailto:dev@archonet.com</a>]</font><br/><font size="2">Sent: Friday, March 14, 2003 3:13
AM</font><br/><font size="2">To: sarloc@jevic.com</font><br /><font size="2">Cc: pgsql-sql@postgresql.org</font><br
/><fontsize="2">Subject: Re: [SQL] Poor performance on a right join</font><br /><p><font size="2">> When doing a
joinquery I am getting a responce time of 3 seconds. The</font><br /><font size="2">> callhist table has 66000 rows
andthe phone table has 10000 rows. I</font><br /><font size="2">> have an indexes on callhist.call_date,
callhist.operator_id,</font><br/><font size="2">> phone.phone, & phone.cust_code. Here's the SQL</font><br
/><fontsize="2">> SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM PHONE as b right</font><br /><font
size="2">>join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where (b.PHONE =</font><br /><font size="2">>
'555-555-5555')order by a.call_date desc;</font><br /><font size="2">> The postgres db is running on a 2 P3 700
processorserver with 1GB of</font><br /><font size="2">> ram running Red Hat Linux 7.3. I am running PostgreSQL
7.2.2</font><br/><font size="2">></font><br /><font size="2">> Why is the query taking so long? What can I do to
helpthe</font><br /><font size="2">> performance?</font><p><font size="2">We'll need the output of EXPLAIN ANALYSE
SELECT...</font><br /><font size="2">Perhaps table definitions too.</font><p><font size="2">- Richard Huxton</font>
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
> Here's the EXPLAIN output: > > 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; > NOTICE: QUERY PLAN: > > Sort (cost=14320.04..14320.04 rows=60466 width=68) (actual > time=5104.37..5104.3 > 8 rows=23 loops=1) > -> Merge Join (cost=0.00..8157.88 rows=60466 width=68) (actual > time=1396.24. > .5104.08 rows=23 loops=1) > -> Index Scan using phone_custcode_idx on phone b > (cost=0.00..345.34 r > ows=11395 width=28) (actual time=0.28..473.16 rows=11402 loops=1) > -> Index Scan using callhist_cust_idx on callhist a > (cost=0.00..4667.4 > 8 rows=60466 width=40) (actual time=0.37..3717.76 rows=254386 loops=1) > Total runtime: 5104.58 msec I might be wrong, but it looks like the join is happening before filtering on the phone number (rows=254386). What happens if you don't use and explicit join: ...WHERE a.CUST_CODE=b.CUST_CODE AND b.PHONE = '847-478-2100'... - Richard Huxton
<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>