From: Shaun Thomas <sthomas@peak6.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Benjamin Krajmalnik <kraj@servoyant.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Unused indices
Date: Thu, 24 Feb 2011 08:25:19 -0600
Message-ID: <4D666A4F.1060205@peak6.com> (raw)
In-Reply-To: <4D65795C.20404@2ndquadrant.com>
References: <F4E6A2751A2823418A21D4A160B689887B0F8E@fletch.stackdump.local>
<4D65795C.20404@2ndquadrant.com>
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@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com, greg@2ndquadrant.com, kraj@servoyant.com
Subject: Re: Unused indices
In-Reply-To: <4D666A4F.1060205@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox