Re: Insertion to temp table deteriorating over time

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