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

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

From: Andy Colson <andy(at)squeakycode(dot)net>
To: 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 14:37:57
Message-ID: 4AB8E145.4040901@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
Stef Telford wrote:
> Hey Everyone,
>    So, I have a nice postgreSQL server (8.4) up and running our 
> database. I even managed to get master->slave going without trouble 
> using the excellent skytools.. however, I want to maximize speed and the 
> hot updates where possible, so, I am wanting to prune unused indexes 
> from the database.
> 
>    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 ?
> 
> 
>    The reason I ask is, well, the count on that gives me 750 indexes 
> where-as the count on all user_indexes is 1100. About 2/3rds of them are 
> obsolete ? I did do an ETL from mySQL -> postgreSQL but.. that's still a 
> ridiculous amount of (potentially) unused indexes.
> 
>    Regards
>    Stef
> 

Did you google that? I recall seeing some posts like that on planet 
postgres.

Yea, here it is:

http://radek.cc/2009/09/05/psqlrc-tricks-indexes/

google turns up several for "postgres unused indexes".  I havent read 
any of the others, not sure how good they are.

-Andy

In response to

Responses

pgsql-performance by date

Next:From: Fernando HeviaDate: 2009-09-22 15:29:52
Subject: Re: High CPU load on Postgres Server during Peak times!!!!
Previous:From: Gurjeet SinghDate: 2009-09-22 14:35:55
Subject: Re: Hunting Unused Indexes .. is it this simple ?

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