Обсуждение: 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: > 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 > > > 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
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