Re: VACUUM ANALYZE extremely slow - Mailing list pgsql-general

Hi. Sorry for being a bit emotional, I was pretty constructive in my
earlier posts (the earlier, the more constructive if you care to
search) but I am progressively getting pissed off :(

Thanks for the initial tip, running ANALYZE w/o vacuum is faster. Are
frequent vacuums even necessary if there are virtually no deletes in
the system and updates are much less frequent compared to inserts?

Now for the answers.
* What other non-default configuration settings do you have?
I played w/shared buffers, setting them between 16k and 32k,~ 24k
seems to be the best but the difference is minimal. The work_mem
setting is 256kb, and I increased effective cache size to ~700Mb (~35%
ram) based on a tip from some article.
max_fsm_* is increased too to accomodate vacuum analyze runs.

* What version of PostgreSQL are you using and on what OS?
8.1 and Debian

What kind of disks and controllers do you have?
Umm... Unfortunately I do not know, and do not have access to check :)
THe development server probably has a fast consumer grade HDD, not a
RAID I suppose.

* My "system has to go down" remark was probably a bit exaggerated;
the system is noticeably slower when the vacuum is running and that is
basically without workload; it will probably become unusable under
load.

* Specific queries that were slow: there are generally no specific
queries, everything runs slowly, mostly due to estimates being grossly
inaccurate (like 800k cost for a complex query based on Explain and
then 5, actual cost based on e.analyze)

I was not able to find any special bottlenecks in the queries. I come
from MSSQL (and a bit of MySQL) background, and the only thing I
noticed is that there are a lot of seqscans in the places where
indexes should be used (imo), when I turn seqscans off indexes are
ridiculously slow (I have already posted about it), presumably due to
random access used to build a big result set; however, result set is
not in fact big in real queries and postgres supposedly doesn't do
some magic that MSSQL does (join order or something) that makes the
join that should utilize the index narrower and thus faster.

There are also some weird development-stage-specific bottlenecks like
adding a column to 34m row table and updating it to default value (I
wasn't able to wait till the end of that query, it probably runs for
days); I think I understand why it is slow based on forum posts, but
again, MSSQL and MySQL do it better.

Also there's a general impression of everything being slow even after
MySQL on the same server; starting from seqscans to aggregate
operations; I won't be able to supply concrete evidence tho (not yet
anyway), it's just the gut feeling.