From: | dev(at)archonet(dot)com |
---|---|
To: | "Carmen Sarlo" <SarloC(at)Jevic(dot)com> |
Cc: | "'dev(at)archonet(dot)com'" <dev(at)archonet(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Poor performance on a right join |
Date: | 2003-03-17 20:33:07 |
Message-ID: | 49167.192.168.1.32.1047933187.squirrel@mainbox.archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Carmen Sarlo | 2003-03-17 20:34:40 | Re: Poor performance on a right join |
Previous Message | Tom Lane | 2003-03-17 20:30:40 | Re: Poor performance on a right join |