From: Brad Nicholson <bnichols@ca.afilias.info>
To: George Pavlov <gpavlov@mynewplace.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: finding unused indexes?
Date: Tue, 01 Aug 2006 12:11:47 -0400
Message-ID: <1154448707.2624.11.camel@dba5.int.libertyrms.com> (raw)
In-Reply-To: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net>
References: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net>
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.
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: bnichols@ca.afilias.info, gpavlov@mynewplace.com
Subject: Re: finding unused indexes?
In-Reply-To: <1154448707.2624.11.camel@dba5.int.libertyrms.com>
* 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