Thread: Performance improvement for unique checks

Hi,<br />   Since we insert a new entry into the index for every update that's being made into the table, we inevitably
makea unique check against the older version of the newly inserted row, even when the values are not updated. Of course
iam talking about non-HOT updates. (We will not go to the index for HOT updates)<br /><br />a) The page which contains
theindex entry is Exclusively locked<br />b) We go ahead and visit the heap page for its HeapTupleSatisfiesDirty.<br
/><br/>If we have the information of the old tuple(its tuple-id) after a heap update, during the index insert, we can
avoidthe uniqueness check for this tuple,as we know for sure that tuple won't satisfy the visibility criteria. If the
tablehas 'n' unique indexes it avoids 'n' heap tuple lookups, also increasing the concurrency in the btree, as the
writelock duration is reduced.<br /><br />Any comments?<br /><br />Thanks,<br />Gokul.<br /> 

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