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]

Re: Explain output question

Patrick Hatcher <PHatcher@macys .com> writes:[color=blue]
> 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.[/color]

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]

Re: Explain output question

thank you. I thought it was correct but just wanted to make sure.


Patrick Hatcher
Macys.Com




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

To
Patrick Hatcher <PHatcher@macys .com>
cc
pgsql-general@postgre sql.org
Subject
Re: [GENERAL] Explain output question






Patrick Hatcher <PHatcher@macys .com> writes:[color=blue]
> 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.[/color]

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