PostgreSQL - Tuning Query Performance

To identify queries requiring more than 2 seconds to run (change this value as needed), run the following command via psql:

SELECT now() - query_start as "runtime", usename, datname, wait_event, wait_event_type, state, query
FROM  pg_stat_activity
WHERE now() - query_start > '2 seconds'::interval and state = 'active'
ORDER BY runtime DESC;

For the tables identified in the above queries, examine their schema to determine if indexes are set properly:

\d TABLE_NAME

For external articles about database indexes, see:

• https://semaphoreci.com/blog/2017/05/09/faster-rails-is-your-database-properly-indexed.html
• https://devcenter.heroku.com/articles/postgresql-indexes
• http://rny.io/rails/postgresql/2013/08/20/postgresql-indexing-in-rails.html

Healthcare Blocks Postgres databases default to 100 max connections. This value can be customized for your environment (please open a help desk ticket as needed). To view the number of current connections, run the following query via psql:

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from 
(select count(*) used from pg_stat_activity) t1, 
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2, 
(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;