X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id BC75354A0E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 May 2005 20:29:30 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 36260-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  4 May 2005 23:29:29 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196])
	by svr1.postgresql.org (Postfix) with ESMTP id 78C8E549BF
	for <pgsql-performance@postgresql.org>;
	Wed,  4 May 2005 20:29:28 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id 68so416174wri
	for <pgsql-performance@postgresql.org>;
	Wed, 04 May 2005 16:29:29 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ON3czTN41G3HV3pD4tDSX4Do6uhtkdrjQzrrmJOfYIZihyhQCovtkrrg15JWRGyDFCZmIsbFXtdlovUV/fZ1g1vm70pOJW7o4m2dJhc+tQSvPu8GxHK0BtcrsrFOv1ynUMdtxbKIC6eAz85liZeyNUO1a0FXRNXuQYZeecSFC88=
Received: by 10.54.153.19 with SMTP id a19mr405095wre;
	Wed, 04 May 2005 16:29:29 -0700 (PDT)
Received: by 10.54.120.16 with HTTP; Wed, 4 May 2005 16:29:29 -0700 (PDT)
Message-ID: <59d991c40505041629449b2b1b@mail.gmail.com>
Date: Wed, 4 May 2005 19:29:29 -0400
From: Christopher Petrilli <petrilli@gmail.com>
Reply-To: Christopher Petrilli <petrilli@gmail.com>
To: Mischa Sandberg <mischa.sandberg@telus.net>
Subject: Re: COPY vs INSERT
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1115248292.427956a468a55@webmail.telus.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <OF93C70832.2AC188CE-ON85256FF6.0056B587-85256FF6.005774C2@us.ibm.com>
	<21154.1115136741@sss.pgh.pa.us>
	<1115146397.4277c89dc37b4@webmail.telus.net>
	<1115246490.1709.233393619@webmail.messagingengine.com>
	<1115248292.427956a468a55@webmail.telus.net>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.318 tagged_above=0 required=5 tests=AWL,
	DNS_FROM_RFC_ABUSE, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200505/43
X-Sequence-Number: 12207

On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
>=20
> > > COPY invokes all the same logic as INSERT on the server side
> > > (rowexclusive locking, transaction log, updating indexes, rules).
> > > The difference is that all the rows are inserted as a single
> > > transaction. This reduces the number of fsync's on the xlog,
> > > which may be a limiting factor for you. You'll want to crank
> > > WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> > > One of my streams has 6K records; I run with WB=3D1000, CS=3D128.
> >
> > So what's the difference between a COPY and a batch of INSERT
> > statements.  Also, surely, fsyncs only occur at the end of a
> > transaction, no need to fsync before a commit has been issued,
> > right?
>=20
> Sorry, I was comparing granularities the other way araound. As far as
> xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
> putline commands you use to feed the copy. With inserts, you can choose
> whether to commit every row, every nth row, etc.
>=20
> Copy makes better use of the TCP connection for transmission. COPY uses
> the TCP connection like a one-way pipe. INSERT is like an RPC: the
> sender has to wait until the insert's return status roundtrips.

I have found even greater performance increases by using COPY FROM
<filename> not COPY FROM STDIN.  This allows the backend process to
directly read the file, rather than shoving it over a pipe (thereby
potentially hitting the CPU multiple times).  My experience is that
this is anywhere from 5-10x faster than INSERT statements on the
whole, and sometimes 200x.

Chris

--=20
| Christopher Petrilli
| petrilli@gmail.com