Skip site navigation (1) Skip section navigation (2)

Re: Hunting Unused Indexes .. is it this simple ?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Andy Colson <andy(at)squeakycode(dot)net>
Cc: Stef Telford <stef(at)ummon(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Hunting Unused Indexes .. is it this simple ?
Date: 2009-09-22 23:52:17
Message-ID: 4AB96331.6070902@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Stef,

>>    is it as simple as taking the output from ; select indexrelname
>> from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and
>> idx_tup_fetch = 0 ;
>>
>>    And  .. dropping ?

Almost that simple.  The caveat is that indexes which are only used for
the enforcement of unique constraints (or other constraints) don't
count, but you don't want to drop them because they're required for the
constraints to work.

Also, if you have a large index with very low (but non-zero) scans, you
probably want to drop that as well.

Full query for that is here:
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

In response to

pgsql-performance by date

Next:From: Magnus HaganderDate: 2009-09-23 07:58:30
Subject: Re: statement stats extra load?
Previous:From: Scott MarloweDate: 2009-09-22 18:22:00
Subject: Re: session servers in ram

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group