Path: g2news2.google.com!news1.google.com!news.glorb.com!news2.glorb.com!news.hub.org!postgresql.org!pgsql-performance-owner+M35625
From: j...@agliodbs.com (Josh Berkus)
Newsgroups: pgsql.performance
Subject: Re: [PERFORM] Hunting Unused Indexes .. is it this simple ?
Date: Tue, 22 Sep 2009 16:52:17 -0700
Organization: PostgreSQL Experts Inc.
Lines: 29
Sender: n...@news.hub.org
Message-ID: <4AB96331.6070902@agliodbs.com>
References: <4AB8D9AA.6050007@ummon.com> <4AB8E145.4040901@squeakycode.net>
NNTP-Posting-Host: news.hub.org
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Trace: news.hub.org 1253663555 74581 200.46.204.72 (22 Sep 2009 23:52:35 GMT)
X-Complaints-To: usenet@news.hub.org
NNTP-Posting-Date: Tue, 22 Sep 2009 23:52:35 +0000 (UTC)
X-Received: from mx2.hub.org (mx2.hub.org [200.46.204.254])
	by news.hub.org (8.14.3/8.14.3) with ESMTP id n8MNqZCs074573
	for <pgsql-performa...@news.hub.org>; Tue, 22 Sep 2009 20:52:35 -0300 (ADT)
	(envelope-from pgsql-performance-owner+M35...@postgresql.org)
X-Received: from postgresql.org (mail.postgresql.org [200.46.204.86])
	by mx2.hub.org (Postfix) with ESMTP id 00E771E815AF;
	Tue, 22 Sep 2009 20:52:37 -0300 (ADT)
X-Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 818746334F5
	for <pgsql-performance-postgresql....@mail.postgresql.org>; Tue, 22 Sep 2009 20:52:33 -0300 (ADT)
X-Received: from mail.postgresql.org ([200.46.204.86])
 by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
 with ESMTP id 18037-08
 for <pgsql-performance-postgresql....@mail.postgresql.org>;
 Tue, 22 Sep 2009 23:52:25 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com [69.36.227.170])
	by mail.postgresql.org (Postfix) with ESMTP id 3EEF9632AA8
	for <pgsql-performa...@postgresql.org>; Tue, 22 Sep 2009 20:52:25 -0300 (ADT)
X-Received: from dsl081-245-111.sfo1.dsl.speakeasy.net ([64.81.245.111] helo=Sidney-Stratton.local)
	by glacier.frostconsultingllc.com with esmtpsa (TLSv1:CAMELLIA256-SHA:256)
	(Exim 4.69)
	(envelope-from <j...@agliodbs.com>)
	id 1MqF9d-0006US-Pf; Tue, 22 Sep 2009 16:52:20 -0700
X-User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
X-To: Andy Colson <a...@squeakycode.net>
X-CC: Stef Telford <s...@ummon.com>, pgsql-performa...@postgresql.org
X-In-Reply-To: <4AB8E145.4040...@squeakycode.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5
 tests=BAYES_40=-0.185
X-Spam-Level: 
X-Mailing-List: pgsql-performance
X-List-Archive: <http://archives.postgresql.org/pgsql-performance>
X-List-Help: <mailto:majord...@postgresql.org?body=help>
X-List-ID: <pgsql-performance.postgresql.org>
X-List-Owner: <mailto:pgsql-performance-ow...@postgresql.org>
X-List-Post: <mailto:pgsql-performa...@postgresql.org>
X-List-Subscribe: <mailto:majord...@postgresql.org?body=sub%20pgsql-performance>
X-List-Unsubscribe: <mailto:majord...@postgresql.org?body=unsub%20pgsql-performance>
X-Precedence: bulk

Stef,

>>    is it as simple as taking the output from ; select indexrelname
>> from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and
>> idx_tup_fetch = 0 ;
>>
>>    And  .. dropping ?

Almost that simple.  The caveat is that indexes which are only used for
the enforcement of unique constraints (or other constraints) don't
count, but you don't want to drop them because they're required for the
constraints to work.

Also, if you have a large index with very low (but non-zero) scans, you
probably want to drop that as well.

Full query for that is here:
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

-- 
Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance