Re: Prepared statements performance

Hello

2012/5/10 Daniel McGreal <daniel.mcgreal@redbite.com>:

> Hi again,
>
> I did a follow up test using 'multi-value' inserts which is three times
> faster than multiple inserts thusly:
>

if you need speed, use a COPY statement - it should be 10x faster than INSERTS

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@redbite.com> 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.
>>
>>
>