Re: Understanding pg_stat_user_indexes

On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> What causes an increment to idx_scan in pg_stat_user_indexes? "Select"
> queries only? When used to enforce column uniqueness? When used for
> foreign-key constraints?
>
>
The docs are a little sparse here:

http://developer.postgresql.org/pgdocs/postgres/monitoring-stats.html

but I'm assuming that counter gets incremented every time there's an index
scan using the index in question, which could occur with SELECT, UPDATE,
DELETE, etc. queries which make use of this index. See this thread for
related questions and answers, and the note about bitmap index scans:
http://archives.postgresql.org/pgsql-admin/2006-01/msg00319.php

> I see lots of indexes with an idx_scan-count of zero but which are not
> actually superfluous as they are required to enforce constraints.
>
> Ultimately, I'm trying to come up with a better way to search for truly
> useless indexes.
>
>
You'll probably find this blog post useful:
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796

Josh