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.

Anvesh Patel

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