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 860F05322C
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu,  5 May 2005 21:53:40 -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 02582-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri,  6 May 2005 00:53:30 +0000 (GMT)
Received: from mxsf24.cluster1.charter.net (mxsf24.cluster1.charter.net
	[209.225.28.224])
	by svr1.postgresql.org (Postfix) with ESMTP id 4A8EB52D44
	for <pgsql-performance@postgresql.org>;
	Thu,  5 May 2005 21:53:29 -0300 (ADT)
Received: from mxip17.cluster1.charter.net (mxip17a.cluster1.charter.net
	[209.225.28.147])
	by mxsf24.cluster1.charter.net (8.12.11/8.12.11) with ESMTP id
	j460rWw9001085
	for <pgsql-performance@postgresql.org>; Thu, 5 May 2005 20:53:32 -0400
Received: from pc-68-118-180-13.will.ct.charter.com (HELO [192.168.116.102])
	(68.118.180.13)
	by mxip17.cluster1.charter.net with ESMTP; 05 May 2005 20:53:32 -0400
X-Ironport-AV: i="3.92,158,1112587200";
	d="scan'208"; a="1057003439:sNHT13881740"
Message-ID: <427AC00B.3030500@NarrowPathInc.com>
Date: Thu, 05 May 2005 20:53:31 -0400
From: Keith Worthington <KeithW@NarrowPathInc.com>
Reply-To: KeithW@NarrowPathInc.com
Organization: Narrow Path, Inc.
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Christopher Petrilli <petrilli@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: COPY vs INSERT
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>
	<59d991c40505041629449b2b1b@mail.gmail.com>
In-Reply-To: <59d991c40505041629449b2b1b@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.068 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200505/52
X-Sequence-Number: 12216

Christopher Petrilli wrote:
> On 5/4/05, Mischa Sandberg <mischa.sandberg@telus.net> wrote:
> 
>>Quoting David Roussel <pgsql-performance@diroussel.xsmail.com>:
>>
>>
>>>>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=1000, CS=128.
>>>
>>>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?
>>
>>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.
>>
>>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
> 

Unfortunately, COPY FROM '<file>' can only be done by a superuser.  If 
you that option then that is great.  If not...

-- 
Kind Regards,
Keith