Index Usage
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
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:
Show quoted text
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
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
Show quoted text
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?
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 yourTableit 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