Prepared statements performance

From: Daniel McGreal <daniel(dot)mcgreal(at)redbite(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Prepared statements performance
Date: 2012-05-10 08:25:49
Message-ID: CACAnjQzPTKv2PtO6SriN-tGjE8bjWgOtg1WguK_=TZg8CshHOg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniel McGreal 2012-05-10 09:01:03 Re: Prepared statements performance
Previous Message Albe Laurenz 2012-05-10 07:57:27 Re: config file question between versions 7.4 - 9.1