SSI performance

We know that the predicate locking introduced by the serializable 
snapshot isolation patch adds a significant amount of overhead, when 
it's used. It was fixed for sequential scans by acquiring a relation 
level lock upfront and skipping the locking after that, but the general 
problem for index scans and bitmap index scans remains.

I ran a little benchmark of that:

postgres=# begin isolation level repeatable read;
BEGIN
Time: 0,262 ms
postgres=#  SELECT COUNT(*) FROM foo WHERE id < 400000;
  count
--------
  399999
(1 row)

Time: 204,571 ms

postgres=# begin isolation level serializable;
BEGIN
Time: 0,387 ms
postgres=#  SELECT COUNT(*) FROM foo WHERE id < 400000;
  count
--------
  399999
(1 row)

Time: 352,293 ms

These numbers are fairly repeatable.

I ran oprofile to see where the time is spent, and fed the output to 
kcachegrind to get a better breakdown. Attached is a screenshot of that 
(I don't know how to get this information in a nice text format, sorry). 
As you might expect, about 1/3 of the CPU time is spent in 
PredicateLockTuple(), which matches with the 50% increase in execution 
time compared to repeatable read. IOW, all the overhead comes from 
PredicateLockTuple.

The interesting thing is that CoarserLockCovers() accounts for 20% of 
the overall CPU time, or 2/3 of the overhead. The logic of 
PredicateLockAcquire is:

1. Check if we already have a lock on the tuple.
2. Check if we already have a lock on the page.
3. Check if we already have a lock on the relation.

So if you're accessing a lot of rows, so that your lock is promoted to a 
relation lock, you perform three hash table lookups on every 
PredicateLockAcquire() call to notice that you already have the lock.

I was going to put a note at the beginning of this mail saying upfront 
that this is 9.2 materila, but it occurs to me that we could easily just 
reverse the order of those tests. That would cut the overhead of the 
case where you already have a relation lock by 2/3, but make the case 
where you already have a tuple lock slower. Would that be a good tradeoff?

For 9.2, perhaps a tree would be better than a hash table for this..
-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com