Re: Best COPY Performance

Michael (aka Worky),

On 10/28/06 5:03 AM, "Michael Artz" <mlartz(at)gmail(dot)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