CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Eng.Bassel
Eng.Bassel

Reputation: 137

Script to find and create missing indexes of the schema

I use the below script to find missing indexes but there is any modification on it to find and create statement of index creation or to find which column we can index with ?

Database: Postgres

SELECT 
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
FROM pg_stat_all_tables
WHERE schemaname='public'
AND pg_relation_size(relname::regclass)>100000 
ORDER BY 2 DESC;

Upvotes: 1

Views: 11835

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247555

I think that that query is not very useful.

Better is

SELECT relname,
       seq_scan,
       seq_tup_read / seq_scan AS tup_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0;

An index is missing if both the second and third column are big.

Upvotes: 4

jjanes
jjanes

Reputation: 44343

I don't think anything in "pg_stat_all_tables" can tell you what columns of the table might be missing from an index. I'd generally recommend a slow query log (auto_explain) to identify queries that might need indexes, rather than pg_stat_all_tables to identify tables that might. The nature of the query will then often make the column obvious. If you want to see if a specific index would be used, you can try hypopg, but you need to nominate the columns, it won't choose them for you.

Upvotes: 1

Related Questions