User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Sat, 28 Oct 2006 08:41:55 -0700
Subject: Re: Best COPY Performance
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Michael Artz" <mlartz@gmail.com>
cc: "Worky Workerson" <worky.workerson@gmail.com>,
	"Merlin Moncure" <mmoncure@gmail.com>, pgsql-performance@postgresql.org
Message-ID: <C168C853.55FD%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Best COPY Performance
Thread-Index: Acb6p5hm1rBhSGaaEduGtgAWy4o9DA==
In-Reply-To: <e9c163070610280503t4788ab5ah857ea249b5027e2@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Michael (aka Worky),

On 10/28/06 5:03 AM, "Michael Artz" <mlartz@gmail.com> wrote:

> PG 8.1.3 x86_64.  I installed it via a RH rpm for their "Web Services
> Beta", or something like that.  I know I'm a bit behind the times, but
> getting stuff in (and out) of my isolated lab is a bit of a pain.
> I'll compile up a 8.2 beta as well and see how that works out.

I think 8.1 and 8.2 should be the same in this regard.

Maybe it is just the PK *build* that slows it down, but I just tried some
small scale experiments on my MacBook Pro laptop (which has the same disk
performance as your server) and I get only a 10-15% slowdown from having a
PK on an integer column.  The 10-15% slowdown was on 8.1.5 MPP, so it used
both CPUs to build the index and load at about 15 MB/s.

Note that the primary key is the first column.

                Table "public.part"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 p_partkey     | integer               | not null
 p_name        | character varying(55) | not null
 p_mfgr        | text                  | not null
 p_brand       | text                  | not null
 p_type        | character varying(25) | not null
 p_size        | integer               | not null
 p_container   | text                  | not null
 p_retailprice | double precision      | not null
 p_comment     | character varying(23) | not null
Indexes:
    "part_pkey" PRIMARY KEY, btree (p_partkey)

What is your schema for the table?

- Luke