PostgreSQL: Script to find a Missing Indexes of the schema

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.

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, 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.

More from

Leave a Reply

7 Comments on "PostgreSQL: Script to find a Missing Indexes of the schema"

Notify of
Sort by:   newest | oldest | most voted

I don’t even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you’re going to a famous blogger if you are not already 😉 Cheers!


I really like and appreciate your blog post.Much thanks again. Fantastic.


Muchos Gracias for your blog article. Great.


Good. But how can i find the columns which need the index.


helped a lot… thanks