Re: Understanding pg_stat_user_indexes

Josh Kupershmidt wrote:
> On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)com
> <mailto: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
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