I have an interesting problem. I have the following query that ran ok on Monday
and Tuesday and it has been running ok since I have been at this job. I have
seen it to be IO intensive, but since Wednesday it has become CPU intensive.
Database wise fresh data has been put into the tables, vacuumed & analyzed, no
other parameter has been modified.
Wednesday it ran over 24 hours and it did not finish and all this time it
pegged a CPU between 95-99%. Yesterday the same story. I do not understand what
could have caused it to behave like this suddenly. I am hoping somebody can
point me to do research in the right direction.
The query is as follows and it's explain plan is also attached:
set enable_nestloop = off;
INSERT INTO linkshare.macys_ls_daily_shipped
SELECT
ddw.intr_xref,
cdm.cdm_get_linkshare_id_safe(ddw.intr_xref, 10),
to_char(cdm.cdm_utc_convert(cdm.cdm_get_linkshare_timestamp(ddw.intr_xref,
10), -5), 'YYYY-MM-DD/HH24:MI:SS'),
to_char(cdm.cdm_utc_convert(to_char(sales.order_date, 'YYYY-MM-DD
HH24:MI:SS')::timestamp without time zone, -5), 'YYYY-MM-DD/HH24:MI:SS') ,
ddw.item_upc,
sum(abs(ddw.itm_qty)),
sum((ddw.tran_itm_total * 100::numeric)::integer),
'USD', '', '', '',
ddw.item_desc
FROM
cdm.cdm_ddw_tran_item_grouped ddw
JOIN
cdm.cdm_sitesales sales ON ddw.intr_xref::text = sales.order_number::text
WHERE
ddw.cal_date > (CURRENT_DATE - 7) AND ddw.cal_date < CURRENT_DATE
AND
ddw.intr_xref IS NOT NULL
AND trim(cdm.cdm_get_linkshare_id_safe(ddw.intr_xref, 10)) <> ''
AND cdm.cdm_utc_convert(cdm.cdm_get_linkshare_timestamp(ddw.intr_xref, 10),
-5)::text::date >= (CURRENT_DATE - 52)
AND sales.order_date >= (CURRENT_DATE - 52)
AND (tran_typ_id = 'S'::bpchar)
AND btrim(item_group::text) <> 'EGC'::text
AND btrim(item_group::text) <> 'VGC'::text
GROUP BY
ddw.intr_xref,
cdm.cdm_get_linkshare_id_safe(ddw.intr_xref, 10),
to_char(cdm.cdm_utc_convert(cdm.cdm_get_linkshare_timestamp(ddw.intr_xref,
10), -5), 'YYYY-MM-DD/HH24:MI:SS'),
to_char(cdm.cdm_utc_convert(to_char(sales.order_date, 'YYYY-MM-DD
HH24:MI:SS')::timestamp without time zone, -5), 'YYYY-MM-DD/HH24:MI:SS'),
ddw.item_upc,
8, 9, 10, 11,
ddw.item_desc;
HashAggregate (cost=152555.97..152567.32 rows=267 width=162)
-> Hash Join (cost=139308.18..152547.96 rows=267 width=162)
Hash Cond: (("outer".intr_xref)::text = ("inner".order_number)::text)
-> GroupAggregate (cost=106793.14..109222.13 rows=4319 width=189)
-> Sort (cost=106793.14..106901.09 rows=43182 width=189)
Sort Key: cdm_ddw_tran_item.appl_xref,
cdm_ddw_tran_item.intr_xref, cdm_ddw_tran_item.tran_typ_id,
cdm_ddw_tran_item.cal_date, cdm_ddw_tran_item.cal_time,
cdm_ddw_tran_item.tran_itm_total, cdm_ddw_tran_item.tran_tot_amt,
cdm_ddw_tran_item.fill_store_div, cdm_ddw_tran_item.itm_price,
cdm_ddw_tran_item.item_id, cdm_ddw_tran_item.item_upc,
cdm_ddw_tran_item.item_pid, cdm_ddw_tran_item.item_desc,
cdm_ddw_tran_item.nrf_color_name, cdm_ddw_tran_item.nrf_size_name,
cdm_ddw_tran_item.dept_id, c
-> Index Scan using cdm_ddw_tranp_item_cal_date on
cdm_ddw_tran_item (cost=0.01..103468.52 rows=43182 width=189)
Index Cond: ((cal_date > (('now'::text)::date - 7))
AND (cal_date < ('now'::text)::date))
Filter: ((intr_xref IS NOT NULL) AND
(btrim(cdm.cdm_get_linkshare_id_safe(intr_xref, 10)) <> ''::text) AND
(((cdm.cdm_utc_convert(cdm.cdm_get_linkshare_timestamp(intr_xref, 10),
-5))::text)::date >= (('now'::text)::date - 52)) AND (tran_typ_id =
'S'::bpchar) AND (btrim((item_group)::text) <> 'EGC'::text) AND
(btrim((item_group)::text) <> 'VGC'::text))
-> Hash (cost=31409.92..31409.92 rows=442050 width=20)
-> Index Scan using cdm_sitesales_order_date on cdm_sitesales
sales (cost=0.00..31409.92 rows=442050 width=20)
Index Cond: (order_date >= (('now'::text)::date - 52))
---------------------------------
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.
