Re: Less rows -> better performance?

Andreas,

> I just verified that the autovacuum property is enabled. I did the following
> to prepare the tests:

"autovacuum property is enabled" Did you also check the logs, if
autovacuum is working?

> - setup two test databases, let's call them db_all and db_current
> - import the dump from the live DB into both test DBs
> - delete the old semester data from db_current, leaving only the current
> data
>
> Both test DBs were 600 MB large after this. I did a VACUUM FULL ANALYZE on
> both of them now. db_all didn't shrink significantly (only 1 MB), db_current
> shrunk to 440 MB.

Your test is not testing if vacuum is done on your production
database! With pg_dump + pg_restore you removed next to all database
bloat. (theoretically all)

After loading a fresh dump, vacuuming ideally has to do nearly
nothing; after deleting some data VACUUM reclaims the memory of the
deleted rows, thats the shrinking you see after delete + vacuum.

The bload in your production system may be the result of updates and
deletes in that system; dumping and restoring removes that bloat.

If your life DB is ~2,5Gig, and your dumped / restored DB is only
600MB, that 2500MB minus 600MB is some bloat from not vacuuming or
bloated indexes. So, before the start of the next semester, at least
do vacuum. (maybe also reindex)

Best wishes,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pidgeon
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!