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

#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;
}