Re: psql vs perl prepared inserts

Neil Conway wrote:

> For an INSERT query without any sub-selects
> that is not rewritten by any rules, the cost to parse, rewrite and plan
> the statement is trivial. So I wouldn't expect prepared statements to be
> a big win -- you would gain a lot more from batching multiple inserts
> into a single transaction, and more still from using COPY.

FWIW, when testing pgstream [1] I typically see a 50% increase in execution
speed when switching to prepared statements in such a scenario.
I'm attaching a small test program that inserts 10000 rows into 5 columns, first
without and then with prepared statements, and displays elapsed time.

Example of results:
elapsed time in loop 0 is 1873 ms (PQexec)
elapsed time in loop 1 is 1136 ms (PQexecPrepared)
That's with unix domain sockets and a 8.0.1 server.

[1] a thin C++ layer on top of libpq (http://manitou-mail.org/pgstream) that
happens to have a unified API for prepared/non-prepared statements.

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org