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

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

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: Stef Telford <stef(at)ummon(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Hunting Unused Indexes .. is it this simple ?
Date: 2009-09-22 14:35:55
Message-ID: 65937bea0909220735i223cfc9bsb361b99604a02a7c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Andy ColsonDate: 2009-09-22 14:37:57
Subject: Re: Hunting Unused Indexes .. is it this simple ?
Previous:From: Andy ColsonDate: 2009-09-22 14:35:26
Subject: Re: High CPU load on Postgres Server during Peak times!!!!

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