CPU Intensive query - Mailing list pgsql-performance

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))