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