Hunting Unused Indexes .. is it this simple ?

From: Stef Telford <stef(at)ummon(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Hunting Unused Indexes .. is it this simple ?
Date: 2009-09-22 14:05:30
Message-ID: 4AB8D9AA.6050007@ummon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2009-09-22 14:18:11 Re: High CPU load on Postgres Server during Peak times!!!!
Previous Message Shiva Raman 2009-09-22 13:54:44 High CPU load on Postgres Server during Peak times!!!!