Re: Best COPY Performance

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