Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Postgres in Linux

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Postgres in Linux
# 1  
Old 11-30-2012
Postgres in Linux
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
Code:
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';

Last edited by Franklin52; 11-30-2012 at 04:41 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 11-30-2012
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  
Old 11-30-2012
hi michael
thanks for that... Smilie
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  
Old 12-01-2012
Wrench
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..
This User Gave Thanks to michaelrozar17 For This Post:
nikhil jain
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Using shell scripting for making queries on postgres sql

I have a situation where I have a list of airplanes that make a series of flights. Therefore I am looking up the different flights that each airplane makes based on a postgres sql query: select flightid from plane where airplane='DELTAx' As a result I get a series of flight numbers... (0 Replies)
Discussion started by: JSNY
0 Replies

2. Shell Programming and Scripting

Script for removing Postgres

I made a script to remove Postgres if this is already installed on your system. I have a other script to install Postgres, so this script can be used before you going to install Postgres. Do you like this script? I would love to hear feedback. #!/bin/bash # #################################... (2 Replies)
Discussion started by: dannyvdberg
2 Replies

3. UNIX for Advanced & Expert Users

How to set postgres process in monit file?

Dear Friends, I need to add the postgres process in monit file ( Debian machine ). How to add that process in monit?. If anyone know the solution for this, pls let me know. ---------- Post updated at 02:27 PM ---------- Previous update was at 12:21 PM ---------- I have found the... (0 Replies)
Discussion started by: rekha_sri
0 Replies

4. Shell Programming and Scripting

help copy *.txt to postgres

hi all, a have problem to load *.txt to postgres my database: id_list_ip (nextval) list_ip (varchar) txt file (list_ip.txt) hasilping_10.8.248.1 hasilping_119.110.112.226 hasilping_119.110.125.33 hasilping_125.22.1.25 hasilping_192.168.2.1 hasilping_202.73.96.70 script... (3 Replies)
Discussion started by: adi0926
3 Replies

5. Shell Programming and Scripting

How to write cron job for calling sql function database is postgres

Hi, Please help me to write cron job for calling sql function daily. I have Postgres database. (1 Reply)
Discussion started by: kulbhushan
1 Replies

6. Shell Programming and Scripting

testing postgres connection in shell script

I have a script that does db query in postgres. I'm trying to put in some error checking in the script. I keep running into the password prompt problem where when invalid credentials/or database is put it, it would prompt me for a password which hangs the script. Is there a way that I can check for... (0 Replies)
Discussion started by: zerofire123
0 Replies

7. Shell Programming and Scripting

Backup-Script for Postgres

Hi, I'm working with postgres. Now I found a script to backup my databases. # #!/bin/bash # # # setup environment PG_PORT=5432 PG_HOME=/usr/lib/postgresql/8.3 PG_VAR=/backup PG_DATA=/backup/data PG_BACKUPS=/backup/backup PG_LOGS=/backup/logs ... (6 Replies)
Discussion started by: karl_ha
6 Replies

8. Shell Programming and Scripting

Postgres : pg_dump Error

hi friends, i have script to take backup of postgresql server ....i am getting error like access denied myscript.sh sendEmail=/home/backup/scripts/sendEmail.pl DAY=`/bin/date +%d-%m-%y` DIR=/home/backup/psql/$DAY test -d $DIR | mkdir -p $DIR dblist="test test1 postgres" for db in... (8 Replies)
Discussion started by: jagnikam
8 Replies

9. Shell Programming and Scripting

how will i connect postgres database from unix shell script

I have got the solution so just closing this issue. (3 Replies)
Discussion started by: jambesh
3 Replies
Login or Register to Ask a Question