Обсуждение: slow query
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
Vidhya Bondre skrev: > Hi all, > > I need a very urgent help from you all in below case. > > I have a query [snipped] > after vacuuming the db it has become very very slow ... 100 times slow. > > Please suggest ? Suggestions for getting more/better responses: - Format your query nicely before posting it. - Post the relevant table definitions, including indices - Tell us what the query is supposed to do. Suggestions for finding the cause of your problem: - Run "EXPLAIN ANALYZE" on the query. - Try to "remove bits" of the query to see which bits slow it down - try to find a "minimal query" which shows the performance problem. If you can, use the output of "EXPLAIN ANALYZE" obtained above. For instance, all the SUMs in the SELECT clause are unlikely to significantly affect the running time. - Run "EXPLAIN ANALYZE" on the "minimal query", post the results. Nis