left outer join taking too long?

Started by Johnson, Shaunnalmost 23 years ago2 messagesgeneral
Jump to latest
#1Johnson, Shaunn
SJohnson6@bcbsm.com

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@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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Johnson, Shaunn (#1)
Re: left outer join taking too long?

"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:

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)

Increasing sort_mem would make that cost estimate drop. What effects
it'd have on the actual runtime is harder to say; but I never trust
EXPLAIN's estimates very much ;-)

regards, tom lane