Re: [HACKERS] Postgres Speed or lack thereof

Tom Lane wrote:
> 
> The other thing that jumps out here is the unreasonably high position of
> recv(), which is called 962187 times.  The script being read by psql was
> only 957186 characters.  Evidently we're invoking a kernel recv() call
> once per character read from the frontend.  I suspect this is an
> inefficiency introduced by Magnus Hagander's recent rewrite of backend
> libpq (see, I told you there was a reason for using stdio ;-)).

At least part of the problem is also the fe-be protocol itself, maybe 
not in the backend receiving side, but certainly while front-end is 
receiving.

The main problem is that you must very often wait for a specific end 
character (instead of sending first the length and then reading the 
required amount in one chunk), and it requires some trickery to do it
efficiently without reading each character separately.

I once did the fe-be protocol in python (an interpreted scripting 
language, see http://www.python.org), it was for v6.2. I was quite 
amazed by the baroqueness of the protocol - it uses a mix three 
techniques - 1. wait for EOT char, 2. receive a pascal style string and
3.
get a record consisting of a mix of 1 and 2.

>  We're
> gonna have to do something about that, though it's not as critical as
> the memory-allocation issue.  It also appears that send() is now being
> invoked multiple times per backend reply, which is going to create
> inefficiencies outside the backend (ie, multiple packets per reply).
> On a test case with a lot of SELECTs that would show up more than it
> does here.

I am sure that we need to change the protocol sometime soon, if we 
want to increase the performance. I have contemplated something like 
the X-window protocol, that seems to be quite well designed.

And of course we need some sort of CLI that can do prepared statements 
and that can use binary fields (not only the file interface).

Currently we have some of it, bu only in SPI.

Unfortunately most inserts are not done using SPI :(

It may be the CORBA interface that somebody may be working on, or 
it may be something simpler.

---------------------
Hannu Krosing