Re: Index Usage

On Tue, 2007-10-16 at 15:51 -0700, Ben wrote:
> You could take a look at pg_statio_user_indexes and/or
> pg_stat_user_indexes, if you have stats enabled....
>
> On Tue, 16 Oct 2007, Bryan Murphy wrote:

If your intention is to eliminate the unused indexes rows you should run
'vaccum' and/or 'vacuum full' and/or 'reindex'.

This also has the consequence of freing filesystem space and returning
it back to the OS.

Check it out here:

http://www.postgresql.org/docs/8.1/static/maintenance.html

chapters 22.1, 22.2 and 22.3

I use:

VACUUM FULL ANALYZE;
REINDEX INDEX yourIndex;
REINDEX TABLE yourTable

it works just great for me.

Cheers
joao

>
> > Is there a way I can track index usage over a long period of time?
> > Specifically, I'd like to identify indexes that aren't being regularly
> > used and drop them.
> >
> > Bryan
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq