On Mon, 21 Jul 2003, [ISO-8859-1] Hans-J�rgen Sch�nig wrote:
> > Why was SERIALIZABLE faster? I know SERIALIZABLE doesn't have the
> > rollback penalty in read-only queries, but I don't understand why it
> > would be faster.
> >
>
>
> To be honest I don't have the slightest idea. Maybe it has to do with
> snapshotting but I don't know precisely. In case of SERIALIZABLE all
> snapshots inside a transaction are the same - maybe this makes the big
> difference. I have no other explanation for that.
>
> There is one nifty detail which seems VERY strange to me: If
> serializable mode is set in postgresql.conf the system was 3 times
> faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every
> transaction (using set at the beginning of the transaction) serializable
> mode was as fast as read committed.
Hmm.
>
> We have done 90% cursor work and very simple queries (mostly queries
> such as "DECLARE CURSOR x FOR SELECT * FROM ... WHERE a = b").
> I have no idea why PostgreSQL behaves like that but it seems to be a
> really good tweak because in this mode we beat any other database
> including SQL server on Windows 2003 (2.9sec) and IBM DB2 on Linux (12.6
> seconds).
Are you testing the same type of cursors? Cursors do not behave
according to READ COMMITTED principles under Postgres. What are the
results for READ ONLY INSENSITIVE cursors with DB2 and MS SQL?
Thanks,
Gavin