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
#include "pgstream.h"
#include <iostream>
#include <stdlib.h>
#include <stdio.h>
#include <sys/time.h>
int main(int argc, char** argv)
{
const int loops=10000;
pg_cnx cnx;
char buf[]="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
try {
struct timeval tv1, tv2;
if (argc < 2) {
std::cerr << "Usage: " << argv[0] << " connection_string\n";
exit(1);
}
cnx.connect(argv[1]);
for (int j=0; j<2; j++) {
cnx.set_option("bind_variables", j>0);
cnx.set_option("prepare_statements", j>0);
{
pg_trans trans(cnx);
pg_stmt("CREATE TABLE txt1(pk1 int,t1 text,t2 text,"
"t3 text, t4 text)", cnx);
gettimeofday(&tv1,NULL);
pg_stream s1("INSERT INTO txt1(pk1,t1,t2,t3,t4) "
"VALUES(:i,:p1,:p2,:p3,:p4)", cnx);
for (int i=0; i<loops; i++) {
s1 << i << buf << buf << buf << buf;
}
gettimeofday(&tv2,NULL);
pg_stmt("DROP TABLE txt1", cnx);
trans.commit();
printf("elapsed time in loop %d is %d ms\n", j,
(tv2.tv_sec-tv1.tv_sec)*1000+(tv2.tv_usec-tv1.tv_usec)/1000);
}
}
}
catch(pg_excpt p) {
std::cerr << p.full_error_txt();
}
return 0;
}
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
