Re: Performance improvement for unique checks

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Gokulakannan Somasundaram <gokul007(at)gmail(dot)com>
Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Performance improvement for unique checks
Date: 2010-03-27 17:03:35
Message-ID: 1269709415.3684.1958.camel@ebony
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-03-27 19:06:53 Re: join removal
Previous Message Tom Lane 2010-03-27 14:50:21 Re: join removal