SEO

#1 (**permalink**) 04-08-2008, 08:06 PM

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#2 (**permalink**) 04-08-2008, 08:06 PM

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

pg_atributes index space question