Finding unused index on postgresql 9

Just use this query on you database and find unwanted indexes :

 

SELECT
 t.tablename,
 indexname,
 c.reltuples AS num_rows,
 pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
 pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
 CASE WHEN x.is_unique = 1 THEN 'Y'
 ELSE 'N'
 END AS UNIQUE,
 idx_scan AS number_of_scans,
 idx_tup_read AS tuples_read,
 idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
 (SELECT indrelid,
 max(CAST(indisunique AS integer)) AS is_unique
 FROM pg_index
 GROUP BY indrelid) x
 ON c.oid = x.indrelid
LEFT OUTER JOIN
 ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
 JOIN pg_class c ON c.oid = x.indrelid
 JOIN pg_class ipg ON ipg.oid = x.indexrelid
 JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
 AS foo
 ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

You can do pg_stat_reset() before that and let your db run for a time before anylisis

From http://wiki.postgresql.org/wiki/Index_Maintenance

Publié dans postgresql, sql Tagués avec : , ,
Un commentaire sur “Finding unused index on postgresql 9
  1. Mas dit :

    Same like Hakim, when I tested this index dilsabe feature a while ago, I didn’t see any use case as to enable’ the index back require a index rebuild which similar to dropping/creating the index. Now I can see at least one use case for this index dilsabe feature for its metadata retention. Data warehouse operation often require index to be dropped and recreated later after insert. A lot of times I saw the index recreation step is pre-scripted out which cause subsequent problems when someone modify the index and forgot to change the index recreated script. This metadata of the dilsabed index could be useful to reduce this type of problem as long as the foreign key concern is carefully examined.I am still thinking what is the use case of the statistics retained from the dilsabed index. Jes or anyone, do you come across any use case for this? Thanks.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*