PostgreSQL vs MySQL : strange results on insertion

Hello,

I'm making some tests to migrate a MySQL DB to PostgreSQL DB. I realized a small program in C which does the same thing for MySQL (C API) and PostgreSQL (libpq) : 10000 insertion in a quite simple base.
My DB :
|-----------------|----------------------------------------------|
| test |
| id | auto_increment (or serial for postgreSQL) |
| type_int | INT (or integer) |
| type_varchar | varchar(255) |
| type_int2 | INT (or integer) |
| type_text | text |
|-----------------|----------------------------------------------|

/* -------------------- MySQL code : -------------------- */
#define INSERTION "INSERT INTO test (type_int, type_varchar,type_tinyint,type_text) VALUES (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'"

int main(int argc, char **argv) {
MYSQL mysql;
unsigned int i;
char mquery(1000);
MYSQL_RES *mysql_row;

mysql_init(&mysql);
if (mysql_real_connect(&mysql, NULL, "user", NULL, "bd", 0, NULL, 0)) {
for (i=0;i<=10000;i++) {
sprintf(mquery,INSERTION,i);
if ((mysql_query(&mysql,mquery)!=0) {
printf("sql query error (%s) : %s\n",mquery,mysql_error(&mysql));
mysql_close(&mysql);
return 0;
}
}
mysql_close(&mysql);
}
else {
printf("sql connection error : %s\n",mysql_error(&mysql));
return 0;
}
return 0;
}

/* -------------------- PostgreSQL code : -------------------- */
#define INSERTION "INSERT INTO test (type_int, type_varchar,type_tinyint,type_text) VALUES (%d,\'essai de chaine de caractère\',100,\'MAJUSCULES et minuscules\'"

int main(int argc, char **argv) {
PGconn *conn;
unsigned int i;
char mquery(1000);
PGresult *res;

conn=PQconnectdb("dbname=db user=user");
if (PQstatus(conn) == CONNECTION_OK) {
for (i=0;i<=10000;i++) {
sprintf(mquery,INSERTION,i);
res=PQexec(conn,mquery);
if (PQresultstatus(res)!= PGRES_COMMAND_OK) {
printf("sql query error (%s) : %s\n",mquery,PQresultErrorMessage(res));
PQclear(res);
PQfinish(conn);
return 0;
}
}
PQclear(res);
PQfinish(conn);
}
else {
printf("sql connection error : %s\n",PQerrorMessage(conn));
return 0;
}
return 0;
}

I launch these programs on my computer : Linux Debian with MySQL 3.23.51 and PostgreSQL 7.2.1 (installation by default with 'apt-get install').
Time to realize 10000 insertions with MySQL:
$ time ./test__mysql

real 0m1.500s
user 0m0.150s
sys 0m0.090s
(between 1 and 2 seconds)

Time to realize 10000 insertions with PostgreSQL:
$time ./test_postgresql

real 0m28.568s
user 0m0.390s
sys 0m0.270s
(between 28 and 30 seconds !!!.... )

Very strange, isn't it ? Is there something in PostgreSQL's C API that I didn't understand ? Subtleties during the configuration ? I do not want to believe that PostgreSQL is 15 times slower than MySQL !
Thank you for any comment, remark and correction!

Florent Paul

-------------------------------------------------------------
NetCourrier, votre bureau virtuel sur Internet : Mail, Agenda, Clubs, Toolbar...
Web/Wap : www.netcourrier.com
Téléphone/Fax : 08 92 69 00 21 (0,34 € TTC/min)
Minitel: 3615 NETCOURRIER (0,15 € TTC/min)