pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Luke Lonergan <llonergan@greenplum.com>
To: Hannu Krosing <hannu@skype.net>
Cc: John A Meinel <john@arbash-meinel.com>
Cc: Dan Harris <fbsd@drivefaster.net>
Cc: bizgres-general <bizgres-general@pgfoundry.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: [Bizgres-general] Re: faster INSERT with possible
Date: Tue, 26 Jul 2005 12:30:00 -0700
Message-ID: <BF0BDD48.9FFF%llonergan@greenplum.com> (raw)
In-Reply-To: <1122404195.31930.4.camel@fuji.krosing.net>

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





view thread (6+ messages)  latest in thread

Message-ID: <BF0BDD48.9FFF%llonergan@greenplum.com>
Permalink:  ../BF0BDD48.9FFF%25llonergan@greenplum.com/
Also on:    postgresql.org/message-id/BF0BDD48.9FFF%llonergan@greenplum.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: llonergan@greenplum.com, hannu@skype.net, john@arbash-meinel.com, fbsd@drivefaster.net, bizgres-general@pgfoundry.org
  Subject: Re: [Bizgres-general] Re: faster INSERT with possible
  In-Reply-To: <BF0BDD48.9FFF%llonergan@greenplum.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