On Fri, Feb 5, 2010 at 7:32 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote:
> Josh Kupershmidt wrote: > > On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford < > scrawf...@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. > > 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
