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: (view raw, whole thread or download thread mbox)
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



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-2017 The PostgreSQL Global Development Group