Hannu,
On 7/26/05 11:56 AM, "Hannu Krosing" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
