Finding unused indexes in PostgreSQL

First some quick DB/index design tips:

• When you have a 1-1 relationship, you might consider putting the data in one table, instead of several tables and linking them with foreign keys. This will speed up data retrieval considerably. This needs to be balanced with the number of cases when the given values are missing / are duplicated.
• Don't be afraid of multi-column or partial indexes. Creating the right index can speed your queries up considerably.

Now for the main topic: finding (and eliminating) unused indexes is good for at least two reasons: (1) they don't consume disk space any more and (2) they don't need to be updated when the data changes. In PG you can find unusued indexes by issuing the following statement:

SELECT *, pg_size_pretty(pg_relation_size(indexrelname))
 FROM pg_stat_all_indexes 
 WHERE schemaname = 'public' 
 ORDER BY pg_relation_size(indexrelname) DESC, idx_scan ASC

To be able to do this, you have to make sure that the following thigs are configured in your postgresql.conf file:

• The stats collector is on (stats_start_collector = on)
• Collecting of stats is enabled:

  stats_block_level = on
  stats_row_level = on
  

• Track activities and track counts are enabled:

  track_activities = on
  track_counts = on
  

• stats_reset_on_server_start is set to a value you are comfortable with. Setting it to on means that you could loose historical data if the DB is restarted (thing reporting queries executed one a month / once a quarter for example). Setting it to off means that you may be influenced by queries which are no longer being executed.

Now you can start looking at your workload. For even more tips, you can look at the following presentation:

Pro PostgreSQL, OSCon 2008

View SlideShare presentation or Upload your own. (tags: postgres postgresql)

Update: I've just found the post which was the original inspiration for my research and which give much more detailed explanation and a few variations on the theme.