To: "Steven Flatt" <steven.flatt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time 
In-reply-to: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com> 
References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com>
Comments: In-reply-to "Steven Flatt" <steven.flatt@gmail.com>
	message dated "Wed, 13 Dec 2006 11:44:19 -0500"
Date: Wed, 13 Dec 2006 12:29:18 -0500
Message-ID: <9315.1166030958@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Steven Flatt" <steven.flatt@gmail.com> writes:
> Any idea where the bloat is happening?  I believe that if we were dropping
> and re-creating the temp table over and over, that could result in pg_class
> bloat (among other catalog tables), but what is going wrong if we use the
> same table over and over and truncate it?

That seems very strange --- I too would have expected a TRUNCATE to
bring you back to ground zero performance-wise.  I wonder whether the
issue is not directly related to the temp table but is just some generic
resource leakage problem in a very long-running backend.  Have you
checked to see if the backend process bloats memory-wise, or perhaps has
a huge number of files open (I wonder if it could be leaking open file
handles to the successive generations of the temp table)?  Are you sure
that the slowdown is specific to inserts into the temp table, as opposed
to generic SQL activity?

Also, which PG version is this exactly ("7.4" is not specific enough)?
On what platform?  Can you show us the full schema definition for the
temp table and any indexes on it?

			regards, tom lane