PostgreSQL

Database Maintenance and Monitoring script

Monitor Index dan size for optimization

/*
 * Over optimizing is a thing
	We always our database to be performant, so in order to do that we keep things in memory/cache (see earlier) and we index things so we don’t have to scan everything on disk. 
	But there is a trade-off when it comes to indexing your database. 
	Each index the system has to maintain will slow down your write throughput on the database. 
	This is fine when you do need to speed up queries, as long as they’re being utilized. 
	If you added an index years ago, but something within your application changed and you no longer need it best to remove it.
	Postgres makes it simply to query for unused indexes so you can easily give yourself back some performance by removing them:
 */
SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
--WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
--where relname = 'ret_gen_tsm_targetomzetline'
where relname in ('ret_gen_tsm_targetomzetgrow','ret_gen_tsm_targetomzetline','ret_tsmrekap_category','ret_gen_tsmtarget_category')
ORDER BY relname asc, pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
--idx_scan asc,
pg_relation_size(i.indexrelid) desc
;

--drop index ret_gen_tsm_targetomzetline_tag_status_idx;