Thread: Sending result sets from backend to frontend is _slow_

Glen Parker (glenebob@nwlink.com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Sending result sets from backend to frontend is _slow_

Long Description
When operating over a fast network (ethernet), the sending of select result rows from the backend is very slow, ie. it
usesonly a small fraction of available network bandwidth.  I am running postgres 7.0.2 on a Redhat 6.1 install on x86,
andusing the postodbc driver on win2k, and I have looked at the odbc driver code until I am blue in the face :-) and I
amconfident that it is doing the right thing with network IO (large read buffers, 4096 bytes by default).  There is
alsovery low CPU utilization on both machines during large result transfers.  From this, I believe the problem is in
thebackend, and I think it is probably sending one row per network write.  Obviously, if this is the case, it almost
guaranteessub-optimal network performance on fast networks, except on very wide result sets. 
The backend code is quite difficult to dig into for a beginner, but if someone could explain briefly how results are
sent,and some pointers on where I might start, I would be able to at least attempt a fix for it.  Or one of the gurus
couldlook into it :-) 

Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> When operating over a fast network (ethernet), the sending of select
> result rows from the backend is very slow, ie. it uses only a small
> fraction of available network bandwidth.  I am running postgres 7.0.2
> on a Redhat 6.1 install on x86, and using the postodbc driver on
> win2k, and I have looked at the odbc driver code until I am blue in
> the face :-) and I am confident that it is doing the right thing with
> network IO (large read buffers, 4096 bytes by default).  There is also
> very low CPU utilization on both machines during large result
> transfers.  From this, I believe the problem is in the backend, and I
> think it is probably sending one row per network write.

Certainly not!  See the usage of pq_putbytes and pq_flush.  The only
not-absolutely-necessary flush in the backend is just after sending
an error or notice message (which one would hope is a noncritical
path).  I dunno what is causing your problem, but we're not quite that
dumb ;-)

Depending on what your test query is, it's possible that the server
machine is disk I/O bound --- have you checked?

            regards, tom lane