From: Rainer Bauer <usenet@munnin.com>
Subject: Data transfer very slow when connected via DSL
Date: Thu, 21 Jun 2007 16:22:57 +0200
Organization: Hub.Org Networking Services
Lines: 48
Message-ID: <s7tk73d57kvb3f4ph1kmkvhdfe0kak5a1r@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: pgsql-performance@postgresql.org

Hello all,

one of my customers installed Postgres on a public server  to access the data
from several places. The problem is that it takes _ages_ to transfer data from
the database to the client app. At first I suspected a problem with the ODBC
driver and my application, but using pgAdminIII 1.6.3.6112 (on Windows XP)
gives the same result.

In table "tblItem" there are exactly 50 records stored. The table has 58
columns: 5 character varying and the rest integer.

As far as I can tell the Postgres installation is o.k.

SELECT VERSION()
"PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6
[FreeBSD] 20060305"

EXPLAIN ANALYZE SELECT * FROM "tblItem"
"Seq Scan on "tblItem"  (cost=0.00..2.50 rows=50 width=423) (actual
time=0.011..0.048 rows=50 loops=1)"
"Total runtime: 0.150 ms"

The database computer is connected via a 2MBit SDL connection. I myself have a
768/128 KBit ADSL connection and pinging the server takes 150ms on average.

In the pgAdminIII Query Tool the following command takes 15-16 seconds:
SELECT * FROM "tblItem"

During the first 2 seconds the D/L speed is 10-15KB/s. The remaining time the
U/L and D/L speed is constant at 1KB/s.

My customer reported that the same query takes 2-3 seconds for him (with 6MBit
ADSL and 50ms ping).

So my questions are:
* Could there be anything wrong with the server configuration?
* Is the ping difference between the customers and my machine responsible for
the difference in the query execution time?
* Is this normal behaviour or could this be improved somehow?

Thanks in advance for any help.

Rainer

PS: I tried selecting only selected columns from the table and the speed is
proportional to the no. of rows which must be returned. For example selecting
all 5 character columns takes 2 seconds. Selecting 26 integer columns takes
7-8 seconds and selecting all integer columns takes 14 seconds.