Re: PostgreSQL 7.3.3 and Intel C compiler

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