Thanks for your replies.
Starting a fresh session (not restarting the postmaster) seems to be sufficient to reset performance (and is an easy enough workaround). Still, it would be nice to know the root cause of the problem.
The backend process does not seem to be bloating memory-wise (I'm using vmstat to monitor memory usage on the machine). It also does not appear to be bloating in terms of open file handles (using fstat, I can see the backend process has 160-180 open file handles, not growing).
Regarding your other email -- interesting -- but we are vacuuming pg_class every hour. So I don't think the answer lies there...
Steve
On 12/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:"Steven Flatt" <steven.flatt@gmail.com> writes:
> Having said that, what kinds of things should I be looking for that could
> deteriorate/bloat over time? Ordinarily the culprit might be infrequent
> vacuuming or analyzing, but that wouldn't be corrected by a restart of
> Postgres. In our case, restarting Postgres gives us a huge performance
> improvement (for a short while, anyways).
Do you actually need to restart the postmaster, or is just starting a
fresh session (fresh backend) sufficient? And again, have you monitored
the backend process to see if it's bloating memory-wise or open-file-wise?
regards, tom lane