Re: Prepared statements performance

[Daniel McGreal <daniel.mcgreal@xxxxxxxxxxx>]

Hi again,

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

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.