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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
