Skip site navigation (1) Skip section navigation (2)

Re: Insertion to temp table deteriorating over time

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Insertion to temp table deteriorating over time
Date: 2006-12-18 17:51:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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:


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

I can fix this problem by using CREATE TEMP TABLE ... LIKE instead of CREATE

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?


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

In response to


pgsql-performance by date

Next:From: Tom LaneDate: 2006-12-18 18:00:55
Subject: Re: Insertion to temp table deteriorating over time
Previous:From: Jeff FrostDate: 2006-12-18 17:20:57
Subject: Re: opportunity to benchmark a quad core Xeon

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group