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

On Tue, Sep 22, 2009 at 7:35 PM, Stef Telford <stef(at)ummon(dot)com> 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.
>
>
Yes, those numbers can be used reliably to identify unused indexes.

Best regards,
--
Call it Postgres

EnterpriseDB http://www.enterprisedb.com

gurjeet[(dot)singh](at)EnterpriseDB(dot)com

singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device