Skip site navigation (1) Skip section navigation (2)

Index scans for unique index not visible in stats

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Index scans for unique index not visible in stats
Date: 2005-02-09 23:16:32
Message-ID: 200502091516.32970.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

pgsql-bugs by date

Next:From: Brian B.Date: 2005-02-10 02:37:30
Subject: BUG #1473: Backend bus error, possibly due to ANALYZE
Previous:From: Bruce MomjianDate: 2005-02-09 22:42:38
Subject: Re: BUG #1468: psql_dump is not backward compatible

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group