Re: 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(at)gmail(dot)com> wrote:

> On Wed, Mar 24, 2010 at 7:17 AM, Gokulakannan Somasundaram
> <gokul007(at)gmail(dot)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. 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?
>
> I don't think this works. The postulated long-running transaction
> would also see the hint bit...
>
> ...Robert
>

>
>
> 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.

Gokulakannan Somasundaram <gokul007(at)gmail(dot)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.