On Tue, 2006-08-01 at 09:05 -0700, George Pavlov wrote:
> Anybody have a clever way to quickly find whether there are any unused
> indexes in a PG DB? One way I have done is to take queries from the DB
> log, prepend an explain to each and grep the results, but I am wondering
> if there are either any index usage stats maintained somewhere inside
> Postgres or if there is a slicker/less cumbersome way of doing it. Also
> indexes used by functions are hard to simulate that way.
>
Check out pg_stat_user_indexes, pg_stat_sys_indexes and
pg_statio_all_indexes
You can very clearly see the index usage there. You might have to mess
with the statistics collector section in the postgresql.conf file in
order to collect the information.
Brad.