Wi-Fizzle.com - Putting the fizzle in Wi-Fi since 2005 .. (yes, this was a poor choice for a domain name)

<div class="news_item">#380<div class="news_title">Good Postgresql Tip: Running Vacuum Recommended

Posted by dandriff on Sunday July 26, 2009@02:27PM

Running VACUUM recommended

The estimated rowcount on the table "release" deviates significantly from the actual rowcount. You should run VACUUM ANALYZE on this table.

Instead of issuing a manual VACUUM ANALYZE command on this table (you can use the pgAdmin III maintenance menu for this), running VACUUM ANALYZE on a regular or automated basis should be considered. This can be achieved using a scheduler. PostgreSQL also supplies the autovacuum daemon, which will track changes made to the database and issue vacuum commands as required automatically. In most cases, autovacuum will be the best choice.

What is VACUUM good for?

The PostgreSQL query planner makes its decision based on assumptions, taken from the estimated rowcount. If the actual rowcount is too different from the estimated rowcount, the planner might take a wrong decision, resulting in a query plan that is not optimal. This might result in poor performance.

The PostgreSQL storage needs VACUUM to have transaction IDs in tables fixed. Additionally, outdated rows from updates and deletes are not cleaned up until the VACUUM command has been issued on that table. In-depth information can be found in the online documentation, just press the help button.