Poor SQL performance - Mailing list pgsql-performance

Hey there folks.  I'm at a loss as to how to increase the speed of this
query.  It's something I need to run each day, but can't at the rate this
runs.  Tables are updated 1/day and is vacuum analyzed after each load.

select ddw_tran_key, r.price_type_id, t.price_type_id
from
cdm.cdm_ddw_tran_item_header h JOIN cdm.cdm_ddw_tran_item t
on t.appl_xref=h.appl_xref
JOIN
mdc_upc u ON
u.upc = t.item_upc
JOIN
mdc_price_history r
ON
r.    upc_id = u.keyp_upc and date(r.site_timestamp) = h.first_order_date
where
cal_date = '2005-08-31'
and
h.appl_id= 'MCOM'
and tran_typ_id='S'
limit 1000


My explain is just horrendous:

                                                                    QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=288251.71..342657.36 rows=258 width=14)
   ->  Merge Join  (cost=288251.71..342657.36 rows=258 width=14)
         Merge Cond: (("outer".appl_xref)::text = "inner"."?column6?")
         Join Filter: (date("inner".site_timestamp) =
"outer".first_order_date)
         ->  Index Scan using cdm_ddw_tran_item_header_pkey on
cdm_ddw_tran_item_header h  (cost=0.00..51188.91 rows=789900 width=21)
               Filter: ((appl_id)::text = 'MCOM'::text)
         ->  Sort  (cost=288251.71..288604.31 rows=141038 width=39)
               Sort Key: (t.appl_xref)::text
               ->  Hash Join  (cost=29708.54..276188.93 rows=141038
width=39)
                     Hash Cond: ("outer".upc_id = "inner".keyp_upc)
                     ->  Seq Scan on mdc_price_history r
(cost=0.00..189831.09 rows=11047709 width=16)
                     ->  Hash  (cost=29698.81..29698.81 rows=3892 width=31)
                           ->  Nested Loop  (cost=0.00..29698.81 rows=3892
width=31)
                                 ->  Index Scan using
cdm_ddw_tran_item_cal_date on cdm_ddw_tran_item t  (cost=0.00..14046.49
rows=3891 width=35)
                                       Index Cond: (cal_date =
'2005-08-31'::date)
                                       Filter: (tran_typ_id = 'S'::bpchar)
                                 ->  Index Scan using mdcupcidx on mdc_upc
u  (cost=0.00..4.01 rows=1 width=12)
                                       Index Cond: (u.upc =
"outer".item_upc)
(18 rows)



What I found is that I remove change the line:
r.upc_id = u.keyp_upc and date(r.site_timestamp) = h.first_order_date

To
r.upc_id = u.keyp_upc

My query plan drops to:
                                                                  QUERY
PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=33327.39..37227.51 rows=1000 width=14)
   ->  Hash Join  (cost=33327.39..279027.01 rows=62998 width=14)
         Hash Cond: ("outer".upc_id = "inner".keyp_upc)
         ->  Seq Scan on mdc_price_history r  (cost=0.00..189831.09
rows=11047709 width=8)
         ->  Hash  (cost=33323.05..33323.05 rows=1738 width=14)
               ->  Nested Loop  (cost=0.00..33323.05 rows=1738 width=14)
                     ->  Nested Loop  (cost=0.00..26335.62 rows=1737
width=18)
                           ->  Index Scan using cdm_ddw_tran_item_cal_date
on cdm_ddw_tran_item t  (cost=0.00..14046.49 rows=3891 width=35)
                                 Index Cond: (cal_date =
'2005-08-31'::date)
                                 Filter: (tran_typ_id = 'S'::bpchar)
                           ->  Index Scan using
cdm_ddw_tran_item_header_pkey on cdm_ddw_tran_item_header h
(cost=0.00..3.15 rows=1 width=17)
                                 Index Cond: (("outer".appl_xref)::text =
(h.appl_xref)::text)
                                 Filter: ((appl_id)::text = 'MCOM'::text)
                     ->  Index Scan using mdcupcidx on mdc_upc u
(cost=0.00..4.01 rows=1 width=12)
                           Index Cond: (u.upc = "outer".item_upc)
(15 rows)




Unfortunately, I need this criteria since it contains the first date of the
order and is used to pull the correct price.
Any suggestions?
TIA
Patrick