How to Log slow queries in postgresql database

Hi All,

Postgresql db can log sql queries that takes more time to execute then specified time limit.


Suppose you want to log sql queries that takes more than 100ms to execute.

Follow the below steps

1: Open postgresql.conf file. You will find this file in data folder of postgres.
2: Modify the below paratmeters

a:logging_collector = on
b:log_min_duration_statement = 100
c:log_duration = on

3: Save and close this file.
4: Restart the DB

Now execute the queries again. P{ostgres will log all the queries in pg_log folder
pg_log folder will be inside your data folder of postgresql.
Name of log file will be in the following format

postgresql-%Y-%m-%d_%H%M%S.log


If you want to change log directory and log file name, just modify these parameters to desired value

#log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,

Get Index statistics in Postgresql database

I always observed that around 30 to 40% indexes created on a database are never used by any queries.

Having extra indexes on a DB, can degrade the performance (update and insert statements) , consumes lot of space and can increase the time of backup and restoring the DB.

It make sense to check the index utilization.

To check the same in Postgresql Database follow below steps.


1: Open postgresql.conf file. This file is inside your data directory of postgres.
2: Change following parameters

A: logging_collector = on
B:track_activities = on
C:track_counts = on
3: save and close the file
4: Restart the postgresql server

Now do all possible operartions using application, just to make sure all possible queries got fired to database.

Now just run the following query



select * from pg_stat_all_indexes where schemaname= order by relname,idx_scan desc;


You will find statistics of all indexes.