Re: finding unused indexes?

> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, October 10, 2007 6:36 AM
>
> "George Pavlov" <gpavlov(at)mynewplace(dot)com> 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