Explain output question

[Patrick Hatcher]

I have the following SQL with explain
Should I be concerned with the merge cond: Merge Cond:
(("outer".maske d_acct_id)::tex t = "inner"."?colum n5?")? I have no idea
what column5 is or the same token the column6 shown later.

explain
select fed.indiv_fkey,
SUM(agg.purch_d ollars) as val_purch_store ,
SUM(agg.no_visi ts) as cnt_visit_store ,
SUM(CASE x.gmmid when 1 Then agg.purch_dolla rs else 0 end) as
Store_GMM1_Jewe lryn,
SUM(CASE x.gmmid when 2 Then agg.purch_dolla rs else 0 end) as
Store_GMM2_CCn,
SUM(CASE x.gmmid when 3 Then agg.purch_dolla rs else 0 end) as
Store_GMM3_Beau tyn,
SUM(CASE x.gmmid when 4 Then agg.purch_dolla rs else 0 end) as
Store_GMM4_RTWn ,
SUM(CASE x.gmmid when 5 Then agg.purch_dolla rs else 0 end) as
Store_GMM5_Mens n,
SUM(CASE x.gmmid when 6 Then agg.purch_dolla rs else 0 end) as
Store_GMM6_Home n,
SUM(CASE x.gmmid when 7 Then agg.purch_dolla rs else 0 end) as
Store_GMM7_Furn ituren,
SUM(CASE x.gmmid when 8 Then agg.purch_dolla rs else 0 end) as
Store_GMM8_Othe rn,
SUM(CASE when x.gmmid is null Then agg.purch_dolla rs else 0 end) as
Store_GMM_NotMa ppedn
from cdm.cdm_fedcust omer 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','B UR','BON','RLG' )
group by 1;

GroupAggregate (cost=6510420.2 7..6562483.23 rows=650787 width=27)
-> Sort (cost=6510420.2 7..6512047.23 rows=650787 width=27)
Sort Key: fed.indiv_fkey
-> Merge Join (cost=6010047.0 4..6447580.84 rows=650787
width=27)
Merge Cond: (("outer".maske d_acct_id)::tex t =
"inner"."?colum n5?")
-> Index Scan using fedcust_maskeda ctt_idx on
cdm_fedcustomer fed (cost=0.00..411 831.29 rows=6377392 width=29)
-> Sort (cost=6010047.0 4..6011674.00 rows=650787
width=39)
Sort Key: (agg.masked_acc t_id)::text
-> Merge Join (cost=5738556.1 6..5947207.61
rows=650787 width=39)
Merge Cond: ((("outer".div) ::text =
"inner"."?colum n6?") AND ("outer".dept_k ey = "inner".dept_ke y))
-> Index Scan using fadept_div_idx on
cdm_fed_agg_dep txreff x (cost=0.00..206 .23 rows=5294 width=15)
-> Sort (cost=5738556.1 6..5805859.79
rows=26921450 width=46)
Sort Key: (agg.fed_div):: text,
agg.dept_key
-> Seq Scan on cdm_fed_agg_pur ch agg
(cost=0.00..146 9685.99 rows=26921450 width=46)
Filter: (((fed_div)::te xt =
'MCE'::text) OR ((fed_div)::tex t = 'MCW'::text) OR ((fed_div)::tex t =
'BUR'::text) OR ((fed_div)::tex t = 'BON'::text) OR ((fed_div)::tex t =
'RLG'::text))


TIA
Patrick Hatcher
Macys.Com

[Tom Lane]

Patrick Hatcher <PH******@macys .com> writes:

Should I be concerned with the merge cond: Merge Cond:
(("outer".maske d_acct_id)::tex t = "inner"."?colum n5?")? 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

[Patrick Hatcher]

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***********@p ostgresql.org
Subject
Re: [GENERAL] Explain output question


Patrick Hatcher <PH******@macys .com> writes:

Should I be concerned with the merge cond: Merge Cond:
(("outer".maske d_acct_id)::tex t = "inner"."?colum n5?")? 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