I have an update on this.
The reason I couldn't reproduce this problem was because of the way I was creating the temp table in my tests. I was using:
CREATE TEMP TABLE tmp (LIKE perm);
This did not observe performance degradation over time.
However, the way our application was creating this table (something I should have observed sooner, no doubt) is:
CREATE TEMP TABLE tmp AS SELECT <column-list> FROM perm LIMIT 0;
This, on its own however, is not enough to reproduce the problem. Next imagine that perm is actually a view, which is defined as a UNION ALL SELECT from several other views, and those views are also defined as UNION ALL SELECTs from a bunch of permanent tables. All views have insert rules redirecting rows according to some criteria. The whole structure is pretty convoluted.
I can fix this problem by using CREATE TEMP TABLE ... LIKE instead of CREATE TEMP TABLE ... AS.
I'm still curious about the root cause of this problem. From the docs, I see that CREATE TABLE AS evaluates the query just once to create the table, but based on what I'm seeing, I'm wondering whether this isn't truly the case. Are there any known issues with CREATE TABLE AS when the table you're creating is temporary and you're selecting from a view?
Steve
On 12/15/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:"Steven Flatt" <steven.flatt@gmail.com> writes:
> I've been trying to reproduce the problem for days now :). I've done pretty
> much exactly what you describe below, but I can't reproduce the problem on
> any of our lab machines. Something is indeed special in this environment.
Yuck. You could try strace'ing the problem backend and see if anything
is visibly different between fast and slow operation. I don't suppose
you have oprofile on that machine, but if you did it'd be even better.
regards, tom lane