Message-ID: <4CDA690F.1030408@catalyst.net.nz>
Date: Wed, 10 Nov 2010 22:42:39 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Array interface
References: <4CD06A81.1020800@vmsinfo.com> <4CDA618F.7050102@catalyst.net.nz>
In-Reply-To: <4CDA618F.7050102@catalyst.net.nz>
Content-Type: multipart/mixed; boundary="------------090402010904080701010204"

This is a multi-part message in MIME format.
--------------090402010904080701010204
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/11/10 22:10, Mark Kirkwood wrote:
>
> What might also be interesting is doing each INSERT with an array-load 
> of bind variables appended to the VALUES clause - as this will only do 
> 1 insert call per "array" of values.

This is probably more like what you were expecting:

rows    num values tuples(i.e array size) elapsed
1000000                 1                     106
1000000                10                      14
1000000               100                      13
1000000              1000                      14

I didn't try to use PREPARE + EXECUTE here, just did "do" with the 
INSERT + array size number of VALUES tuples (execute could well be 
faster). The obvious difference here is we only do rows/(array size) 
number of insert calls.

Cheers

Mark


--------------090402010904080701010204
Content-Type: application/x-perl;
 name="execinsert-values.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="execinsert-values.pl"

#!/usr/bin/perl
#
# execinsert-values.pl : test case for multi values insert
#

use strict;
use warnings;

use DBI;
use Time::HiRes qw(time);

my $driver = "Pg";
my $user = "postgres";
my $dbname = "bench";
my $host = "localhost";
my $port = 5439;
my $dsn;

my $dbh;
my $sth;
my $row;
my $elapsed;
my $i;
my $maxi = shift;
my $values;
my $aid = 1;
my $maxaid = 10000000;
my $bid = 1;
my $maxbid = 100;
my $arraysiz = shift;

if (!defined $maxi)  {
	$maxi = 1000000
};
if (!defined $arraysiz)  {
	$arraysiz = 1
};

$dsn = "dbi:$driver:host=$host;port=$port;dbname=$dbname";
$dbh = DBI->connect($dsn, $user, "", {AutoCommit => 0, RaiseError => 1} ) 
		or die "Cannot connect to $dbname!\n";

$dbh->do("TRUNCATE pgbench_accounts");

$elapsed = time();

for ($i = 0; $i < $maxi + 1; $i++) {

	if ($i%$arraysiz eq 0) {

		if ($i > 0 ) {
			$dbh->do("INSERT INTO pgbench_accounts (aid, bid, abalance) VALUES $values");
		}
		$values = "($aid, $bid, 0)";

	} else {

		$values = $values . ",($aid, $bid, 0)";
	}

	$aid++;
	if ($aid eq $maxaid) {
		$aid = 1;
		$bid++;
	}

}

$dbh->commit();

$elapsed = time() - $elapsed;

print "$i inserts with array size $arraysiz in $elapsed s\n";

$dbh->disconnect();

--------------090402010904080701010204--