Re: Hunting Unused Indexes .. is it this simple ?

Stef,

>> is it as simple as taking the output from ; select indexrelname
>> from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and
>> idx_tup_fetch = 0 ;
>>
>> And .. dropping ?

Almost that simple. The caveat is that indexes which are only used for
the enforcement of unique constraints (or other constraints) don't
count, but you don't want to drop them because they're required for the
constraints to work.

Also, if you have a large index with very low (but non-zero) scans, you
probably want to drop that as well.

Full query for that is here:
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com