Sergei Shelukhin <realgeek@xxxxxxxxx> 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
