pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding unused indexes?
6+ messages / 4 participants
[nested] [flat]
* finding unused indexes?
@ 2006-08-01 16:05 George Pavlov <gpavlov@mynewplace.com>
0 siblings, 2 replies; 6+ messages in thread
From: George Pavlov @ 2006-08-01 16:05 UTC (permalink / raw)
To: pgsql-sql
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.
George
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding unused indexes?
@ 2006-08-01 16:10 Jim Buttafuoco <jim@contactbda.com>
parent: George Pavlov <gpavlov@mynewplace.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jim Buttafuoco @ 2006-08-01 16:10 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; pgsql-sql
check out pg_stat_user_indexes, you will need to turn on the stats collection in your postgresql.conf file first.
Jim
---------- Original Message -----------
From: "George Pavlov" <gpavlov@mynewplace.com>
To: <pgsql-sql@postgresql.org>
Sent: Tue, 1 Aug 2006 09:05:34 -0700
Subject: [SQL] finding unused indexes?
> 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.
>
> George
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding unused indexes?
@ 2006-08-01 16:11 Brad Nicholson <bnichols@ca.afilias.info>
parent: George Pavlov <gpavlov@mynewplace.com>
1 sibling, 1 reply; 6+ messages in thread
From: Brad Nicholson @ 2006-08-01 16:11 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding unused indexes?
@ 2007-10-09 18:58 George Pavlov <gpavlov@mynewplace.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 6+ messages in thread
From: George Pavlov @ 2007-10-09 18:58 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-sql; +Cc: jim@contactbda.com
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:bnichols@ca.afilias.info]
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding unused indexes?
@ 2007-10-10 13:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: George Pavlov <gpavlov@mynewplace.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-10-10 13:35 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-sql; jim@contactbda.com
"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)?
> 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?
You have a lot of searches that find no rows?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: finding unused indexes?
@ 2007-10-10 17:13 George Pavlov <gpavlov@mynewplace.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: George Pavlov @ 2007-10-10 17:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-sql; jim@contactbda.com
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-10-10 17:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-01 16:05 finding unused indexes? George Pavlov <gpavlov@mynewplace.com>
2006-08-01 16:10 ` Jim Buttafuoco <jim@contactbda.com>
2006-08-01 16:11 ` Brad Nicholson <bnichols@ca.afilias.info>
2007-10-09 18:58 ` George Pavlov <gpavlov@mynewplace.com>
2007-10-10 13:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-10 17:13 ` George Pavlov <gpavlov@mynewplace.com>
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