I have this code which takes around 3hours to execute, My boss wants me to reduce the time of execution
please anyone an help me on this m very much new to postgres and DB thing. plz help...


CREATE OR REPLACE FUNCTION cc.computeconssubcrptionccdb(for_date date)
RETURNS bigint AS
$BODY$
declare
status_flag boolean;
rpt_start_date date;
sql_stmt character varying(1000);
begin

-- Check if DB has data for for_date
-- If DB is refreshed, then go ahead with calculation
-- Truncate Temp Tables
-- Populate AU30 Temp Table and also update AU30 Report table with Number
-- Populate other tables
-- compute the Metrics
-- Populate Reporting Metrics Table

-- Get the start date
rpt_start_date := for_date + INTERVAL '1 days';

execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(timestamp), '||chr(39)||'1.5 S30'||chr(39)||', '||chr(39)||'1.5 S30 Combo Natural Activations'||chr(39)||', count(id) from cc.transaction
where serverid in ('||chr(39)||'NLTAD'||chr(39)||')
and date(timestamp) ='||chr(39)||rpt_start_date||chr(39)||' and state=4
and type in (18,31) group by 1 order by 1';
raise notice 'Completed populating 1.5 S30 Combo Natural Activations to rpt_cons_sub_ccdb_data table';


execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(timestamp), '||chr(39)||'1.6 HC'||chr(39)||', '||chr(39)||'1.6 HC Paid Activations'||chr(39)||', count(id) from cc.transaction
where serverid in ('||chr(39)||'1.5'||chr(39)||')
and date(timestamp) ='||chr(39)||rpt_start_date||chr(39)||' and state=4
and type in (19) group by 1 order by 1';
raise notice 'Completed populating 1.6 HC Paid Activations to rpt_cons_sub_ccdb_data table';

execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(t.timestamp), '||chr(39)||'1.5 S30'||chr(39)||', '||chr(39)||'1.5 S30 Async Combo Activations'||chr(39)||', count(t.id) from cc.transaction t, cc.client c
where t.serverid in ('||chr(39)||'NLTAD'||chr(39)||')
and date(t.timestamp) ='||chr(39)||rpt_start_date||chr(39)||'
and t.state=4
and t.client_id = c.id
and c.clientswversion='||chr(39)||'2.0'||chr(39)||'
and t.type in (18,31) group by 1 order by 1';
raise notice 'Completed populating 1.5 S30 Async Combo Activations to rpt_cons_sub_ccdb_data table';

execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(t.timestamp), '||chr(39)||'1.5 S30'||chr(39)||', '||chr(39)||'1.5 S30 Async Paid Activations'||chr(39)||', count(t.id) from cc.transaction t, cc.client c
where t.serverid in ('||chr(39)||'NLTAD'||chr(39)||')
and date(t.timestamp) ='||chr(39)||rpt_start_date||chr(39)||'
and t.state=4
and t.client_id = c.id
and c.clientswversion='||chr(39)||'2.0'||chr(39)||'
and t.type in (19) group by 1 order by 1';
raise notice 'Completed populating 1.5 S30 Async Paid Activations to rpt_cons_sub_ccdb_data table';

execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(s.endtime), '||chr(39)||'Active PAID'||chr(39)||', '||chr(39)||'Active PAID DATA TillDate'||chr(39)||', count(s.id) from cc.subscription s
where s.serverid in ('||chr(39)||'NLTAD'||chr(39)||')
and date(s.endtime) ='||chr(39)||rpt_start_date||chr(39)||'
and s.type = 2
and s.cancelled='||chr(39)||'f'||chr(39)||'
and s.serviceid not in ('||chr(39)||'id1_1'||chr(39)||','||chr(39)||'id1_ 3'||chr(39)||','||chr(39)||'id1_4'||chr(39)||')
group by 1 order by 1';
raise notice 'Completed populating Active PAID DATA TillDate to rpt_cons_sub_ccdb_data table';

----=====================S30 1.7 First Ping Counts============================================ ==
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(t.timestamp), '||chr(39)||'S30 1.7'||chr(39)||', '||chr(39)||'S30 1.7 First Ping Counts'||chr(39)||', count(t.id) from cc.transaction t, cc.client c
where t.client_id = c.id
and date(t.timestamp) ='||chr(39)||rpt_start_date||chr(39)||'
and t.state=4
and c.clientswversion in( '||chr(39)||'4.0'||chr(39)||','||chr(39)||'5.0'||c hr(39)||')
and t.type in (41) group by 1 order by 1';
raise notice 'Completed populating S30 1.7 First Ping Counts to rpt_cons_sub_ccdb_data table';


