Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

find a Missing Indexes of the schema

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 are adding the 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 the better query performance.
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 indexes accordingly.
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)>100000 
ORDER BY 2 DESC;

Comments

Popular posts from this blog

VMWARE WORKSTATION 3,4,5,6,7,8,9,10,11,12,14,15...etc LICENSE KEYS COLLECTION

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Configuration UDEV SCSI Rules In Oracle Linux 5, 6 and 7

aspell

Linux ctime,mtime,atime,cmin,amin,mmin