Performance problems???

I have observed some disturbing behavior with the latest (7.1.3) version of
PotgreSQL.

In an application that I am working on with a team of folks, there is a
specific need to execute a series of SQL statements similar to those used in
the 'loaddb.pl' script included below. Without getting into better ways to
increment rowid's (this code is part of another tool that we are using), I'd
like to know why I get the following results with PostgreSQL and MySQL.

In 3 separate runs I get the following PostgreSQL results:

o 1 - 2000 records inserted in 12 seconds.
o 2001 - 4000 records inserted in 16 seconds.
o 4001 - 6000 records inserted in 20 seconds.

You see, there is a clear performance degradation here that is associated
with the number of records in the database. It appears that the main culprit
is the update statement that is issued (see 'loaddb.pl' script below). This
performance behavior is not expected. Especially with so few rows in such a
small table.

In 3 separate runs I get the following MySQL results:

o 1 - 2000 records inserted in 6 seconds.
o 2001 - 4000 records inserted in 5 seconds.
o 4001 - 6000 records inserted in 6 seconds.

You see, MySQL performs as expected. There is no performance degradation
here that is related to the number of records in the database tables.

I have been a huge fan and advocate of PostgreSQL. I was stunned to see this
behavior. I am hoping that it is either a bug that has been fixed, or that I
can alter my PostgreSQL configuration to eliminate this behavior.

I have an urgent need to resolve this situation. If I cannot solve the
problem soon, I will be forced to drop PostgreSQL in favor of MySQL. This is
not something that I wish to do.

Please help.

Thanks in advance.

- Jim

########################################################################
#!/usr/bin/perl -w
#
## setupdb.pl
#
## Simple perl script that creates the 'problemtest' db.
#
## Usage: ./setupdb.pl <db-type>
#
## Assumes that the 'problemtest' PostgreSQL and MySQL databases exist.
## and that there is a user 'problemtest' with proper privileges.
#
########################################################################

use strict;
use DBI;

my $dbd;

if (@ARGV) {
if (uc($ARGV[0]) eq 'POSTGRESQL') {
$dbd = 'Pg';
} elsif (uc($ARGV[0]) eq 'MYSQL') {
$dbd = 'mysql';
} else {
&DoUsage();
}
} else {
&DoUsage();
}

my $dsn = "DBI:$dbd:dbname=problemtest";
my $usr = 'problemtest';
my $pwd = 'problemtest';

my $dbh = DBI->connect($dsn,$usr,$pwd,
{ AutoCommit => 1, RaiseError => 1 });

$dbh->do(<<END);
drop table foo
END

$dbh->do(<<END);
drop table control
END

$dbh->do(<<END);
create table foo (
id integer not null,
primary key (id),
name varchar(100))
END

$dbh->do(<<END);
create table control (
next_id integer not null)
END

$dbh->do(<<END);
insert into control (next_id) values(1)
END

$dbh->disconnect();

sub DoUsage {
print "\n\tUsage: ./setupdb.pl <db-type>\n";
print "\tWhere db-type is 'PostgreSQL' or 'MySQL'\n\n";
exit 0;
}

########################################################################
#!/usr/bin/perl -w
#
## loaddb.pl
#
## Simple perl script to illustrate the performance degradation
## of the update statement with PostgreSQL as compared to MySQL.
#
## Usage: ./loaddb.pl <db-type> <range-start> <range-end>
#
########################################################################

use strict;
use DBI;

my $dbd;

if (@ARGV == 3) {
if (uc($ARGV[0]) eq 'POSTGRESQL') {
$dbd = 'Pg';
} elsif (uc($ARGV[0]) eq 'MYSQL') {
$dbd = 'mysql';
} else {
&DoUsage();
}
} else {
&DoUsage();
}

my $dsn = "DBI:$dbd:dbname=problemtest";
my $usr = 'problemtest';
my $pwd = 'problemtest';

my $dbh = DBI->connect($dsn,$usr,$pwd,
{ AutoCommit => 1, RaiseError => 1 });

my $inc_id = $dbh->prepare("update control set next_id = next_id + 1");
my $get_id = $dbh->prepare("select next_id from control");
my $insert = $dbh->prepare("insert into foo (id,name) values(?,?)");

my $start = time;
foreach($ARGV[1]..$ARGV[2]){
$inc_id->execute();
$get_id->execute();
my $id = $get_id->fetchrow_array();
$insert->execute($id,"name$id");
}
my $duration = time - $start;
print "duration = $duration\n";

$inc_id->finish();
$get_id->finish();
$insert->finish();

$dbh->disconnect();

sub DoUsage {
print "\n\tUsage: ./loaddb.pl <db-type> <range-start> <range-end>.\n";
print "\tWhere db-type is 'PostgreSQL' or 'MySQL'.\n\n";
exit 0;
}