Bruce Momjian wrote:
> Hans-J?rgen Sch?nig wrote:
>
>>This week I have done some performance tuning at a customer's office. We
>>have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in
>>any transaction isolation level :).
>>
>>By the way: In case of very simple statements SERIALIZABLE is about 3
>>times faster than READ COMMITTED. I expected it to be faster but I did
>>not expect this difference.
>
>
> 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.
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).
I am sorry but I cannot provide you the tools we have used because we
have a non disclosure agreement with the customer. I will try to verify
this with my machines and a simple self-made benchmark.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
