By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Explain output question

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Patrick Hatcher <PH******@macys.com> writes:
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.


You should be able to figure that out by correlating the plan with the
original query. In this case the inner column is clearly
agg.masked_acct_id since there is nothing else that fed.masked_acct_id
would be joined to.

It's annoying that EXPLAIN isn't always able to deliver a reasonable
text representation of values that have bubbled up from a lower plan
level. I've so far not found a good fix, but it's on the to-think-about
list ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

P: n/a
thank you. I thought it was correct but just wanted to make sure.
Patrick Hatcher
Macys.Com


Tom Lane <tg*@sss.pgh.pa.us>
10/08/04 11:34 AM

To
Patrick Hatcher <PH******@macys.com>
cc
pg***********@postgresql.org
Subject
Re: [GENERAL] Explain output question


Patrick Hatcher <PH******@macys.com> writes:
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.


You should be able to figure that out by correlating the plan with the
original query. In this case the inner column is clearly
agg.masked_acct_id since there is nothing else that fed.masked_acct_id
would be joined to.

It's annoying that EXPLAIN isn't always able to deliver a reasonable
text representation of values that have bubbled up from a lower plan
level. I've so far not found a good fix, but it's on the to-think-about
list ...

regards, tom lane
Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.