Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C91ECB5DC22
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 27 Jun 2011 05:02:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 99430-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon, 27 Jun 2011 08:02:43 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[173.230.139.50])
	by mail.postgresql.org (Postfix) with ESMTP id 9B1F5B5D803
	for <pgsql-sql@postgresql.org>; Mon, 27 Jun 2011 05:02:43 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 7FC3E2D;
	Mon, 27 Jun 2011 04:02:42 -0400 (EDT)
X-Virus-Scanned: amavisd-new at postnewspapers.com.au.com
Received: from www.postnewspapers.com.au ([127.0.0.1])
	by localhost (www.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id PnbycjbNWlL3; Mon, 27 Jun 2011 04:02:42 -0400 (EDT)
Received: from mail.postnewspapers.com.au (ppp171-128.static.internode.on.net
	[150.101.171.128])
	by www.postnewspapers.com.au (Postfix) with ESMTP id A6A1127;
	Mon, 27 Jun 2011 04:02:41 -0400 (EDT)
Received: from [10.0.0.53] (wallace.localnet [10.0.0.53])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 408B8AE22A;
	Mon, 27 Jun 2011 16:02:40 +0800 (WST)
Message-ID: <4E083920.1070302@postnewspapers.com.au>
Date: Mon, 27 Jun 2011 16:02:40 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.15) Gecko/20110307 Thunderbird/3.1.9
MIME-Version: 1.0
To: Pavel Stehule <pavel.stehule@gmail.com>
CC: chester c young <chestercyoung@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: best performance for simple dml
References: <BANLkTimcokKM=MTAu1-kZFPsNGW8Lgj3rw@mail.gmail.com>
	<576786.8402.qm@web161422.mail.bf1.yahoo.com>
	<BANLkTik90qDGv-9CcHtV6i=abynywUkbNA@mail.gmail.com>
In-Reply-To: <BANLkTik90qDGv-9CcHtV6i=abynywUkbNA@mail.gmail.com>
X-Enigmail-Version: 1.1.2
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201106/97
X-Sequence-Number: 35563

On 27/06/11 15:05, Pavel Stehule wrote:

> exec('begin');
> for(i = 0; i < 1000; i++)
>   exec("insert into foo values($1), itoa(i));
> exec('commit');

You can probably also benefit from multi-valued INSERTs, though I
haven't verified this.

INSERT INTO foo VALUES
(1,'joe','dean'),
(4,'fred','bob'),
(11,'anne','smith');

There'll be a threshhold above which the COPY protocol becomes faster,
though.

--
Craig Ringer