Re: 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=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(at)archonet(dot)com [mailto:dev(at)archonet(dot)com]
Sent: Friday, March 14, 2003 3:13 AM
To: sarloc(at)jevic(dot)com
Cc: pgsql-sql(at)postgresql(dot)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