Re: index stat

>>> On Mon, Nov 5, 2007 at 10:42 AM, in message
<B10E6810AC2A2F4EA7550D072CDE8760197DD5@SAB-FENWICK.sab.uiuc.edu>, "Campbell,
Lance" <lance@uiuc.edu> wrote:

> How can I [. . .] get rid of some unnecessary indexes

Here's what I periodically run to look for unused indexes:

select relname, indexrelname
  from pg_stat_user_indexes
  where indexrelname not like '%_pkey'
    and idx_scan = 0
  order by relname, indexrelname
;

We omit the primary keys from the list (based on our naming
convention) because they are needed to ensure integrity.

-Kevin