Thread: pg_atributes index space question

     Hello,

After a VACUUM FULL I saw that pg_attribute tables indexes haven't
been deleted as reported by a subsequent vacuum analyze. But the pages
corresponding to just the table has been deleted to 196 pages from
about 181557 pages. Are all system tables affected by this ? How can
we reclaim this space without shutting down postmaster ? Is this fixed
in any new release ?

db=# select relpages, reltuples from pg_catalog.pg_class where
relname = 'pg_attribute';
 relpages | reltuples
----------+-----------
      196 |      9965
(1 row)

db=# vacuum verbose analyze pg_catalog.pg_attribute;
INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  index "pg_attribute_relid_attnam_index" now contains 9965 row
versions in 181557 pages
DETAIL:  1518 index row versions were removed.
181263 index pages have been deleted, 20000 are currently reusable.

thanks

Joe Maldonado <jmaldonado@webehosting.biz> writes:
> db=# vacuum verbose analyze pg_catalog.pg_attribute;
> INFO:  vacuuming "pg_catalog.pg_attribute"
> INFO:  index "pg_attribute_relid_attnam_index" now contains 9965 row
> versions in 181557 pages

REINDEX is probably the only realistic way to fix that.  It shouldn't
take very long, fortunately, so the exclusive lock shouldn't be an
enormous problem.

            regards, tom lane