1. create table test (id int4, aaa int4, primary key (id));
2. insert into test values (0,1);
3. Execute "update test set aaa=1 where id=0;" in an endless loop
I just did the test on PostgreSQL 7.4.12 and MySQL 5.0.22 (MyISAM,
sorry had no configured InnoDB). Ubuntu 6.0.6, AMD64, 2GB, default
database settings.
MySQL performs very well, approximately 15000-20000 updates per second
with no degradation of performance.
PostgreSQL does approximately 1600 records per second for the first
10000, then 200rps for the first 100k records, and then slower and
slower downgrading to 10-20 rps(!!!) when reaching 300k.
Something is wrong with your test code. If I had to guess I would say
you did all the updates in a single transaction without committing
them, in which case yes it will slow down until you commit.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
