Thread

1. Sending result sets from backend to frontend is _slow_

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-06T22:21:06Z

   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 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.  Obviously, if this is the case, it almost guarantees sub-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 could look into it :-)
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: Sending result sets from backend to frontend is _slow_

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-07T01:59:14Z

   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