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
