X-Original-To: pgsql-sql@postgresql.org
Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net
	[194.217.242.80])
	by postgresql.org (Postfix) with ESMTP id 9D502475AE5
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 15:33:09 -0500 (EST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2)
	id 18v1ID-0002Zn-0d; Mon, 17 Mar 2003 20:33:09 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id E937216E24; Mon, 17 Mar 2003 20:33:07 +0000 (GMT)
Received: from archonet.com (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with SMTP
	id 55C9F16E22; Mon, 17 Mar 2003 20:33:07 +0000 (GMT)
Received: from 192.168.1.32 (SquirrelMail authenticated user dev)
	by mainbox.archonet.com with HTTP;
	Mon, 17 Mar 2003 20:33:07 -0000 (GMT)
Message-ID: <49167.192.168.1.32.1047933187.squirrel@mainbox.archonet.com>
In-Reply-To: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE>
References: <3BCE0BA7092FD311BE570008C75DB4F80A46ABB4@EXCHANGE>
Date: Mon, 17 Mar 2003 20:33:07 -0000 (GMT)
Subject: Re: Poor performance on a right join
From: dev@archonet.com
To: "Carmen Sarlo" <SarloC@Jevic.com>
Cc: "'dev@archonet.com'" <dev@archonet.com>, pgsql-sql@postgresql.org
X-Mailer: SquirrelMail (version 1.3.2)
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
X-Priority: 3
Importance: Normal
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Archive-Number: 200303/417
X-Sequence-Number: 12456

> 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