Postgres in Linux

Shell Programming and Scripting
1

I have this 15 postgres sql queries similar to below to run in linux... Its taking a lot of time to run (3hours) .
can any one plz guide me how can i reduce the time of execution

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.5 S30'||chr(39)||', '||chr(39)||'1.5 S30 Paid 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 (19) group by 1 order by 1';
raise notice 'Completed populating 1.5 S30 Paid Activations to rpt_cons_sub_ccdb_data table';
2

The long run of the script has little to do with Linux, i believe you need to tune your SQL's. You could create function based index on the timestamp column in Oracle DB, similarly you can create index on expressions in Postgres.

3

hi michael
thanks for that... :slight_smile:
plz can u help me in shorten the code as well as improve the performance... i did post in sql forum but no luck

4

I neither worked in Postgres or installed it in my pc to test hence i would not able to help you much, but try searching in internet for postgres sql tuning tips etc. As suggested earlier try expression indexing on timestamp column and unless it necessary avoid order by clause..

1 Like