CPU Intensive query

From: Abu Mushayeed <abumushayeed(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: CPU Intensive query
Date: 2007-05-18 16:02:52
Message-ID: 877956.83201.qm@web57112.mail.re3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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))


---------------------------------
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kenneth Marshall 2007-05-18 16:21:39 Re: performance drop on 8.2.4, reverting to 8.1.4
Previous Message Tom Lane 2007-05-18 15:54:49 Re: performance drop on 8.2.4, reverting to 8.1.4