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