------=====================1.7 S30 Natural Combo Activations======================================= =======
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(s.starttime), '||chr(39)||'1.7 S30'||chr(39)||', '||chr(39)||'1.7 S30 Natural Combo Activations'||chr(39)||', count(s.id) from cc.subscription s, cc.client c
where s.client_id = c.id
and date(s.starttime) ='||chr(39)||rpt_start_date||chr(39)||'
and s.serviceid not in ('||chr(39)||'id1_1'||chr(39)||','||chr(39)||'id1_ 3'||chr(39)||','||chr(39)||'id1_4'||chr(39)||')
and c.clientswversion in( '||chr(39)||'4.0'||chr(39)||','||chr(39)||'5.0'||c hr(39)||')
and c.devicemodel in ('||chr(39)||'Nokia1616'||chr(39)||','||chr(39)||' NokiaX1-01'||chr(39)||','||chr(39)||'Nokia100'||chr(39)||' ,'||chr(39)||'Nokia101'||chr(39)||')
and s.type in ('||chr(39)||'1'||chr(39)||') group by 1 order by 1';
raise notice 'Completed populating 1.7 S30 Natural Combo Activations to rpt_cons_sub_ccdb_data table';



-------=====================1.9 PAID ACTIVE ==============================================
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(s.starttime), '||chr(39)||'1.9'||chr(39)||', '||chr(39)||'1.9 PAID ACTIVE'||chr(39)||', count(s.id) from cc.subscription s, cc.client c
where s.client_id = c.id
and date(s.starttime) ='||chr(39)||rpt_start_date||chr(39)||'
and s.serviceid not in ('||chr(39)||'id1_1'||chr(39)||','||chr(39)||'id1_ 3'||chr(39)||','||chr(39)||'id1_4'||chr(39)||')
and c.clientswversion like '||chr(39)||'1.9%'||chr(39)||'
and s.type in ('||chr(39)||'2'||chr(39)||') group by 1 order by 1';
raise notice 'Completed populating 1.9 PAID ACTIVE to rpt_cons_sub_ccdb_data table';

------=====================1.9 COMBO & TRAIL ACTIVE ==============================================
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(s.starttime), '||chr(39)||'1.9'||chr(39)||', '||chr(39)||'1.9 COMBO & TRAIL ACTIVE'||chr(39)||', count(s.id) from cc.subscription s, cc.client c
where s.client_id = c.id
and date(s.starttime) ='||chr(39)||rpt_start_date||chr(39)||'
and s.serviceid not in ('||chr(39)||'id1_1'||chr(39)||','||chr(39)||'id1_ 3'||chr(39)||','||chr(39)||'id1_4'||chr(39)||')
and c.clientswversion like '||chr(39)||'1.9%'||chr(39)||'
and s.type in ('||chr(39)||'1'||chr(39)||','||chr(39)||'3'||chr( 39)||') group by 1 order by 1';
raise notice 'Completed populating 1.9 COMBO & TRAIL ACTIVE to rpt_cons_sub_ccdb_data table';

-----=====================1.7 PAID ACTIVE ==============================================
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(s.starttime), '||chr(39)||'1.7'||chr(39)||', '||chr(39)||'1.7 PAID ACTIVE'||chr(39)||', count(s.id) from cc.subscription s, cc.client c
where s.client_id = c.id
and date(s.starttime) ='||chr(39)||rpt_start_date||chr(39)||'
and s.serviceid not in ('||chr(39)||'id1_1'||chr(39)||','||chr(39)||'id1_ 3'||chr(39)||','||chr(39)||'id1_4'||chr(39)||')
and c.clientswversion like '||chr(39)||'1.7%'||chr(39)||'
and s.type in ('||chr(39)||'2'||chr(39)||') group by 1 order by 1';
raise notice 'Completed populating 1.7 PAID ACTIVE to rpt_cons_sub_ccdb_data table';

-----=====================2.0 Natural Combo Activations======================================= =======
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(t.timestamp), '||chr(39)||'2.0'||chr(39)||', '||chr(39)||'2.0 Natural Combo Activations'||chr(39)||', count(t.id) from cc.transaction t, cc.client c
where t.client_id = c.id
and date(t.timestamp) ='||chr(39)||rpt_start_date||chr(39)||'
and t.state=4
and t.serverid = '||chr(39)||'2.0'||chr(39)||'
and t.type in (18,31) group by 1 order by 1';
raise notice 'Completed populating 2.0 Natural Combo Activations to rpt_cons_sub_ccdb_data table';

------=====================2.0 Paid Activations======================================= =======
execute 'insert into cc.rpt_cons_sub_ccdb_data(report_date, server_name, report_type, count)
select date(t.timestamp), '||chr(39)||'2.0'||chr(39)||', '||chr(39)||'2.0 Paid Activations'||chr(39)||', count(t.id) from cc.transaction t, cc.client c
where t.client_id = c.id
and date(t.timestamp) ='||chr(39)||rpt_start_date||chr(39)||'
and t.state=4
and t.serverid = '||chr(39)||'2.0'||chr(39)||'
and t.type in (19) group by 1 order by 1';
raise notice 'Completed populating 2.0 Paid Activations to rpt_cons_sub_ccdb_data table';


return 10;

end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION cc.computeconssubcrptionccdb(date)
OWNER TO postgres;


any solution is welcome. it shul reduce the time of execution
thanks in advance