postgresql admin queries

postgresql admin queries

***find the biggest tables
select relname, pg_size_pretty(pg_relation_size(relname::text)) from pg_stat_user_tables where schemaname= 'public' order by pg_relation_size(relname::text) desc;

***find the biggest indexes
select tablename, indexname, pg_size_pretty(pg_relation_size(indexname::text)) from pg_indexes where schemaname= 'public' order by pg_relation_size(indexname::text) desc;

***find indexes that are almost never used sorted by size.
  -if the application has changed significantly since the database was built, run pg_stat_reset() and wait for a few days to view only up-to-date statistics.
SELECT idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(idstat.relname::regclass)) AS table_size, pg_size_pretty(pg_relation_size(indexrelname::regclass)) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
AND indexdef !~* 'unique'
ORDER BY pg_relation_size(idstat.relname::regclass) DESC;

***check postgresql cache effectiveness for each table. This doesn't look at filesystem cache so isn't a perfect measure of cache effectiveness.
SELECT 
relname,
  heap_blks_read as heap_read,
  heap_blks_hit as heap_hit,
  (heap_blks_hit / (heap_blks_hit + heap_blks_read)::decimal(18,4))::decimal(18,4)  as ratio
FROM 
  pg_statio_user_tables
  where heap_blks_hit > 0
ORDER BY ratio desc;