From: Carmen Sarlo <SarloC@Jevic.com>
To: 'dev@archonet.com' <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
Message-ID: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: SarloC@Jevic.com, dev@archonet.com
Subject: Re: Poor performance on a right join
In-Reply-To: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox