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
