pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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 18:06:04 -0500
Message-ID: <357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com> (raw)
In-Reply-To: <19298.1166464855@sss.pgh.pa.us>
References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@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>
	<357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com>
	<19298.1166464855@sss.pgh.pa.us>

Please ignore my post from earlier today.  As strange as it sounds, changing
"CREATE TEMP TABLE ... AS" to "CREATE TEMP TABLE ... LIKE" appeared to fix
my performance problem because things errored out so quickly (and silently
in my test program).  After checking the pgsql logs, it became clear to me
that you can't use LIKE on a view.  Duh.

Moving forward, I have also discovered that our temp table did in fact have
a varchar column (no specified limit on varchar).  With this in mind, I
could easily reproduce the problem on a temp table with one column.  So...

Issue #1:

(I'm assuming there's a reasonable explanation for this.)  If I create a
temp table with a single varchar column (or text column), do 100 inserts to
that table, copy to a permanent table, truncate the temp table and repeat,
the time required for the 100 inserts grows almost linearly.  Maybe the data
is treated as large objects.

Note that if I change the column type to varchar(SOME_LIMIT), integer,
timestamptz, interval, etc., performance does not degrade.  Also note that
if I do not use a temp table (but do use a varchar column), inserts are
slower (as expected) but do not degrade over time.  So this seems to be
specific to temp tables with varchar/text column(s).

Issue #2:

As I said earlier, the temp table is created via:

CREATE TEMP TABLE tmp AS SELECT <column-list> FROM perm LIMIT 0;

where perm is a view defined as follows:

View definition:
 SELECT <column-list>
   FROM view2
   JOIN tbl USING (col1, col2)
  WHERE <some-conditions>
UNION ALL
 SELECT <column-list>
   FROM view3
   JOIN tbl USING (col1, col2)
  WHERE <some-conditions>;

Now the varchar columns that end up in the perm view come from the tbl
table, but in tbl, they are defined as varchar(40).  Somehow the 40 limit is
lost when constructing the view.  After a little more testing, I found that
this problem only occurs when you are creating a view (i.e. CREATE TABLE ...
AS does not observe this problem) and also that the UNION ALL clause must be
present to observe this problem.

This looks like a bug.  I know this is Postgres 7.4.6 and I haven't been
able to verify with a later version of Postgres, but does this look familiar
to anyone?

Steve

view thread (22+ messages)  latest in thread

Message-ID: <357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com>
Permalink:  ../357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com/
Also on:    postgresql.org/message-id/357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  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: <357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com>
  References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@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> <357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com> <19298.1166464855@sss.pgh.pa.us> <357fa7590612181506r69a17e37x271623007f27c877@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