Skip site navigation (1) Skip section navigation (2)

CPU Intensive query

From: Abu Mushayeed <abumushayeed(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: CPU Intensive query
Date: 2007-05-18 16:00:06
Message-ID: 217678.88787.qm@web57104.mail.re3.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
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))


       
---------------------------------
Boardwalk for $500? In 2007? Ha! 
Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.

pgsql-admin by date

Next:From: Joe ConwayDate: 2007-05-18 16:31:11
Subject: Re: select from dblink very slow
Previous:From: Scott MarloweDate: 2007-05-18 15:59:07
Subject: Re: cross database references...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group