Re: Understanding pg_stat_user_indexes

Josh Kupershmidt wrote:

On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford <scrawford@pinpointresearch.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

Hence the reason for my query - to find out the precise, detailed definition of those columns.

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

Well, not really. If that were true, every table with a primary key would show index-scans on the associated index - a necessary condition of determining uniqueness. But I have tables with primary-keys, thousands to millions of rows, and zero index-scans on the primary-key index.

-Steve