From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> |
Cc: | Andrew Dunstan <andrew(at)dunslane(dot)net>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Parsing speed (was Re: pgstats_initstats() cost) |
Date: | 2003-08-12 19:36:07 |
Message-ID: | 15729.1060716967@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-08-12 20:01:33 | Re: reuse sysids security hole? |
Previous Message | Bruce Momjian | 2003-08-12 19:20:40 | Re: reuse sysids security hole? |