The following bug has been logged online:
Bug reference: 2358
Logged by: Alexis Wilke
Email address: alexis@m2osw.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
