Parsing speed (was Re: pgstats_initstats() cost)

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> I wasn't interested in measuring the performance of yacc -- since I know
> it is bad. It was a basic test which wasn't even meant to be real
> world. It just seemed interesting that the numbers were three times slower
> than other databases I ran it on. Here is the script which generates the
> SQL:

> echo "create table abc(t text);"
> echo "begin;"
> c=0
> while [ $c -lt 100000 ]
> do
> echo "insert into abc values('thread1');";
> c=$[$c+1]
> done
> echo "commit;"

Of course the obvious way of getting rid of the parser overhead is not
to parse everytime --- viz, to use prepared statements.

I have just finished running some experiments that compared a series of
INSERTs issued via PQexec() versus preparing an INSERT command and then
issuing new-FE-protocol Bind and Execute commands against the prepared
statement. With a test case like the above (one target column and a
prepared statement like "insert into abc values($1)"), I saw about a 30%
speedup. (Or at least I did after fixing a couple of bottlenecks in the
backend's per-client-message loop.)

Of course, the amount of work needed to parse this INSERT command is
pretty trivial. With just a slightly more complex test case:
create table abc (f1 text, f2 int, f3 float8);
and a prepared statement like
PREPARE mystmt(text,int,float8) AS insert into abc values($1,$2,$3)
there was a factor of two difference in the speed.

This leaves us with a bit of a problem, though, because there isn't any
libpq API that allows access to this speedup. I put in a routine to
support Parse/Bind/Execute so that people could use out-of-line
parameters for safety reasons --- but there's no function to do
Bind/Execute against a pre-existing prepared statement. (I had to make
a hacked version of libpq to do the above testing.)

I'm beginning to think that was a serious omission. I'm tempted to fix
it, even though we're past feature freeze for 7.4. Comments?

regards, tom lane