Re: Unused indices

On 02/23/2011 03:17 PM, Greg Smith wrote:

> Yes. The block usage you're seeing there reflects the activity from
> maintaining the index. But since it isn't ever being used for
> queries, with zero scans and zero rows it's delivered to clients,

Nice to know. To that end, here's a query that will find every unused
index in your database:

SELECT i.schemaname, i.relname, i.indexrelname, c.relpages*8 indsize
FROM pg_stat_user_indexes i
JOIN pg_class c on (i.indexrelid=c.oid)
JOIN pg_index ix ON (i.indexrelid=ix.indexrelid)
WHERE i.idx_scan = 0
AND i.idx_tup_read = 0
AND i.schemaname NOT IN ('zzz', 'archive')
AND NOT ix.indisprimary
AND c.relpages > 0
ORDER BY indsize DESC;

I noticed with our database that without the indisprimary clause, we had
another 4GB of unused indexes. Clearly we need to look at those tables
in general, but this will find all the "safe" indexes for removal.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email