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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly