Unfortunately these are experimental conditions. The conditions surrounding the intended application are such that my two options are prepared statements or many inserts. I put the multi-value inserts in as I was curious as to why prepared statements would be slower given they only plan the query once (as also does the multi-value insert, I assume).
It turns out though that the results are skewed by using pgAdmin. Executing my scripts from the command line gives much more appropriate results.
Thanks,
Dan.
On Thu, May 10, 2012 at 10:16 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
Hello
2012/5/10 Daniel McGreal <daniel.mcgreal@xxxxxxxxxxx>:
> Hi again,if you need speed, use a COPY statement - it should be 10x faster than INSERTS
>
> I did a follow up test using 'multi-value' inserts which is three times
> faster than multiple inserts thusly:
>
Pavel
>
> TRUNCATE test;
> BEGIN;
> INSERT INTO test (one, two, three, four, five) VALUES ('2011-01-01', true,
> 'three', 4, 5.5)
>
> ,('2011-01-01', true, 'three', 4, 5.5)
> -- 99'998 more , ('2011-01-01', true, 'three', 4, 5.5) ...;
> END;
>
> This is the kind of speed increase I was hoping for when using prepared
> statements (which makes sense because in this multi-value insert the query
> is only being planned once?).
>
> Thanks,
> Dan.
> P.S. Mac OS X 10.7.3 using PostgreSQL 9.1.2.
>
>
>> On Thu, May 10, 2012 at 9:25 AM, Daniel McGreal
>> <daniel.mcgreal@xxxxxxxxxxx> wrote:
>>>
>>> Hi!
>>>
>>> My reading to date suggests that prepared statements should be faster to
>>> execute than issuing the same statement multiple times. However, issuing
>>> 100'000 INSERTs turned out to be more than ten times faster than executing
>>> the same prepared statement 100'000 times when executed via pgAdmin. The
>>> table was:
>>>
>>> CREATE TABLE test
>>> (
>>> one date,
>>> two boolean,
>>> three character varying,
>>> four integer,
>>> five numeric(18,5),
>>> id serial NOT NULL --note the index here
>>> )
>>>
>>> The prepared statement test lasting ~160 seconds was:
>>>
>>> TRUNCATE test;
>>>
>>> BEGIN;
>>> PREPARE foo(date, boolean, varchar, integer, numeric(18,5)) AS
>>> INSERT INTO test (one, two, three, four, five) VALUES ($1, $2, $3,
>>> $4, $5);
>>>
>>> EXECUTE foo('2011-01-01', true, 'three', 4, 5.5);
>>> -- 99'999 more executes...
>>> END;
>>>
>>> The insertion test lasting ~12 seconds was:
>>>
>>> TRUNCATE test;
>>>
>>> BEGIN;
>>> INSERT INTO test (one, two, three, four, five) VALUES ('2011-01-01',
>>> true, 'three', 4, 5.5);
>>> -- 99'999 more inserts...
>>> END;
>>>
>>> I'm assuming then that I've done something mistakenly.
>>>
>>> Many thanks,
>>> Dan.
>>
>>
>
