On Thu, Jul 28, 2005 at 03:12:33PM -0400, Greg Stark wrote: > I think occasionally people get bitten by not having their pg_* tables being > vacuumed or analyzed regularly. If you have lots of tables and the stats are > never updated for pg_class or related tables you can find the planner taking a > long time to plan queries. > > This happens if you schedule a cron job to do your vacuuming and analyzing but > connect as a user other than the database owner. For example, you leave the > database owned by "postgres" but create a user to own all the tables and use > that to run regularly scheduled "vacuum analyze"s. > > I'm not sure how often these types of problems get properly diagnosed. The > symptoms are quite mysterious. In retrospect I think I observed something like > it and never figured out what was going on. The problem only went away when I > upgraded the database and went through an initdb cycle.
I've had exactly this problem at least five times, twice on my own systems and three times that I noticed on customer machines. It's an easy mistake to make on a system that doesn't have much interactive use, and if you're creating and dropping a lot of tables it can devastate your performance after a while. Cheers, Steve ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
