From: Steven Flatt <steven.flatt@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time
Date: Mon, 18 Dec 2006 12:51:13 -0500
Message-ID: <357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com> (raw)
In-Reply-To: <20792.1166211026@sss.pgh.pa.us>
References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com>
<25209.1166131396@sss.pgh.pa.us>
<357fa7590612150821i3eca3f66ge2ced1ee20fa2dac@mail.gmail.com>
<11651.1166202586@sss.pgh.pa.us>
<357fa7590612150955g1b9c733duaf000c23d28aa8af@mail.gmail.com>
<12508.1166206006@sss.pgh.pa.us>
<357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com>
<13947.1166209788@sss.pgh.pa.us>
<357fa7590612151121x5f95102fj21a96b1bdc7eb002@mail.gmail.com>
<20792.1166211026@sss.pgh.pa.us>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: steven.flatt@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Insertion to temp table deteriorating over time
In-Reply-To: <357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox