Re: Explain output question

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

Patrick Hatcher <PHatcher(at)macys(dot)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

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

Patrick Hatcher
Macys.Com

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
10/08/04 11:34 AM

To
Patrick Hatcher <PHatcher(at)macys(dot)com>
cc
pgsql-general(at)postgresql(dot)org
Subject
Re: [GENERAL] Explain output question

Patrick Hatcher <PHatcher(at)macys(dot)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