Index scans for unique index not visible in stats

Summary:  Index scans to enforce a unique index do not show up in pg_stats
Severity:  Very Annoying
Verified On:  7.4.3, 7.4.6, 8.0.1
Description:
Index scans on the index of a unique constraint in order to verify uniqueness
of inserted rows do not show up in the pg_stats views.  This is a problem
because it can lead the DBA to think that index is not being used and is a
candidate for dropping.

Example:

powerpostgres=# create table unq_test ( id int not null primary key, the_data
text );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "unq_test_pkey"
for table "unq_test"
CREATE TABLE

powerpostgres=# insert into unq_test values ( 1, 'joe' );
INSERT 26277897 1
powerpostgres=# insert into unq_test values ( 2, 'mary' );
INSERT 26277898 1
powerpostgres=# insert into unq_test values ( 3, 'kevin' );
INSERT 26277899 1
powerpostgres=# insert into unq_test values ( 3, 'hal' );
ERROR:  duplicate key violates unique constraint "unq_test_pkey"

powerpostgres=# select * from pg_stat_user_indexes;
  relid   | indexrelid | schemaname |  relname   | indexrelname  | idx_scan |
idx_tup_read | idx_tup_fetch
----------+------------+------------+------------+---------------+----------+--------------+---------------
 26277890 |   26277895 | public     | unq_test   | unq_test_pkey |        0 |
0 |             0
(2 rows)

powerpostgres=# select * from pg_stat_user_tables;
  relid   |     schemaname     |         relname         | seq_scan |
seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del

----------+--------------------+-------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------
 26277890 | public             | unq_test                |        0 |
0 |        0 |             0 |         5 |         0 |         0



--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco