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 6E42454A13 for ; Wed, 4 May 2005 20:24:01 -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 35087-10 for ; Wed, 4 May 2005 23:23:58 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 93F2254A0D for ; Wed, 4 May 2005 20:23:57 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j44NNvZ6021224; (envelope-from ) Wed, 4 May 2005 18:23:57 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j44NNttB008933 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 4 May 2005 18:23:56 -0500 (CDT) Message-ID: <4279598B.5010908@arbash-meinel.com> Date: Wed, 04 May 2005 18:23:55 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: David Roussel Cc: Mischa Sandberg , pgsql-perform Subject: Re: COPY vs INSERT References: <21154.1115136741@sss.pgh.pa.us> <1115146397.4277c89dc37b4@webmail.telus.net> <1115246490.1709.233393619@webmail.messagingengine.com> In-Reply-To: <1115246490.1709.233393619@webmail.messagingengine.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig5AFDC63DD17149B68ADC8371" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/42 X-Sequence-Number: 12206 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig5AFDC63DD17149B68ADC8371 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit David Roussel wrote: >>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? I think COPY also has the advantage that for index updates it only grabs the lock once, rather than grabbing and releasing for each row. But I believe you are right that fsync only happens on COMMIT. > > David John =:-> --------------enig5AFDC63DD17149B68ADC8371 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFCeVmLJdeBCYSNAAMRAuWrAKCbyhxIXWmpXWuKbPGRhjyloIU7swCgxjPE Gn0DQu9Wj5m+Mr3Wv5Zzokk= =pkfD -----END PGP SIGNATURE----- --------------enig5AFDC63DD17149B68ADC8371--