User-Agent: Microsoft-Entourage/11.1.0.040913
Date: Tue, 26 Jul 2005 12:30:00 -0700
Subject: Re: [Bizgres-general] Re: faster INSERT with possible
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Hannu Krosing" <hannu@skype.net>
Cc: "John A Meinel" <john@arbash-meinel.com>,
	"Dan Harris" <fbsd@drivefaster.net>,
	"bizgres-general" <bizgres-general@pgfoundry.org>,
	pgsql-performance@postgresql.org
Message-ID: <BF0BDD48.9FFF%llonergan@greenplum.com>
In-Reply-To: <1122404195.31930.4.camel@fuji.krosing.net>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Hannu,

On 7/26/05 11:56 AM, "Hannu Krosing" <hannu@skype.net> wrote:

> On T, 2005-07-26 at 11:46 -0700, Luke Lonergan wrote:
> 
>> Yah - that's a typical approach, and it would be excellent if the COPY
>> bypassed WAL for the temp table load.
> 
> Don't *all* operations on TEMP tables bypass WAL ?

Good question - do they?  We had discussed the bypass as an elective option,
or an automated one for special conditions (no index on table, empty table)
or both.  I thought that temp tables was one of those special conditions.

Well - now that I test it, it appears you are correct, temp table COPY
bypasses WAL - thanks for pointing it out!

The following test is on a load of 200MB of table data from an ASCII file
with 1 text column of size 145MB.

- Luke

===================== TEST ===========================
dgtestdb=# create temporary table temp1 (a text);
CREATE TABLE
dgtestdb=# \timing
Timing is on.
dgtestdb=# \i copy.ctl
COPY
Time: 4549.212 ms
dgtestdb=# \i copy.ctl
COPY
Time: 3897.395 ms

-- that's two tests, two loads of 200MB each, averaging 4.2 secs

dgtestdb=# create table temp2 as select * from temp1;
SELECT
Time: 5914.803 ms

-- a quick comparison to "CREATE TABLE AS SELECT", which bypasses WAL
-- on bizgres

dgtestdb=# drop table temp1;
DROP TABLE
Time: 135.782 ms
dgtestdb=# drop table temp2;
DROP TABLE
Time: 3.707 ms
dgtestdb=# create table temp1 (a text);
CREATE TABLE
Time: 1.667 ms
dgtestdb=# \i copy.ctl
COPY
Time: 6034.274 ms
dgtestdb=# 

-- This was a non-temporary table COPY, showing the slower performance of 6
secs.

- Luke