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 040C0475921 for ; Fri, 14 Mar 2003 09:15:39 -0500 (EST) Received: by EXCHANGE with Internet Mail Service (5.5.2655.55) id ; Fri, 14 Mar 2003 09:15:43 -0500 Message-ID: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE> From: Carmen Sarlo To: "'dev@archonet.com'" Cc: pgsql-sql@postgresql.org Subject: Re: Poor performance on a right join Date: Fri, 14 Mar 2003 09:15:34 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2655.55) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2EA34.2DEA8B60" X-Archive-Number: 200303/411 X-Sequence-Number: 12449 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_01C2EA34.2DEA8B60 Content-Type: text/plain; charset="iso-8859-1" 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 EXPLAIN The table definitions are: callhist ( callhist_id int4 DEFAULT nextval('"callhist_callhist_id_seq"'::text) NOT NULL, name char(25) NOT NULL, cust_code char(8) NOT NULL, action char(1) NOT NULL, call_date timestamp NOT NULL, operator_id char(16) NOT NULL, notes text, CONSTRAINT callhist_pkey PRIMARY KEY (callhist_id) ) WITH OIDS; phone ( phone char(12) NOT NULL, cust_code char(8) NOT NULL, CONSTRAINT phone_pkey PRIMARY KEY (phone, cust_code) ) WITH OIDS; Carmen -----Original Message----- From: dev@archonet.com [mailto:dev@archonet.com] Sent: Friday, March 14, 2003 3:13 AM To: sarloc@jevic.com Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] 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? We'll need the output of EXPLAIN ANALYSE SELECT ... Perhaps table definitions too. - Richard Huxton ------_=_NextPart_001_01C2EA34.2DEA8B60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Poor performance on a right join

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

Sort  (cost=3D14320.04..14320.04 rows=3D60466 width= =3D68) (actual time=3D5104.37..5104.3
8 rows=3D23 loops=3D1)
  ->  Merge Join  (cost=3D0.00..8157.8= 8 rows=3D60466 width=3D68) (actual time=3D1396.24.
.5104.08 rows=3D23 loops=3D1)
        ->  I= ndex Scan using phone_custcode_idx on phone b  (cost=3D0.00..345.34 r<= /FONT>
ows=3D11395 width=3D28) (actual time=3D0.28..473.16 rows= =3D11402 loops=3D1)
        ->  I= ndex Scan using callhist_cust_idx on callhist a  (cost=3D0.00..4667.4<= /FONT>
8 rows=3D60466 width=3D40) (actual time=3D0.37..3717.76 = rows=3D254386 loops=3D1)
Total runtime: 5104.58 msec

EXPLAIN

The table definitions are:
callhist (
  callhist_id int4 DEFAULT nextval('"callhist_= callhist_id_seq"'::text) NOT NULL,
  name char(25) NOT NULL,
  cust_code char(8) NOT NULL,
  action char(1) NOT NULL,
  call_date timestamp NOT NULL,
  operator_id char(16) NOT NULL,
  notes text,
  CONSTRAINT callhist_pkey PRIMARY KEY (callhist_id= )
) WITH OIDS;

phone (
  phone char(12) NOT NULL,
  cust_code char(8) NOT NULL,
  CONSTRAINT phone_pkey PRIMARY KEY (phone, cust_co= de)
) WITH OIDS;


Carmen

-----Original Message-----
From: dev@archonet.com [mailto:dev@archonet.com]
Sent: Friday, March 14, 2003 3:13 AM
To: sarloc@jevic.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 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 h= as 10000 rows. I
> have an indexes on callhist.call_date, callhist.ope= rator_id,
> phone.phone, & phone.cust_code. Here's the SQL<= /FONT>
> 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) w= here (b.PHONE =3D
> '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 Postgre= SQL 7.2.2
>
> Why is the query taking so long? What can I do to h= elp the
> performance?

We'll need the output of EXPLAIN ANALYSE SELECT ...
Perhaps table definitions too.

- Richard Huxton

------_=_NextPart_001_01C2EA34.2DEA8B60--