The following bug has been logged online:

Bug reference: 2358
Logged by: Alexis Wilke
Email address: alexis (AT) m2osw (DOT) com
PostgreSQL version: 8.0.1
Operating system: Linux 2.4.20-8 i686
Description: Vacuum & \dt problems
Details:

I have a database which is fairly small (pgdump in SQL text format is just
about 600Kb, really small! the largest table is 12 columns and 2635 rows...)
and yet after I played with the database after a day or so, it gets dead
slow when I try VACUUM it or worse when I attempt a \dt command on it.

The vacuum can sit there for over 1h doing nothing much since the CPU usage
is less than 1% most of the time. It seems to me this is because it is
taking all its time querying the large files (my assumption because my
system locks up intermittendly when I run the vacuum, as if it needed to
access the drive and was short in resources).

I have 1Gb of RAM though... it's not huge, but I'd hope it should be
enough... except there are 3 1Gb files in that database folder!

Similarly, the \dt can take a good 20 min. before listing anything. Even the
2nd time! And that's if I don't vacuum for a few days only.

Now this could come from my hard drive too... but on the other hand, all the
SQL commands are dead fast! No worries at all with those (be it SELECT,
UPDATE or INSERT). So I'm not too sure why VACUUM can be so slow... I've
seen some talks about using REINDEX instead of VACUUM... would that help and
is that enough to 'clean up' the database?!

As an extra note, I have had the following error:

xmlc=# vacuum;
ERROR: failed to re-find parent key in "pg_class_relname_nsp_index"

which seems to have been fixed with a REINDEX on that table. It seems that's
a rare hard to reproduce one.

Thank you for your input!
Alexis Wilke

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

It sounds like you probably ran for a long time (or more accurately did
a large number of updates/deletes) without vacuuming. Due to the way
vacuum works, this would result in a very large amount of wasted space.

While you could VACUUM FULL and REINDEX to fix this, in your case you'd
probably be best off just dumping the database and reloading.

Also, 8.0.1 is very old. You should really upgrade to at least 8.0.7, or
8.1.3.

On Sun, Mar 26, 2006 at 12:01:09AM +0000, Alexis Wilke wrote:
--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings