Re: slow transfer speeds with PostgreSQL

On 8/3/06, hansell baran <hansellb(at)yahoo(dot)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