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 05E5D9FB375 for ; Tue, 1 Aug 2006 13:10:16 -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 59373-09 for ; Tue, 1 Aug 2006 13:10:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from amanda.contactbda.com (ipn36372-e65122.cidr.lightship.net [216.204.66.226]) by postgresql.org (Postfix) with ESMTP id 3B1909FB373 for ; Tue, 1 Aug 2006 13:10:03 -0300 (ADT) Received: from amanda.contactbda.com (amanda.contactbda.com [192.168.1.2]) by amanda.contactbda.com (8.12.11/8.12.11/Debian-3) with ESMTP id k71GA1un000955; Tue, 1 Aug 2006 12:10:01 -0400 From: "Jim Buttafuoco" To: "George Pavlov" , Reply-To: jim@contactbda.com Subject: Re: finding unused indexes? Date: Tue, 1 Aug 2006 12:10:01 -0400 Message-Id: <20060801160934.M4017@contactbda.com> In-Reply-To: <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net> References: <44CE9C63.7010409@debraneys.com> <8C5B026B51B6854CBE88121DBF097A863329E8@ehost010-33.exch010.intermedia.net> X-Mailer: Open WebMail 2.41 20040926 X-OriginatingIP: 192.168.1.1 (jim) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.208 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/2 X-Sequence-Number: 25960 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" To: 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 -------