Re: PostgreSQL 8 on windows very slow

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