Re: finding unused indexes? - Mailing list pgsql-sql

From George Pavlov
Subject Re: finding unused indexes?
Date
Msg-id 8C5B026B51B6854CBE88121DBF097A86013F727B@ehost010-33.exch010.intermedia.net
Whole thread Raw
In response to Re: finding unused indexes?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, October 10, 2007 6:36 AM
>
> "George Pavlov" <gpavlov@mynewplace.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



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: finding unused indexes?
Next
From: "Ray Madigan"
Date:
Subject: Computed table name in function