X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4982C9FB375 for ; Tue, 1 Aug 2006 13:12:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63891-02 for ; Tue, 1 Aug 2006 13:11:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id F348A9FB374 for ; Tue, 1 Aug 2006 13:11:50 -0300 (ADT) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1G7wqR-0000PM-Dd; Tue, 01 Aug 2006 12:11:47 -0400 Subject: Re: finding unused indexes? From: Brad Nicholson To: George Pavlov Cc: pgsql-sql@postgresql.org In-Reply-To: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net> References: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net> Content-Type: text/plain Date: Tue, 01 Aug 2006 12:11:47 -0400 Message-Id: <1154448707.2624.11.camel@dba5.int.libertyrms.com> Mime-Version: 1.0 X-Mailer: Evolution 2.6.2 (2.6.2-1.fc5.5) Content-Transfer-Encoding: 7bit X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.315 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/3 X-Sequence-Number: 25961 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.