Re: PostgreSQL 8 on windows very slow - Mailing list pgsql-general

> 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)