In this post, I provide on script to find missing indexes in the schema of PostgreSQL.
The full table scanning is always performance overhead for any Database Administrator.
As a Database Proffesional, we have already created require index for a table at the time of creation. But later, there might be a chance for the database engine to avoid old index and require new form of indexes because structure and data are changing frequently.
Generally, Database Administrator requires a missing index report for only big table. I am sharing one script which is also returning the total number of sequential scan and size of the table for a given schema.
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
ORDER BY 2 DESC;
Execute this query and find that which table is require missing index base on that size and sequential scanning.