Message-ID: <4CDA618F.7050102@catalyst.net.nz>
Date: Wed, 10 Nov 2010 22:10: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>
In-Reply-To: <4CD06A81.1020800@vmsinfo.com>
Content-Type: multipart/mixed; boundary="------------050809040806050206050906"

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

On 03/11/10 08:46, Mladen Gogala wrote:
> I wrote a little Perl script, intended to test the difference that 
> array insert makes with PostgreSQL. Imagine my surprise when a single  
> record insert into a local database was faster than batches of 100 
> records. Here are the two respective routines:

Interesting - I'm seeing a modest but repeatable improvement with bigger 
array sizes (using attached program to insert pgbench_accounts) on an 
older dual core AMD box with a single SATA drive running Ubuntu 10.04 i686.

    rows arraysize elapsed(s)
1000000         1        161
1000000        10        115
1000000       100        110
1000000      1000        109

This is *despite* the fact that tracing the executed sql (setting 
log_min_duration_statement = 0) shows that there is *no* difference (i.e 
1000000 INSERT executions are performed) for each run. I'm guessing that 
some perl driver overhead is being saved here.

I'd be interested to see if you can reproduce the same or similar effect.

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.

Cheers

Mark

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

#!/usr/bin/perl
#
# execinsert.pl : test case for array 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 $sql = "INSERT INTO pgbench_accounts(aid,bid, abalance) VALUES(?, ? ,?)";
my $dsn;

my $dbh;
my $sth;
my $row;
my $elapsed;
my $i;
my $maxi = shift;
my @aaid;
my @abid;
my @aabal;
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();

$sth = $dbh->prepare($sql);

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

	if ($i%$arraysiz eq 0) {

		if ($i > 0 ) {
            my $tuples = $sth->execute_array(undef,
                                            \@aaid, \@abid, \@aabal);
		}

		@aaid = ();
		@abid = ();
		@aabal = ();
	}

	push @aaid, $aid;
	push @abid, $bid;
	push @aabal, 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();

--------------050809040806050206050906--