left outer join taking too long?

From: "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com>
To: "pg-general (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: left outer join taking too long?
Date: 2003-05-02 18:26:18
Message-ID: 73309C2FDD95D11192E60008C7B1D5BB05FED246@snt452.corp.bcbsm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Have a general SQL question:

I have a script that does a left outer join
and I think it's taking longer than it should; but I
can not verify that (because I don't have anything
to compare it with - yes, I know, please bear
with me).

[snip script]
explain

select
a.contract,
a.mbr_num,
a.mbrfname,
a.mbrlname,
a.mbradr1,
a.mbradr2,
a.mbrcity,
a.mbrst,
a.mbrzip,
a.bu,
a.class,
a.product,
a."group",
a.phone,
a.mbr_sex,
a.county,
b.pharm_copay,
'P'::char(1) as primary_covg
from mbr a left join t_mbr_ben_spans b on
(a.contract, a.mbr_num) = (b.contract, b.mbr_num)
;

[shaunn(at)hmp ]$ psql -U shaunn -d bcn -f ./bruce.sql
psql:./bruce.sql:27: NOTICE: QUERY PLAN:

Merge Join (cost=1081799.72..1088792.93 rows=1518781 width=237)
-> Index Scan using t_mbr_ben_spans_i on t_mbr_ben_spans b
(cost=0.00..3053.66 rows=51333 width=30)
-> Sort (cost=1081799.72..1081799.72 rows=1518781 width=207)
-> Seq Scan on mbr a (cost=0.00..73602.81 rows=1518781 width=207)

EXPLAIN

[/snip scritp]

As I look at this, I'm led to believe that 'cost' will make this thing
take a few days and I don't know how to make it more efficient.

What am I doing wrong?

Thanks!

-X

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Ronström 2003-05-02 19:08:42 Connection closed
Previous Message Dennis Gearon 2003-05-02 17:58:23 Re: select first occurrence of a table