[Bizgres-general] Re: [PERFORM] faster INSERT with possible pre-existing row?

[Luke Lonergan]

Hannu,

On 7/26/05 11:56 AM, "Hannu Krosing" <hannu at 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