From: | Patrick Hatcher <PHatcher(at)macys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Explain output question |
Date: | 2004-10-08 17:23:26 |
Message-ID: | OF9007578A.E468134D-ON88256F27.005F777C-88256F27.0060B839@fds.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have the following SQL with explain
Should I be concerned with the merge cond: Merge Cond:
(("outer".masked_acct_id)::text = "inner"."?column5?")? I have no idea
what column5 is or the same token the column6 shown later.
explain
select fed.indiv_fkey,
SUM(agg.purch_dollars) as val_purch_store,
SUM(agg.no_visits) as cnt_visit_store,
SUM(CASE x.gmmid when 1 Then agg.purch_dollars else 0 end) as
Store_GMM1_Jewelryn,
SUM(CASE x.gmmid when 2 Then agg.purch_dollars else 0 end) as
Store_GMM2_CCn,
SUM(CASE x.gmmid when 3 Then agg.purch_dollars else 0 end) as
Store_GMM3_Beautyn,
SUM(CASE x.gmmid when 4 Then agg.purch_dollars else 0 end) as
Store_GMM4_RTWn,
SUM(CASE x.gmmid when 5 Then agg.purch_dollars else 0 end) as
Store_GMM5_Mensn,
SUM(CASE x.gmmid when 6 Then agg.purch_dollars else 0 end) as
Store_GMM6_Homen,
SUM(CASE x.gmmid when 7 Then agg.purch_dollars else 0 end) as
Store_GMM7_Furnituren,
SUM(CASE x.gmmid when 8 Then agg.purch_dollars else 0 end) as
Store_GMM8_Othern,
SUM(CASE when x.gmmid is null Then agg.purch_dollars else 0 end) as
Store_GMM_NotMappedn
from cdm.cdm_fedcustomer fed
inner join cdm.cdm_fed_agg_purch agg
on fed.masked_acct_id = agg.masked_acct_id
inner join cdm.cdm_fed_agg_deptxreff x
on (agg.dept_key = x.dept_key and agg.fed_div = x.div)
where agg.fed_div in ('MCE','MCW','BUR','BON','RLG')
group by 1;
GroupAggregate (cost=6510420.27..6562483.23 rows=650787 width=27)
-> Sort (cost=6510420.27..6512047.23 rows=650787 width=27)
Sort Key: fed.indiv_fkey
-> Merge Join (cost=6010047.04..6447580.84 rows=650787
width=27)
Merge Cond: (("outer".masked_acct_id)::text =
"inner"."?column5?")
-> Index Scan using fedcust_maskedactt_idx on
cdm_fedcustomer fed (cost=0.00..411831.29 rows=6377392 width=29)
-> Sort (cost=6010047.04..6011674.00 rows=650787
width=39)
Sort Key: (agg.masked_acct_id)::text
-> Merge Join (cost=5738556.16..5947207.61
rows=650787 width=39)
Merge Cond: ((("outer".div)::text =
"inner"."?column6?") AND ("outer".dept_key = "inner".dept_key))
-> Index Scan using fadept_div_idx on
cdm_fed_agg_deptxreff x (cost=0.00..206.23 rows=5294 width=15)
-> Sort (cost=5738556.16..5805859.79
rows=26921450 width=46)
Sort Key: (agg.fed_div)::text,
agg.dept_key
-> Seq Scan on cdm_fed_agg_purch agg
(cost=0.00..1469685.99 rows=26921450 width=46)
Filter: (((fed_div)::text =
'MCE'::text) OR ((fed_div)::text = 'MCW'::text) OR ((fed_div)::text =
'BUR'::text) OR ((fed_div)::text = 'BON'::text) OR ((fed_div)::text =
'RLG'::text))
TIA
Patrick Hatcher
Macys.Com
From | Date | Subject | |
---|---|---|---|
Next Message | Samik Raychaudhuri | 2004-10-08 17:54:05 | Re: CGI program cannot access database |
Previous Message | Guy Fraser | 2004-10-08 17:20:48 | Re: About PostgreSQL's limit on arithmetic operations |