IF you think postgres performance is the cause of an application performing badly these sql statements may help

Postgres

 

  Long Running Transactions

 

The statement below shows the current running sessions and how long they’ve been running with.

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
order by duration desc;

 

you can further troubleshoot what these queries are doing by using the pg_top utility

 

/opt/vmware/vpostgres/current/bin/pg_top

 

 

 

Use the ‘Q’ key and enter a PID of the statement you’re interested in to get more information.

 

http://ptop.projects.pgfoundry.org/screenshots/

 

 

 

 TempDB

Temporary data such as sort and temporary tables are handled very differently in postgres. Mostly this is done in memory and not on disk. A section of memory is allocated to each session for this. It’s called the work_mem and can be increased if performance is poor. When the memory set is too small it writes temp file but this is expensive.

See work_mem in https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

SQL Tracing

 

This is simple In postgres but adds load and generates a lot of data which has to be analyzed manually as far as I am aware.

 

Here’s the procedure.

Find –name postgresql.conf

Back it up

Edit the postgresql.conf to update these entries

track_activities = on
track_activity_query_size = 6000
log_statement_stats = on
log_statement = ‘all’

 

reload the postgres instance

/opt/vmware/vpostgres/current/bin/pg_ctl reload –U secureall -D /common/db/vshield

The values of the-U and –D commands will be differnet you can get these from

By doing a ps –ef | grep postgres you’ll be able to see the values for –U and –D

Verify logs have verbose information. These logs will be postgresql.log or server,log.

To roll back the change just replace the postgresql.conf with the original and reload again.