
I've noticed subsequent executions of the same insert command are slow.
I've searched the list archives for this matter and found several
entries
related, including suggestions how to speed up.
The standard answer from the core team is, use COPY.
Sorry, but this is from an application point of view not really an
option
if you're dealing with program variables.
We used to have an INGRES installation around, and since I know Postgres

is based on it, may be this old INGRES feature is worth to consider:

Taken from the Reference Manual
[REPEATED] INSERT INTO ...

The key word REPEATED directs INGRES to encode the INSERT and save its
execution plan when it is first executed. This encoding can account for
significant performance improvements on subsequent executions of the
same INSERT.

What do you others think of it?

Regards, Christoph



Mon, 24 Feb 2003 13:22:56 +0100
Christoph Haller <ch@rodos.fzk.de>
REPEATED INSERT INTO ... 
Christoph Haller <ch@rodos.fzk.de> writes:
> Taken from the Reference Manual
> [REPEATED] INSERT INTO ...

> The key word REPEATED directs INGRES to encode the INSERT and save its
> execution plan when it is first executed. This encoding can account for
> significant performance improvements on subsequent executions of the
> same INSERT.

> What do you others think of it?

You can do that today with PREPARE/EXECUTE; there's no need to invent
specialized syntax for it.

			regards, tom lane

Mon, 24 Feb 2003 10:02:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: REPEATED INSERT INTO ... 
On Mon, 2003-02-24 at 07:22, Christoph Haller wrote:
> I've noticed subsequent executions of the same insert command are slow.
> I've searched the list archives for this matter and found several
> entries
> related, including suggestions how to speed up.
> The standard answer from the core team is, use COPY.
> Sorry, but this is from an application point of view not really an
> option
> if you're dealing with program variables.

What do you mean by "program variables"? If you're just referring to
variables in the programming language the DB client is written in, I see
no reason why you couldn't use COPY to input those.

> The key word REPEATED directs INGRES to encode the INSERT and save its
> execution plan when it is first executed. This encoding can account for
> significant performance improvements on subsequent executions of the
> same INSERT.

As Tom mentioned, we already support PREPARE/EXECUTE for this
functionality -- but I'd suspect that for most INSERT queries, the
parsing/rewriting/planning stages are relatively inexpensive, and the
bulk of the runtime is spent in the executor (and writing WAL records,
etc). So my guess would be that you won't see that much of a performance
improvement from this...

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




24 Feb 2003 11:37:49 -0500
Neil Conway <neilc@samurai.com>
Re: REPEATED INSERT INTO ...