Обсуждение: Understanding pg_stat_user_indexes
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? 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. Cheers, Steve
On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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
You'll probably find this blog post useful:
http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796
Josh
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
Josh Kupershmidt wrote:
-Steve
On Fri, Feb 5, 2010 at 12:02 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:Hence the reason for my query - to find out the precise, detailed definition of those columns.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
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.
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
-Steve
On Fri, Feb 5, 2010 at 7:32 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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.
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
Josh Kupershmidt wrote:Hence the reason for my query - to find out the precise, detailed definition of those columns.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
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.
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.
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
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