Performance Improvement for Unique Indexes
Hi,
While i was studying the unique index checks very closely, i realized
that what we need is to find out whether the tuple is deleted / not. So say
a tuple is deleted by a transaction, but it is not dead( because of some
long running transaction ), still we can mark a hint bit as deleted and it
will help the subsequent transactions doing the unique checks. As a matter
of fact, it will help the deferred_unique cases, since it will anyway check
the tuples twice, if there is a duplicate.
So we have one bit left in the Index Tuple that can be used as hint bit.
If we are ready to break the disk compatibility, then we can store the size
as a multiple of 8, and we will get three bits free. Any comments?
Thanks,
Gokul.
There is no issue with that. Because we are taking a Dirty Snapshot to do
the comparison not the MVCC one. But this should be used only for unique
checks and not for the visibility checks.
Gokul.
On Wed, Mar 24, 2010 at 4:53 PM, Robert Haas <robertmhaas@gmail.com> wrote:
Show quoted text
On Wed, Mar 24, 2010 at 7:17 AM, Gokulakannan Somasundaram
<gokul007@gmail.com> wrote:While i was studying the unique index checks very closely, i realized
that what we need is to find out whether the tuple is deleted / not. Sosay
a tuple is deleted by a transaction, but it is not dead( because of some
long running transaction ), still we can mark a hint bit as deleted andit
will help the subsequent transactions doing the unique checks. As a
matter
of fact, it will help the deferred_unique cases, since it will anyway
check
the tuples twice, if there is a duplicate.
So we have one bit left in the Index Tuple that can be used as hintbit.
If we are ready to break the disk compatibility, then we can store the
size
as a multiple of 8, and we will get three bits free. Any comments?
I don't think this works. The postulated long-running transaction
would also see the hint bit......Robert
Import Notes
Reply to msg id not found: 603c8f071003240423r615ebd3dqc3700ae75b4ce4f@mail.gmail.com
How are you going to unmark the hint bit in case of a rollback?
Only after you find that the transaction is committed, this hint bit has to
be set. It is equivalent to any other hint bit.
Gokul.
Import Notes
Reply to msg id not found: f96a9b831003240454j7f0dce14q43c89399f6943f24@mail.gmail.com
Gokulakannan Somasundaram <gokul007@gmail.com> writes:
While i was studying the unique index checks very closely, i realized
that what we need is to find out whether the tuple is deleted / not. So say
a tuple is deleted by a transaction, but it is not dead( because of some
long running transaction ), still we can mark a hint bit as deleted and it
will help the subsequent transactions doing the unique checks. As a matter
of fact, it will help the deferred_unique cases, since it will anyway check
the tuples twice, if there is a duplicate.
It seems fairly unlikely to me that this would be useful enough to
justify using up a precious hint bit. The applicability of the hint
is very short-term --- as soon as the tuple is dead to all transactions,
it can be marked with the existing LP_DEAD hint bit. And if it's only
useful for uniqueness checks, as seems to be the case, that's another
big restriction on the value.
regards, tom lane
it seems fairly unlikely to me that this would be useful enough to
justify using up a precious hint bit. The applicability of the hint
is very short-term --- as soon as the tuple is dead to all transactions,
it can be marked with the existing LP_DEAD hint bit. And if it's only
useful for uniqueness checks, as seems to be the case, that's another
big restriction on the value.Right. It is of little value.
Gokul.