Performance improvement for unique checks

On Sat, 2010-03-27 at 02:23 +0530, Gokulakannan Somasundaram wrote:

Since we insert a new entry into the index for every update that's
being made into the table, we inevitably make a unique check against
the older version of the newly inserted row, even when the values are
not updated. Of course i am talking about non-HOT updates. (We will
not go to the index for HOT updates)

a) The page which contains the index entry is Exclusively locked
b) We go ahead and visit the heap page for its
HeapTupleSatisfiesDirty.

If we have the information of the old tuple(its tuple-id) after a heap
update, during the index insert, we can avoid the uniqueness check for
this tuple,as we know for sure that tuple won't satisfy the visibility
criteria. If the table has 'n' unique indexes it avoids 'n' heap tuple
lookups, also increasing the concurrency in the btree, as the write
lock duration is reduced.

Any comments?

Please write it, then test the performance and publish your results,
with a detailed analysis of whether there is benefit and in which cases
there is a loss.

--
Simon Riggs www.2ndQuadrant.com

Please write it, then test the performance and publish your results,
with a detailed analysis of whether there is benefit and in which cases
there is a loss.

Thanks. Will do it. Just wanted to know, whether the idea will get

rejected right away / worth trying out.

Thanks,
Gokul.