There are a couple of issues here ...
- ANALYZE: Without statistical data, the Postgres planner often makes very bad choices which causes queries to run very slowly. Much more often than Oracle. After you run ANALYZE on a fairly large set of data, it figures out it should use indexes etc. Unless the distribution of your data changes greatly you don't really need to run ANALYZE all that often (though there is no reason not to)
- VACUUM: PostgreSQL doesn't reclaim space unless you tell it to explicitly. If you've got PG databases lying around that you're not VACUUMing then they'll still run fast (assuming you've run ANALYZE after your tables have grown to reasonable size) but ... they'll be much larger than necessary. Which can slow things down by causing more disk I/O etc but not nearly as drastically as the bad query plans that result from not running ANALYZE.
- We do lots of joins on three, four, five or more tables in OpenACS and that makes the bad decisions made by PG when you don't run ANALYZE even worse.
An easy way to run VACUUM ANALYZE is to mount the cronjob package and run it from there. Then you have a nice simple web interface for scheduling it...
