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