From: Vidhya Bondre <meetvbondre@gmail.com>
To: pgsql-performance@postgresql.org
Subject: slow query
Date: Mon, 2 Jul 2007 15:37:04 +0530
Message-ID: <919b7db60707020307x75a347a5v1051e434b0e583d9@mail.gmail.com> (raw)
In-Reply-To: <919b7db60707020304p75e80e3av3ea79d1951989ffe@mail.gmail.com>
References: <919b7db60707020304p75e80e3av3ea79d1951989ffe@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: meetvbondre@gmail.com
Subject: Re: slow query
In-Reply-To: <919b7db60707020307x75a347a5v1051e434b0e583d9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox