Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id DEF1C9FC3B0 for ; Fri, 18 May 2007 13:02:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 25695-06 for ; Fri, 18 May 2007 13:02:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web57112.mail.re3.yahoo.com (web57112.mail.re3.yahoo.com [216.252.111.125]) by postgresql.org (Postfix) with SMTP id 0299B9FC38E for ; Fri, 18 May 2007 13:02:53 -0300 (ADT) Received: (qmail 83513 invoked by uid 60001); 18 May 2007 16:02:52 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=S8OELjriRrSEbYieqV60agMz4Za/SpSTD1yB60aT2wKazqEQuLd1f39EQHCgemNt5jp6+OtEO8b7vcULHlzxxr/AUMHbjiES0H3DU3xDxjct5QpKuiY+o/42SCm2LsnoxqXOZmRupqo15gDbJNIKSZEi9MNjSs+TK4fXavT/ARA=; X-YMail-OSG: 2nIj88gVM1nR8m3ARHWiwtIpPYzfA5oUAu_Uy3i4BCUrQMRMO1SrjoYWaEVOBiKY.iU7IVT5NHP6wpSCnSRgvWhGpaj.EwZVryzp Received: from [64.237.4.140] by web57112.mail.re3.yahoo.com via HTTP; Fri, 18 May 2007 09:02:52 PDT Date: Fri, 18 May 2007 09:02:52 -0700 (PDT) From: Abu Mushayeed Subject: CPU Intensive query To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1093072193-1179504172=:83201" Content-Transfer-Encoding: 8bit Message-ID: <877956.83201.qm@web57112.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.513 tagged_above=0 required=5 tests=AWL=0.382, BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_30_40=0.374, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200705/325 X-Sequence-Number: 24715 --0-1093072193-1179504172=:83201 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1093072193-1179504172=:83201 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-1093072193-1179504172=:83201--