resurrecting an old thread: so is it safe to say that an index that has pg_stat_user_indexes.idx_scan, pg_stat_user_indexes.idx_tup_read, and pg_stat_user_indexes.idx_tup_fetch all equal to 0 has not been used (since stats have been reset)?
i have a bunch of those and all of them have pg_statio_user_indexes.idx_blks_read > 0 and most of those have pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? i guess i don't entirely understand those two values so an explanation would be very welcome (maybe an example of when each of the five values gets incremented. thanks! george > -----Original Message----- > From: Brad Nicholson [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 01, 2006 9:12 AM > To: George Pavlov > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] finding unused indexes? > > 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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
