ok, Thanks a lot for your time guys ! I guess my table is pretty unusual and thats why this problem has not surfaced until now. Better late then never ;) I'll cron a "manual" vacuum full on the table.
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Otto Blomqvist wrote: > >> This table is basically a queue full of records waiting to get transfered > >> over from our 68030 system to the PG database. The records are then moved > >> into folders (using a trigger) like file_92_myy depending on what month the > >> record was created on the 68030. During normal operations there should not > >> be more than 10 records at a time in the table, although during the course > >> of a day a normal system will get about 50k records. I create 50000 records > >> to simulate incoming traffic, since we don't have much traffic in the test > >> lab. > > Really the right way to do housekeeping for a table like that is to > VACUUM FULL (or better yet, TRUNCATE, if possible) immediately after > discarding a batch of records. The VACUUM FULL will take very little > time if it only has to repack <10 records. Plain VACUUM is likely to > leave the table nearly empty but physically sizable, which is bad news > from a statistical point of view: as the table fills up again, it won't > get physically larger, thereby giving the planner no clue that it > doesn't still have <10 records. This means the queries that process > the 50K-record patch are going to get horrible plans :-( > > I'm not sure if autovacuum could be taught to do that --- it could > perhaps launch a vacuum as soon as it notices a large fraction of the > table got deleted, but do we really want to authorize it to launch > VACUUM FULL? It'd be better to issue the vacuum synchronously > as part of the batch updating script, I feel. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend