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
