DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=MSYDO8r9zbHM+4FrXoECX+wkAheUAeHlhj2aKE8hVeSCU6XuipG9HP9cDX84IjploG4SC17tzDo5y7rS70/JRL56xQ9NfTjF17eG/vsz1u7tklXlC8O9uAvTXfPbFR89nzBnKPzr86F0bW9T82TBpNDX5bFkyv0pETbOi9WiNgk=
Message-ID: <b42b73150608101351m168dd1e9mbe74b820d4e720b5@mail.gmail.com>
Date: Thu, 10 Aug 2006 16:51:16 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "hansell baran" <hansellb@yahoo.com>
Subject: Re: slow transfer speeds with PostgreSQL
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20060803233939.75401.qmail@web30304.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20060803233939.75401.qmail@web30304.mail.mud.yahoo.com>

On 8/3/06, hansell baran <hansellb@yahoo.com> wrote:
> Hi. I'm new at using PostgreSQL.
> Where I work, all databases were built with MS Access. The Access files are
> hosted by computers with Windows 2000 and Windows
>
> XP. A new server is on its way and only Open Source Software is going to be
> installed. The OS is going to be SUSE Linux 10.1
>
> and we are making comparisons between MySQL, PostgreSQL and MS Access. We
> installed MySQL and PostgreSQL on both SUSE and
>
> Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is one HDD
> for Windows and one for Linux)
> The "Test Server" in which we install the DBMS has the following
> characteristics:
>
> CPU speed = 1.3 GHz
> RAM = 512 MB
> HDD = 40 GB
>
> The biggest table has 544371 rows(tuples?) with 55 rows. All fields are
> float8. Only 1 is varchar(255) and 1 timestamp.
> We query the MS Access databases through Visual Basic Programs and ODBC
> Drivers. We made a Visual Basic program that uses ADO
>
> to connect to ALL three DBMS using ODBC drivers.
>
> When we run the following query "SELECT * FROM big_table", we get the
> following resutls:
>
> MS Access
> - Execution time ~ 51 seconds (Depending on the client machine, it can go as
> low as 20 seconds)
> - Network Utilization ~ 80 Mbps (According to Windows Task Manager)
>
> MySQL 5.0 (under Windows)
> - Execution time ~ 630 seconds
> - Network Utilization ~ 8 Mbps
>
> PostgreSQL 8.1 (under Windows)
> - Execution time ~ 290 seconds)
> - Network Utilization ~ 13 Mbps
>
>
> MS Access (under Linux. MS Access files are in the Linux computer which has
> the SAMBA server running. The client computer has
>
> a mapped network drive that conects to the Linux files.)
> - Execution time ~ 55 seconds (Depending on the client machine, it can go as
> low as 20 seconds)
> - Network Utilization ~ 76 Mbps (According to Windows Task Manager)
>
> MySQL 5.0(under Linux)
> - Execution time ~ 440 seconds
> - Network Utilization ~ 11 Mbps
>
> PostgreSQL 8.1(under Linux)
> - Execution time ~ 180 seconds)
> - Network Utilization ~ 18 Mbps
>
>
> Very different results are obtained if a the query "SELECT * from big_table
> ORDER BY "some_column"". In this scenario

you have to be careful comparing access to mysql/postgresql in this
way because the architecture is different...these results are a bit
misleading.  access can do some optimization tricks on very simple
queries, especially select * from bigtable becuase the result does not
have to be fully materialized and returned to the client.

> PostgreSQL is faster than MS Access or MySQL by more than 100 seconds.
>
> We have run many other queries (not complex, at most nesting of 5 inner
> joins) and MS Access is always faster. We have seen

i find this really hard to believe. is your postgresql database
properly indexed and did you run analyze?  do the standard
-performance thing, run the query in with explain analyze:

explain anaylze 5_table_join_query

and post the results to this list.

merlin