Message-ID: <4D669FB8.8010001@2ndquadrant.com>
Date: Thu, 24 Feb 2011 13:13:12 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: sthomas@peak6.com
CC: Benjamin Krajmalnik <kraj@servoyant.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Unused indices
References: <F4E6A2751A2823418A21D4A160B689887B0F8E@fletch.stackdump.local>
	<4D65795C.20404@2ndquadrant.com> <4D666A4F.1060205@peak6.com>
In-Reply-To: <4D666A4F.1060205@peak6.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Shaun Thomas wrote:
> I noticed with our database that without the indisprimary clause, we 
> had another 4GB of unused indexes.

That's not quite the right filter.  You want to screen out everything 
that isn't a unique index, not just the primary key ones.  You probably 
can't drop any of those without impacting database integrity.

Also, as a picky point, you really should use functions like 
pg_relation_size instead of doing math on relpages.  Your example breaks 
on PostgreSQL builds that change the page size, and if you try to 
compute bytes that way it will overflow on large tables unless you start 
casting things to int8.

Here's the simplest thing that does something useful here, showing all 
of the indexes on the system starting with the ones that are unused:

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
  pg_stat_user_indexes i
  JOIN pg_index USING (indexrelid)
WHERE
  indisunique IS false
ORDER BY idx_scan,relname;

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books