Thread: PostgreSQL 8 on windows very slow

Hi,

I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ?
Thanks

What queries are you running?
What sort of a machine are the database systems running on?

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of lol
Sent: Tuesday, January 11, 2005 6:47 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL 8 on windows very slow

I have tried RC4, but there's no differences

My results using my configuration are :
MySQL 4 is 6 times  faster than pgSQL
Firebird 1.5 is 3 times faster than pgSQL

Are these results coherent ? May be the problem comes more from ZeosLib
than pgSQL8


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Well, for SELECT request pgsql  runs well, although it's a little
slower than firebird & MySQL.
The problem is especially for INSERT request. It's strange there's a
lot of disk access, as if it doesn't use the cache or something like
that (I'm not a db expert).
The application runs on windows 2000 SP4 english, developped in Delphi
7 and access the database using ZeosLib 6.1.5 (I have also tried with
6.5.1-alpha 2).
We don't make a lot of request in our application. We use MS-Access 97
and we want to change for an SQL compliant open source database.
Out application with pgsql (but I think the problem is zeoslib)  is a
little slower than ms-access ! We have good results with MySQL and
Firebird (using zeoslib).

Thank you

In our testing Postgres came out on top, well MySql was faster if you
use the non-transactional table type "MyISAM". If you use the InnoDb
table type it comes out slower. All of this is very subjective depending
on your querys and data set. Have you run the ANALYZE command on
Postgres since you have added the data into the tables? It makes a huge
difference in performance for us.

Hope that helps, Jasper

lol wrote:

>Well, for SELECT request pgsql  runs well, although it's a little
>slower than firebird & MySQL.
>The problem is especially for INSERT request. It's strange there's a
>lot of disk access, as if it doesn't use the cache or something like
>that (I'm not a db expert).
>The application runs on windows 2000 SP4 english, developped in Delphi
>7 and access the database using ZeosLib 6.1.5 (I have also tried with
>6.5.1-alpha 2).
>We don't make a lot of request in our application. We use MS-Access 97
>and we want to change for an SQL compliant open source database.
>Out application with pgsql (but I think the problem is zeoslib)  is a
>little slower than ms-access ! We have good results with MySQL and
>Firebird (using zeoslib).
>
>Thank you
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>
>

> Out application with pgsql (but I think the problem is zeoslib)  is a
> little slower than ms-access ! We have good results with MySQL and
> Firebird (using zeoslib).

    access ? really ?
    what is the size of your dataset ?
    i've seen an access application literally die, belly-up with like 15
minutes queries, and the data set size was tiny by postgresql standards !
    If you do 10.000 inserts, each in its transaction, sure postgres is going
to be slow as hell... IMHO you should check if your library isn't turning
on autocommit and/or adding begin's and commit's behind your back.

    You can make the following test :

CREATE TABLE dummy( id serial, data integer );
INSERT INTO dummy (data) VALUES (0);
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;
INSERT INTO dummy (data) SELECT data FROM dummy;

EXPLAIN ANALYZE INSERT INTO dummy (data) SELECT data FROM dummy;
  Seq Scan on dummy  (cost=0.00..1388.05 rows=68870 width=4) (actual
time=0.084..1024.668 rows=65536 loops=1)
  Total runtime: 2428.795 ms

    With a vanilla IDE 7200rpm disk on Linux. If you get 10 seconds, there's
a problem in your conriguration.

    Now, a little demonstration of MySQL, with an half-completed query :

mysql> create table test (id integer);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test (id) values (1),(2),(3),(3),(4),(5);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create table testu (id integer, unique(id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testu select * from test;
ERROR 1062: Duplicate entry '3' for key 1
mysql> select * from testu;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

--- Now, even better

mysql> delete from testu;
Query OK, 3 rows affected (0.00 sec)

mysql> insert into testu (id) values (1),(2),(3),(3),(4),(5);
ERROR 1062: Duplicate entry '3' for key 1
mysql> select * from testu;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

I've runned your test: 3304 ms (7200 rpm hard disk, pentium3 1Ghz).