Re: Unused indices

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books