Performance Problem

Hi,

explain analyze SELECT am.campaign_id, am.optimize_type,
am.creative_id, am.optimize_by_days, am.impressions_delta,
am.clicks_delta, am.channel_code, am.cost,dm.allocation_map_id,
SUM(CASE dm.sqldate when 20070602 then dm.impressions_delivered else 0
end) as deliv_yest, SUM(CASE sign(20070526 - dm.sqldate) when -1 then
dm.impressions_delivered else 0 end) as deliv_wk1, SUM(CASE
sign(20070519 - dm.sqldate) when -1 then dm.impressions_delivered else
0 end) as deliv_wk2, SUM(CASE sign(20070512 - dm.sqldate ) when -1
then dm.impressions_delivered else 0 end) as deliv_wk3, SUM(CASE
sign(20070505 - dm.sqldate) when -1 then dm.impressions_delivered else
0 end) as deliv_wk4, SUM(CASE sign(20070428 - dm.sqldate) when -1 then
dm.impressions_delivered else 0 end) as deliv_wk5, SUM(CASE
sign(20070421 - dm.sqldate) when -1 then dm.impressions_delivered else
0 end) as deliv_wk6, SUM(CASE sign(20070414 - dm.sqldate) when -1 then
dm.impressions_delivered else 0 end) as deliv_wk7, SUM(CASE
sign(20070407 - dm.sqldate) when -1 then dm.impressions_delivered else
0 end) as deliv_wk8, SUM(CASE dm.sqldate when 20070602 then
dm.clicks_delivered else 0 end) as clicks_yest, SUM(CASE sign(20070526
- dm.sqldate) when -1 then dm.clicks_delivered else 0 end) as
clicks_wk1, SUM(CASE sign(20070519 - dm.sqldate) when -1 then
dm.clicks_delivered else 0 end) as clicks_wk2, SUM(CASE sign(20070512
-dm.sqldate) when -1 then dm.clicks_delivered else 0 end) as
clicks_wk3, SUM(CASE sign(20070505 - dm.sqldate) when -1 then
dm.clicks_delivered else 0 end) as clicks_wk4, SUM(CASE sign(20070428
- dm.sqldate) when -1 then dm.clicks_delivered else 0 end) as
clicks_wk5, SUM(CASE sign(20070421 - dm.sqldate) when -1 then
dm.clicks_delivered else 0 end) as clicks_wk6, SUM(CASE sign(20070414
- dm.sqldate) when -1 then dm.clicks_delivered else 0 end) as
clicks_wk7, SUM(CASE sign(20070407 -dm.sqldate) when -1 then
dm.clicks_delivered else 0 end) as clicks_wk8 FROM dl_mp dm INNER JOIN
(SELECT cr.campaign_id, cr.optimize_type, cr.creative_id,
cr.optimize_by_days, am1.impressions_delta, am1.clicks_delta,
am1.channel_code , am1.id , cr.cost FROM al_mp am1 INNER JOIN (SELECT
c.campaign_id , c.optimize_type, cr1.id AS creative_id,
c.optimize_by_days, c.cost FROM crt cr1 INNER JOIN (SELECT
c1.asset_id AS campaign_id, ca.value AS optimize_type,
c1.optimize_by_days AS optimize_by_days , c1.cost as cost FROM cmp c1
INNER JOIN (SELECT ca2.campaign_id AS campaign_id, ca3.value AS value
FROM cmp_attr ca2, cmp_attr 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 WHERE 20070603 BETWEEN (c1.start_date - interval '1
day') AND (c1.end_date + interval '1 day') AND c1.status = 'A' AND
c1.revenue_type != 'FOC') AS c ON cr1.campaign_id =c.campaign_id
AND c.optimize_by_days > 0 WHERE cr1.status != 'HID') AS cr ON
cr.creative_id = am1.creative_id WHERE am1.status = 'A') AS am ON
am.id = dm.allocation_map_id AND am.creative_id = dm.creative_id AND
am.channel_code = dm.channel_code GROUP BY am.campaign_id,
am.optimize_type, am.creative_id, am.optimize_by_days ,
am.impressions_delta,am.clicks_delta , am.channel_code, am.cost ,
dm.allocation_map_id;

.

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=92536.47..92536.69 rows=1 width=138) (actual
time=3194317.938..3194324.811 rows=1584 loops=1)
-> Nested Loop (cost=66901.04..92536.40 rows=1 width=138) (actual
time=9044.558..3193988.565 rows=13556 loops=1)
Join Filter: (("outer".channel_code = "inner".channel_code)
AND ("inner".creative_id = "outer".creative_id))
-> Nested Loop (cost=40269.84..41486.82 rows=1 width=122)
(actual time=442.818..119250.727 rows=11483 loops=1)
-> Nested Loop (cost=105.83..333.55 rows=1 width=94)
(actual time=17.199..117.536 rows=263 loops=1)
-> Nested Loop (cost=105.83..329.53 rows=1
width=24) (actual time=17.175..106.429 rows=263 loops=1)
-> Nested Loop (cost=105.83..171.93
rows=1 width=16) (actual time=17.125..40.490 rows=38 loops=1)
-> Bitmap Heap Scan on cmp_attr ca2
(cost=105.83..168.20 rows=1 width=4) (actual time=1.759..5.767
rows=1186 loops=1)
Recheck Cond:
((attribute)::text = 'OPTIMIZE_STATUS'::text)
Filter: ((value)::text = '1'::text)
-> Bitmap Index Scan on
campaign_attributes_pk (cost=0.00..105.83 rows=60 width=0) (actual
time=1.721..1.721 rows=1279 loops=1)
Index Cond:
((attribute)::text = 'OPTIMIZE_STATUS'::text)
-> Index Scan using cmp_pk1 on cmp
c1 (cost=0.00..3.72 rows=1 width=12) (actual time=0.025..0.026 rows=0
loops=1186)
Index Cond: (c1.asset_id =
"outer".campaign_id)
Filter: (('20070603'::text >=
((start_date - '1 day'::interval))::text) AND ('20070603'::text <=
((end_date + '1 day'::interval))::text) AND (status = 'A'::bpchar) AND
(revenue_type <> 'FOC'::bpchar) AND (optimize_by_days > 0))
-> Index Scan using creative_c_id on crt
cr1 (cost=0.00..156.55 rows=84 width=8) (actual time=0.051..1.699
rows=7 loops=38)
Index Cond: (cr1.campaign_id =
"outer".asset_id)
Filter: (status <> 'HID'::bpchar)
-> Index Scan using campaign_attributes_pk on
cmp_attr ca3 (cost=0.00..4.01 rows=1 width=82) (actual
time=0.027..0.031 rows=1 loops=263)
Index Cond: (("outer".campaign_id =
ca3.campaign_id) AND ((ca3.attribute)::text = 'OPTIMIZE_TYPE'::text))
-> Bitmap Heap Scan on al_mp am1
(cost=40164.01..41146.99 rows=502 width=28) (actual
time=447.274..452.698 rows=44 loops=263)
Recheck Cond: ("outer".id = am1.creative_id)
Filter: ((status)::text = 'A'::text)
-> Bitmap Index Scan on alc_map_idx
(cost=0.00..40164.01 rows=502 width=0) (actual time=447.145..447.145
rows=144 loops=263)
Index Cond: ("outer".id = am1.creative_id)
-> Bitmap Heap Scan on dl_mp dm (cost=26631.20..50697.13
rows=20140 width=32) (actual time=266.680..267.745 rows=1 loops=11483)
Recheck Cond: ("outer".id = dm.allocation_map_id)
-> Bitmap Index Scan on dl_mp_amap_dt
(cost=0.00..26631.20 rows=20140 width=0) (actual time=266.436..266.436
rows=1 loops=11483)
Index Cond: ("outer".id = dm.allocation_map_id)
Total runtime: 3194328.561 ms
(30 rows)

Befor doing vaccum full on the database this query use to take less
than 4min. But now after doing vacumming reindexing the tables it is
taking 73mins.

After observing the explain analyse it seems like it is not selecting
the required index properly.

So can anybody suggest any thing??

--
Regards
Gauri