From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Array interface
Date: Wed, 10 Nov 2010 22:10:39 +1300
Message-ID: <4CDA618F.7050102@catalyst.net.nz> (raw)
In-Reply-To: <4CD06A81.1020800@vmsinfo.com>
References: <4CD06A81.1020800@vmsinfo.com>
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
Attachments:
execinsert.pl (application/x-perl, 1.3K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz
Subject: Re: Array interface
In-Reply-To: <4CDA618F.7050102@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox