Re: Prepared statements performance - Mailing list pgsql-general

From Daniel McGreal
Subject Re: Prepared statements performance
Date
Msg-id CACAnjQzwyD2OFM7GNqCLtx0idak4RnBg4wTrAV5Fh9gTU35rDQ@mail.gmail.com
Whole thread Raw
In response to Re: Prepared statements performance  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Prepared statements performance
List pgsql-general
Doing the same tests from psql gives:
  1. ~2.5 seconds for INSERT/VALUES
  2. ~10 seconds for prepared statement executes
  3. ~15 seconds for multiple INSERTs

Dan.

On Thu, May 10, 2012 at 3:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alban Hertroys <haramrae@gmail.com> writes:
> On 10 May 2012 15:05, Radosław Smogura <rsmogura@softperience.eu> wrote:
>> May I ask what kind of planning may occur during insert?

> Well, for example, if there's a unique constraint on the table then
> the database will have to check that the newly inserted values don't
> conflict with values that are already in the table. It needs to plan
> an efficient strategy for that, which depends on the values being
> inserted.

There is no planning associated with checking unique constraints; that's
just a matter for the index mechanisms.

I think the real point here is that a simple INSERT/VALUES has such a
trivial plan that there is hardly any gain to be had by avoiding the
planning stage.  Then the other overhead of a prepared statement
(looking up the saved plan, checking it's not stale, etc) outweighs
that.  Or at least it could.  3x slower seems a bit fishy; I wonder
whether there's some client-side inefficiency involved in that.
Doing performance measurements with pgAdmin seems pretty questionable
in the first place ...

                       regards, tom lane

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Prepared statements performance
Next
From: "Albe Laurenz"
Date:
Subject: Re: vacuum, vacuum full and problems releasing disk space