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: 357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com (view raw or flat)
Thread:
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:

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
>

In response to

Responses

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-2014 The PostgreSQL Global Development Group