From: George Pavlov <gpavlov@mynewplace.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-sql@postgresql.org
Cc: jim@contactbda.com
Subject: Re: finding unused indexes?
Date: Wed, 10 Oct 2007 10:13:45 -0700
Message-ID: <8C5B026B51B6854CBE88121DBF097A86013F727B@ehost010-33.exch010.intermedia.net> (raw)
In-Reply-To: <15003.1192023359@sss.pgh.pa.us>
References: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net>
<1154448707.2624.11.camel@dba5.int.libertyrms.com>
<8C5B026B51B6854CBE88121DBF097A86013F6F6C@ehost010-33.exch010.intermedia.net>
<15003.1192023359@sss.pgh.pa.us>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpavlov@mynewplace.com, tgl@sss.pgh.pa.us, bnichols@ca.afilias.info, jim@contactbda.com
Subject: Re: finding unused indexes?
In-Reply-To: <8C5B026B51B6854CBE88121DBF097A86013F727B@ehost010-33.exch010.intermedia.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox