Re: slow request

On Oct 9, 2007, at 6:34 AM, michael(dot)guiard(at)gmail(dot)com wrote:

> Hi !
> I am using postgreSQL v8.1.5 with latest odbc driver v8.02.0500.
> I have very slow request between my server and my client. They are
> both on the same switch 100Mb/s. I have no particular network
> problems.
> I use the pgadmin tool to do my request.
>
> My database is compose of one table. This table has some simple fields
> (varchar & int, less than 500 bytes) and its primary key is a
> varchar(32) (CLSID). This table has 140000 records.
> I know the primary key as a clsid is not the best choice, but it is
> mine :)
>
> The request "select * from mytable" on the server takes
> approximatively 30 seconds.
> The same request on the client takes approximatively 400 seconds !
> What I do not understand is that my network traffic during this
> request on the client side is very low. It is less than 100KB/s !
>
> Why is it so slow ? I suppose that my 140000 records are retrieve one
> by one... is it true ? if yes, why ?
>
> I try to do the same thing with another table with a primary key as
> 'int4'. The result is the same : 540 secs to retrieve 150000 records
> at 45 KB/s (average speed) (132 octets/record * 150000 = 19MB / 45 KB/
> s = 430 seconds)
>
> How can I improve these performances ?

This has been reported before, IIRC, and one issue was that pgadmin
spent a lot longer rendering the data than it did retrieving it.

So before you look any further, run the same query from psql and
see if that changes anything.

Cheers,
Steve