-- https://www.dbrnd.com/2015/10/postgresql-script-to-find-a-missing-indexes-of-the-schema/ SELECT relname AS TableName ,seq_scan-idx_scan AS TotalSeqScan ,CASE WHEN seq_scan-idx_scan > 0 THEN 'Missing Index Found' ELSE 'Missing Index Not Found' END AS MissingIndex ,pg_size_pretty(pg_relation_size(('"' || relname || '"')::regclass)) AS TableSize ,idx_scan AS TotalIndexScan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(('"' || relname || '"')::regclass)>10000 ORDER BY 2 DESC;
SELECT relname AS TableName, to_char(seq_scan, '999,999,999,999') AS TotalSeqScan, to_char(idx_scan, '999,999,999,999') AS TotalIndexScan, to_char(n_live_tup, '999,999,999,999') AS TableRows, pg_size_pretty(pg_relation_size(('"' || relname || '"')::regclass)) AS TableSize FROM pg_stat_all_tables WHERE schemaname = 'public' AND 50 * seq_scan > idx_scan -- more then 2% AND n_live_tup > 10000 AND pg_relation_size(('"' || relname || '"')::regclass) > 5000000 ORDER BY relname ASC;
