Thread: Index Usage

Index Usage

From
"Bryan Murphy"
Date:
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

Re: Index Usage

From
Ben
Date:
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:

> 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
>

Re: Index Usage

From
Joao Miguel Ferreira
Date:
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


Re: Index Usage

From
"Bryan Murphy"
Date:
On 10/17/07, Joao Miguel Ferreira <joao.mc.ferreira@criticalsoftware.com> 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

That's not my intention at all.  My intention is to justify the
validity of each index in our database.  Some indexes have snuck in
that I find of questionable value, and I want the data to backup my
intuition.

Anyway, I'll look into the pg_stat* tables and see if those give me
the data I want.  Thanks for the advice guys!

Bryan