Re: Poor performance on a right join - Mailing list pgsql-sql

<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>