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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.