Skip site navigation (1) Skip section navigation (2)

Performance improvement for unique checks

From: Gokulakannan Somasundaram <gokul007(at)gmail(dot)com>
To: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
Subject: Performance improvement for unique checks
Date: 2010-03-26 20:53:21
Message-ID: 9362e74e1003261353r22f96499h2ae2bc26a07170c8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,
   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?

Thanks,
Gokul.

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-03-26 21:36:03
Subject: Re: join removal
Previous:From: Tom LaneDate: 2010-03-26 20:16:21
Subject: Re: Re: [COMMITTERS] pgsql: Augment WAL records for btree delete with GetOldestXmin() to

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group