pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor performance on a right join
6+ messages / 4 participants
[nested] [flat]
* Poor performance on a right join
@ 2003-03-14 00:12 Carmen <sarloc@jevic.com>
2003-03-14 08:13 ` Re: Poor performance on a right join dev@archonet.com
0 siblings, 1 reply; 6+ messages in thread
From: sarloc@jevic.com @ 2003-03-14 00:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor performance on a right join
2003-03-14 00:12 Poor performance on a right join Carmen <sarloc@jevic.com>
@ 2003-03-14 08:13 ` dev@archonet.com
0 siblings, 0 replies; 6+ messages in thread
From: dev@archonet.com @ 2003-03-14 08:13 UTC (permalink / raw)
To: sarloc@jevic.com; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor performance on a right join
@ 2003-03-14 14:15 Carmen Sarlo <SarloC@Jevic.com>
2003-03-17 20:30 ` Re: Poor performance on a right join Tom Lane <tgl@sss.pgh.pa.us>
2003-03-17 20:33 ` Re: Poor performance on a right join dev@archonet.com
0 siblings, 2 replies; 6+ messages in thread
From: Carmen Sarlo @ 2003-03-14 14:15 UTC (permalink / raw)
To: 'dev@archonet.com' <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor performance on a right join
2003-03-14 14:15 Re: Poor performance on a right join Carmen Sarlo <SarloC@Jevic.com>
@ 2003-03-17 20:30 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-03-17 20:30 UTC (permalink / raw)
To: Carmen Sarlo <SarloC@Jevic.com>; +Cc: 'dev@archonet.com' <dev@archonet.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor performance on a right join
2003-03-14 14:15 Re: Poor performance on a right join Carmen Sarlo <SarloC@Jevic.com>
@ 2003-03-17 20:33 ` dev@archonet.com
1 sibling, 0 replies; 6+ messages in thread
From: dev@archonet.com @ 2003-03-17 20:33 UTC (permalink / raw)
To: Carmen Sarlo <SarloC@Jevic.com>; +Cc: 'dev@archonet.com' <dev@archonet.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Poor performance on a right join
@ 2003-03-17 20:34 Carmen Sarlo <SarloC@Jevic.com>
0 siblings, 0 replies; 6+ messages in thread
From: Carmen Sarlo @ 2003-03-17 20:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 'dev@archonet.com' <dev@archonet.com>; pgsql-sql
I found that when I do an inner join, I get better performance.
Carmen
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 17, 2003 3:31 PM
To: Carmen Sarlo
Cc: 'dev@archonet.com'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Poor performance on a right join
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-17 20:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-14 00:12 Poor performance on a right join Carmen <sarloc@jevic.com>
2003-03-14 08:13 ` dev@archonet.com
2003-03-14 14:15 Re: Poor performance on a right join Carmen Sarlo <SarloC@Jevic.com>
2003-03-17 20:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-17 20:33 ` dev@archonet.com
2003-03-17 20:34 Re: Poor performance on a right join Carmen Sarlo <SarloC@Jevic.com>
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