Обсуждение: Performance Improvement for Unique Indexes
Hi,<br /> While i was studying the unique index checks very closely, i realized that what we need is to find out whetherthe tuple is deleted / not. So say a tuple is deleted by a transaction, but it is not dead( because of some long runningtransaction ), still we can mark a hint bit as deleted and it will help the subsequent transactions doing the uniquechecks. As a matter of fact, it will help the deferred_unique cases, since it will anyway check the tuples twice, ifthere is a duplicate.<br /> So we have one bit left in the Index Tuple that can be used as hint bit. If we are readyto break the disk compatibility, then we can store the size as a multiple of 8, and we will get three bits free. Anycomments?<br /><br />Thanks,<br />Gokul.<br />
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.
Gokul.
On Wed, Mar 24, 2010 at 4:53 PM, Robert Haas <robertmhaas@gmail.com> wrote:
I don't think this works. The postulated long-running transactionOn 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. 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?
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@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.
Gokul.