> From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 10, 2007 6:36 AM > > "George Pavlov" <[EMAIL PROTECTED]> writes: > > 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)?
just want to make sure this question gets answered. i want to be certain that there are no uses of the index that do not get reflected in one of these three stats. > > 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? > > You have stats_block_level turned on, but not stats_tuple_level? i did confirm that both settings are on and anyway i have many indexes with non-0 tuple-level stats so i am collecting them.i was curious about why some indexes would have 0s in the tup_read/tup_fetch/scan stats, but still have positive blks_read/hit numbers? and it ties to my main question of how to identify indexes that are unused/candidates for removal. > You have a lot of searches that find no rows? wouldn't a search be reflected in the idx_scan number though? in this particular case it would be *all* searches in these indexes finding no rows? (tup_read/tup_fetch/scan are all 0, but blks_read/hit are both > 0.) george ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
