Re: why postgresql is so slow?

Jeff <jdavis-pgsql(at)empires(dot)org> wrote:
> Then run "VACUUM ANALYZE" every once in a while (depending on how
> fast your data changes), like every night for instance.

Consider VACUUM and ANALYZE somewhat separately.

You need to ANALYZE any time the distribution of the data changes.

You need to VACUUM any time a lot of data is invalidated by UPDATEs or
DELETEs. (And when you VACUUM, it's cheap to throw in an ANALYZE.)

That turns into a variety of policies depending on what the pattern of
activity for a particular table is:

- If a table only ever gets INSERTs, you need only ever ANALYZE it,
as VACUUM should be useless. (Caveat: If INSERTs could get
rolled back due to other processing nuking transactions, that
leaves dead tuples for VACUUM to work with...)

- A table that sees enormous numbers of updates (for instance, a list
of account balances) should be vacuumed REALLY frequently.

- Any time you do a VACUUM, you might as well also do an ANALYZE.

pg_autovacuum applies these policies, so it's a reasonable thing to
use in 7.3/7.4...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/
"One of the most dangerous things in the universe is an ignorant
people with real grievances. That is nowhere near as dangerous,
however, as an informed and intelligent society with grievances. The
damage that vengeful intelligence can wreak, you cannot even imagine."
-- Miles Teg, Heretics of Dune