Re: Understanding pg_stat_user_indexes

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

> Josh Kupershmidt wrote:
>
> 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
>
> Hence the reason for my query - to find out the precise, detailed
> definition of those columns.
>
>
I dug a little to get a better idea. To make a long story short, if you'd
like to see exactly how the counter behind that "idx_scan" column gets
updated, put in an elog() statement next to the:
pgstat_count_index_scan(rel);
call around line 460 of nbtsearch.c (for B-Tree indexes). There are some
other places where pgstat_count_index_scan is used for GiST, GIN, and hash
indexes if you're interested in those index types as well. And it looks like
heap scans can also increment the t_numscans counter using the
pgstat_count_heapscan(rel) macro in a few places as well.

That should be enough to get you moving down the right road :-) If you'd
like to post a little "guide to idx_scan" containing a summary of when this
counter gets incremented, I'm sure others would be interested too.

>
> 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.
>

That's not quite true -- just because you have a primary key declared on a
table, doesn't mean that the index has been used in an index scan to fulfill
a query (a common reason this might happen would be because your PK is
actually a surrogate keys which is never queried directly). I tested this
myself with a trivial dummy table with a PK declared, running SELECTs which
were not against the PK column.

You can see for yourself with a simple example like this:

CREATE TABLE bar (name text PRIMARY KEY, value int UNIQUE);
INSERT INTO bar (name, value) SELECT a::text, a FROM generate_series(1,
100000) AS a;
SELECT * FROM pg_stat_user_indexes WHERE relname = 'bar';

that enforcing a primary key constraint or unique constraint doesn't cause
idx_scan to get bumped up.

Cheers,
Josh