Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 948569FB4CF for ; Mon, 2 Jul 2007 07:07:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59185-01 for ; Mon, 2 Jul 2007 07:07:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.226]) by postgresql.org (Postfix) with ESMTP id C6DE89FB772 for ; Mon, 2 Jul 2007 07:07:07 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id l8so709847nzf for ; Mon, 02 Jul 2007 03:07:05 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=fo/Rb6H3AwqmGcU9Droun2E1KAGdV1131SoXKS6UWnLkAPdxfvd09IVx65qdnktBZJ67gUmGtMyiTCZ/hl0+9ktFN3vP3cOwGq5Djcy5ondm/U1t7nxkUc/KBf/D4TbgnHOpiWkL32kV0Wpa8tUMjZdGh5f17cGixLPR2zWctMM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=jDv/nhlAKgKhLvXKMnYshyNbRajcuzBOSIw/ZYcMurgq8rd17ibAuDPp/aN6+5vft1LWTl7viG4Mhc2zs6CaUNqhKY9DIYz3AtxyBVTXNlDHz5QU2B5AhZlTH1uFkYzDVfQuqhiGBCSzDnl5caSnPChOOYjCOq1nQks4+T1jrr8= Received: by 10.141.85.13 with SMTP id n13mr1375623rvl.1183370824537; Mon, 02 Jul 2007 03:07:04 -0700 (PDT) Received: by 10.141.87.4 with HTTP; Mon, 2 Jul 2007 03:07:04 -0700 (PDT) Message-ID: <919b7db60707020307x75a347a5v1051e434b0e583d9@mail.gmail.com> Date: Mon, 2 Jul 2007 15:37:04 +0530 From: "Vidhya Bondre" To: pgsql-performance@postgresql.org Subject: slow query In-Reply-To: <919b7db60707020304p75e80e3av3ea79d1951989ffe@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3815_31910701.1183370824521" References: <919b7db60707020304p75e80e3av3ea79d1951989ffe@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/2 X-Sequence-Number: 25686 ------=_Part_3815_31910701.1183370824521 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi all, I need a very urgent help from you all in below case. I have a query SELECT amp.campaign_id, dam.allocation_map_id,amp.optimize_type, amp.optimize_by_days, amp.rate, amp.action_id,amp.actions_delta, amp.vearned_today, amp.creative_id, amp.channel_code,SUM(CASE dam.sqldatewhen 20070701 then dam.actions_delivered else 0 end) as action_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vearned_total else 0 end) as earned_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vactions_delivered else 0 end) as vactions_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel1,SUM(CASE sign(20070617 - dam.sqldate ) when -1 then dam.vactions_delivered else 0 end) as vactionsdel2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel5, SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel8 FROM delivered_action_map dam INNER JOIN (SELECT a.campaign_id, a.optimize_type,a.optimize_by_days, a.rate, a.action_id, am.creative_id, am.channel_code, amt.actions_delta, amt.vearned_today, am.id AS allocation_map_id FROM (SELECT c.campaign_id , c.optimize_type, c.optimize_by_days, a1.rate, a1.id AS action_id FROM action a1 INNER JOIN (SELECT c1.asset_id AS campaign_id, ca.value AS optimize_type,c1.optimize_by_days AS optimize_by_days FROM campaign c1 INNER JOIN (SELECT ca2.campaign_id AS campaign_id, ca3.value AS value FROM campaign_attributes ca2, campaign_attributes ca3 WHERE ca2.campaign_id = ca3.campaign_id AND ca2.attribute='OPTIMIZE_STATUS' AND ca2.value = '1'AND ca3. attribute ='OPTIMIZE_TYPE') as ca ON c1.asset_id=ca.campaign_id AND 20070702 BETWEEN (c1.start_date - interval '1 day') AND (c1.end_date+interval '1day') AND c1.status = 'A' AND c1.revenue_type != 'FOC' AND c1.action_type >= 1 AND c1.optimize_by_days > 0) AS c ON a1.campaign_id = c.campaign_id AND a1.status = 'A') AS a, allocation_map am, action_metrics amt WHERE a.action_id = amt.action_id AND am.id = amt.allocation_map_id AND am.status= 'A') AS amp ON dam.allocation_map_id= amp.allocation_map_id AND dam.action_id = amp.action_id GROUP BY amp.campaign_id, amp.optimize_type, amp.optimize_by_days, amp.rate, amp.action_id, amp.actions_delta , amp.creative_id, amp.channel_code, dam.allocation_map_id, amp.vearned_today; after vacuuming the db it has become very very slow ... 100 times slow. Please suggest ? Regards Vidhya ------=_Part_3815_31910701.1183370824521 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline



Hi all,
 
   I need a very urgent help from you all in below case.
 
   I have a query
 
  SELECT amp.campaign_id, dam.allocation_map_id,amp.optimize_type,amp.optimize_by_days, amp.rate, amp.action_id,amp.actions_delta, amp.vearned_today, amp.creative_id, amp.channel_code,SUM(CASE dam.sqldate when 20070701 then dam.actions_delivered else 0 end) as action_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vearned_total else 0 end) as earned_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vactions_delivered else 0 end) as vactions_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel1,SUM(CASE sign(20070617 - dam.sqldate ) when -1 then dam.vactions_delivered else 0 end) as vactionsdel2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel5, SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel8 FROM delivered_action_map dam  INNER JOIN  (SELECT a.campaign_id, a.optimize_type,a.optimize_by_days,a.rate, a.action_id, am.creative_id, am.channel_code,  amt.actions_delta, amt.vearned_today, am.id AS allocation_map_id FROM  (SELECT c.campaign_id , c.optimize_type, c.optimize_by_days, a1.rate, a1.id AS action_id FROM action a1 INNER JOIN  (SELECT c1.asset_id AS campaign_id, ca.value AS optimize_type,c1.optimize_by_days AS optimize_by_days FROM campaign c1 INNER JOIN (SELECT ca2.campaign_id AS campaign_id, ca3.value AS value FROM campaign_attributes ca2, campaign_attributes ca3 WHERE ca2.campaign_id = ca3.campaign_id AND ca2.attribute='OPTIMIZE_STATUS' AND ca2.value = '1'AND ca3. attribute ='OPTIMIZE_TYPE') as ca ON c1.asset_id=ca.campaign_id  AND 20070702 BETWEEN (c1.start_date - interval '1 day') AND (c1.end_date +interval '1day') AND c1.status = 'A' AND c1.revenue_type != 'FOC' AND c1.action_type >= 1 AND c1.optimize_by_days > 0) AS c ON a1.campaign_id = c.campaign_id AND a1.status = 'A') AS a, allocation_map am, action_metrics amt WHERE a.action_id = amt.action_id AND am.id = amt.allocation_map_id AND am.status = 'A') AS amp ON dam.allocation_map_id= amp.allocation_map_id AND dam.action_id = amp.action_id GROUP BY amp.campaign_id, amp.optimize_type, amp.optimize_by_days, amp.rate , amp.action_id, amp.actions_delta , amp.creative_id, amp.channel_code, dam.allocation_map_id, amp.vearned_today;
 
after vacuuming the db it has become very very slow ... 100 times slow.
 
Please suggest ?
 
Regards
Vidhya
 ------=_Part_3815_31910701.1183370824521--