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

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq