A starting point for analysis of postgres performance

postgres_performance.MD

Resetting pg_stat_database and pg_stat_all_tables

To reset below states

select pg_stat_reset();

SELECT pg_stat_statements_reset()

Involving pg_stat_database

Cache hits (rel IO Peformance)

The following query should be very close to 1

SELECT blks_hit::float/(blks_read + blks_hit) as cache_hit_ratio
FROM pg_stat_database WHERE datname = 'talentdeck';

Transaction Rollbacks

The following should be very close to 1

SELECT xact_commit::float/(xact_commit + xact_rollback) as successful_xact_ratio
FROM pg_stat_database
WHERE datname='talentdeck';

Deadlocks

Not sure what is good or bad head but on the db i checked the value was zero.

SELECT deadlocks
FROM pg_stat_database
WHERE datname='talentdeck';

Involving pg_stat_all_tables

Row scans vs table scans

Apparently this should be very close to 1 ?!?!? (Confirm) Not entirely convinced of this because sequential scans make sense on small tables and make sense when fetching the entire table.

SELECT sum(idx_scan)/(sum(idx_scan) + sum(seq_scan)) as idx_scan_ratio
FROM pg_stat_all_tables
WHERE schemaname='public';

On a per tabel basis

SELECT relname,idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio
FROM pg_stat_all_tables
WHERE schemaname='public'
ORDER BY idx_scan_ratio ASC;

This similar query was highly upvoted on SO. Note that it filters by relation size. Also note that it uses difference in scans rather than ratio.

SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relid::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(relid::regclass)>80000 ORDER BY too_much_seq DESC;

Involving pg_stat_statements

If you want to go one step further and analyse exact statements. This can help with finding out the exact source of the problem. The could require extra installation on setup. After a postgre homebrew install I needed to do the following. Make the following changes in postgres config

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Mine was situated: /usr/local/var/postgres/postgresql.conf and you can get the location in postgres via SHOW config_file;. Restart postgres and then run CREATE EXTENSION pg_stat_statements;

Finding slow queries

Note that the following only shows stats for the currently connected db, so no need to filter by dbid.

select calls,total_time,min_time,max_time,mean_time,left(query,100), dbid from pg_stat_statements
order by total_time desc;

Other

Others I came across. (Still ned to take a closer look at these)

Index cache ratio

SELECT 
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM 
  pg_statio_user_indexes;

Unused indexes

SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;