Find useless postgresql indexes

gistfile1.sql

-- find useless indexes in PostgreSQL 8.3
-- find indexes scanned less than 200 times.
-- adjust idstat.idx_scan to change this number
--
-- flush the stats with pg_stat_reset() if you want to start over.
-- adapted from http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796
SELECT
    idstat.schemaname AS schema_name,
    idstat.relname AS table_name,
    indexrelname AS index_name,
    idstat.idx_scan AS times_used,
    pg_size_pretty (pg_relation_size (idstat.relid)) AS table_size,
    pg_size_pretty (pg_relation_size (indexrelid)) AS index_size,
    n_tup_upd + n_tup_ins + n_tup_del AS num_writes,
    indexdef AS definition
FROM
    pg_stat_user_indexes AS idstat
    JOIN pg_indexes ON (
        indexrelname = indexname
        AND
        idstat.schemaname = pg_indexes.schemaname
    )
    JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid
WHERE
    idstat.idx_scan < 200
    AND
    indexdef ! ~ * 'unique'
ORDER BY
    idstat.relname,
    indexrelname;