Re: Insertion to temp table deteriorating over time

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Steven Flatt" <steven(dot)flatt(at)gmail(dot)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
>