Re: VACUUM ANALYZE extremely slow

Sergei Shelukhin <realgeek(at)gmail(dot)com> wrote:
> This is my first (and, by the love of the God, last) project w/pgsql
> and everything but the simplest selects is so slow I want to cry.

Please post an example query and its EXPLAIN ANALYZE output. The
pgsql-performance mailing list is a good place to discuss performance
problems.

> This is especially bad with vacuum analyze - it takes several hours
> for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM
> and virtually no workload at the moment. Maintenance work mem is set
> to 512 Mb.

What other non-default configuration settings do you have? What
version of PostgreSQL are you using and on what OS? What kind of
disks and controllers do you have?

> Is there any way to speed up ANALYZE? Without it all the queries run
> so slow that I want to cry after a couple of hours of operation and
> with it system has to go down for hours per day and that is
> unacceptable.

Why does the system have to go down? Are you running VACUUM FULL
ANALYZE? If so then drop the FULL and do an ordinary VACUUM ANALYZE
instead -- it should run faster and it doesn't require exclusive
access to the table.

As Christopher Browne mentioned, a bare ANALYZE (without VACUUM)
should be fast even on large tables so if necessary you could run
ANALYZE more often than VACUUM ANALYZE.

Have you enabled autovacuum (or contrib/pg_autovacuum in 8.0 and
earlier)? I sometimes prefer to run VACUUM ANALYZE manually but
for many databases autovacuum is a good way to maintain statistics
and clean up dead rows automatically.

--
Michael Fuhr