Обсуждение: left outer join taking too long?
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
"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