Message-ID: <4D666A4F.1060205@peak6.com>
Date: Thu, 24 Feb 2011 08:25:19 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: sthomas@peak6.com
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.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>
In-Reply-To: <4D65795C.20404@2ndquadrant.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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