Home 2015 October PostgreSQL: Script to find a Missing Indexes of the schema

PostgreSQL: Script to find a Missing Indexes of the schema

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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.

Anvesh Patel
MySQL: Frequently asked Interview Questions and Answers Part 7PostgreSQL: Fast way to find the row count of a Table
Comments: 9
  1. sdorttuiiplmnr
    December 10, 2015 at 5:40 pm

    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!

    • Anvesh Patel
      Anvesh Patel
      December 15, 2015 at 6:02 am

      Thanks Man !

  2. fargus
    December 28, 2015 at 3:01 pm

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

  3. steen
    January 30, 2016 at 8:09 am

    Muchos Gracias for your blog article. Great.

  4. Aniltech
    June 4, 2016 at 9:13 am

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

    • Anvesh Patel
      Anvesh Patel
      June 4, 2016 at 10:23 am

      Like SQL Server Index tuning wizard, we do not have such tool in PostgreSQL. Generally, you have to analyze filter, joins, table data and bases on that you should apply proper indexes.

  5. Dietmar Schnabel
    September 10, 2018 at 7:53 am

    I think this script is not okay as it does a strange calculation with regards to calculation of index scans..

    I find a quick adaption of this script better:

    SELECT
    relname AS TableName
    ,seq_scan AS TotalSeqScan
    ,CASE WHEN seq_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;

    Here this solution was showing us many more tables with problems. Hope this helps someone!

    Dietmar

  6. Cuneyd Tanriverdi
    June 18, 2019 at 11:09 am

    Hi, When I tried to execute script from Pgadmin4 or DBEaver tool, I am getting an error like:
    ERROR: relation “jobdeclinefeedback” does not exist
    its because postgresql is case sensetive. On database Table name is “JobDecLineFeedback”
    How can we fix your script about this issue.

    I think this is the issue line:
    pg_size_pretty(pg_relation_size(relname::regclass))

  7. Bassel
    March 18, 2020 at 1:17 pm

    could you please complete the script to give us the script of index creation also ?

Anvesh Patel
Anvesh Patel

Database Engineer

October 7, 2015 PostgreSQL, PostgreSQL DBA Script, , , , , , , , , , , ,