Over the weekend I noticed that Tatsuo's pgbench benchmark seems to
spend an awful lot of its time down inside _bt_check_unique. This
happens because with table declarations like
create table accounts(aid int, primary key(aid), bid int, abalance int, filler char(84))
and commands like
update accounts set abalance = abalance + x where aid = y
the "update" is inserting a new tuple and the index on aid wants to
make sure this insertion doesn't violate the uniqueness constraint.
To do that, it has to visit all active *and dead* tuples with the same
aid index value, to make sure they're all deleted or being deleted by
the current transaction. That's expensive if they're scattered all over
the table.
However, since we have not changed the aid column from its prior value,
it seems like this check is wasted effort. We should be able to deduce
that if the prior state of the row was OK then this one is too.
I'm not quite sure how to implement this, but I wanted to toss the idea
out for discussion. Probably we'd have to have some cooperation between
the heap_update level (where the fact that it's an update is known, and
where we'd have a chance to test for changes in particular columns) and
the index access level. Maybe it's wrong for the index access level to
have primary responsibility for uniqueness checks in the first place.
Obviously this isn't going to happen for 7.1, but it might make a nice
performance improvement for 7.2.
Comments?
regards, tom lane
