From: | "Luke Lonergan" <llonergan(at)greenplum(dot)com> |
---|---|
To: | "Hannu Krosing" <hannu(at)skype(dot)net> |
Cc: | "John A Meinel" <john(at)arbash-meinel(dot)com>, "Dan Harris" <fbsd(at)drivefaster(dot)net>, "bizgres-general" <bizgres-general(at)pgfoundry(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: [Bizgres-general] Re: faster INSERT with possible |
Date: | 2005-07-26 19:30:00 |
Message-ID: | BF0BDD48.9FFF%llonergan@greenplum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hannu,
On 7/26/05 11:56 AM, "Hannu Krosing" <hannu(at)skype(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Nuzum | 2005-07-26 19:35:17 | Re: faster INSERT with possible pre-existing row? |
Previous Message | John A Meinel | 2005-07-26 19:15:27 | Re: Cheap RAM disk? |