This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find missing indexes in the schema of PostgreSQL.
The full table scanning is always creating a performance issue for any database.
As a Database Professional, you might be adding database indexes on a table but periodically the volume of data is changing so we need to analyze the old indexes, or we should find missing indexes which may require for query optimizer.
On the other hand, Database Administrator may also require a report on missing indexes which they can share with developers and users so that they can modify it.
12345678910111213 SELECTrelname AS TableName,seq_scan-idx_scan AS TotalSeqScan,CASE WHEN seq_scan-idx_scan > 0THEN 'Missing Index Found'ELSE 'Missing Index Not Found'END AS MissingIndex,pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize,idx_scan AS TotalIndexScanFROM pg_stat_all_tablesWHERE schemaname='public'AND pg_relation_size(relname::regclass)>100000ORDER BY 2 DESC;
Please visit other related articles...