Re: why postgresql is so slow?

Jeff <jdavis-pgsql@empires.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
couldget  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