Re: PostgreSQL 8 on windows very slow

PFC <lists@xxxxxxxxxxxxxxxxxxxxx> · Mon, 17 Jan 2005 22:01:43 +0100

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)

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