Re: CREATE INDEX and HOT - revised design

On 3/21/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>
>
> A different idea is to flag the _index_ as using HOT for the table or
> not, using a boolean in pg_index. The idea is that when a new index is
> created, it has its HOT boolean set to false and indexes all tuples and
> ignores HOT chains. Then doing lookups using that index, the new index
> does not follow HOT chains. We also add a boolean to pg_class to
> indicate no new HOT chains should be created and set that to false once
> the new index is created. Then, at some later time when all HOT chains
> are dead, we can enable HOT chain following for the new index and allow
> new HOT chains to be created.
>
> A more sophisticated idea would be to place an xid, rather than a
> boolean, in pg_index to indicate which chains were created after the
> index was created to control whether the index should follow that HOT
> chain, or ignore it. The xmax of the head of the HOT chain can be used
> as an indicator of when the chain was created. Transactions started
> before the pg_index xid could continue following the old rules and
> insert into the _new_ index for HOT chain additions, and new
> transactions would create HOT chains that could skip adding to the new
> index. Cleanup of the hybrid HOT chains (some indexes take part, some
> do not) would be more complex.
>
>
>
Bruce, thanks for bringing up this idea.

As I think more about this idea, I think I am able to extend this further
to solve the problems we discussed around it. One of my conerns
were that the change the basic structure of heap
with HOT-chains so that it should be possible to just look at the
heap tuple and say whether it has any index pointer or not.

The way I propose to extend/modify the idea is to use pg_index xid
as suggested by Bruce to mark the index. This xid would guide the
visibility of the index. As we all know, CREATE INDEX locks out
UPDATEs on the table and further UPDATEs are possible only after
the transaction creating the new index commits.

When CREATE INDEX starts, it acquires ShareLock on the table.
At this point we may have one or more HOT-update chains in the
table. Tuples in this chain may be visible to one or more running
transactions. The fact that we have ShareLock on the table means
that all tuples in the chain except the one at the head either
RECENTLY_DEAD or were UPDATEd by the same transaction
that is now running CREATE INDEX.

With this background, I propose to index ONLY the head of the
HOT-chain. The TID of the root tuple is used instead of the actual
TID of the tuple being indexed. This index will not be available to
the transactions which are started before the CREATE INDEX
transaction. Just like we use "indisvalid" flag to avoid including
an invalid index in the plan, we use the pg_index "xid" to decide
whether to use the index in the plan or not. Only transactions with
txid > pg_index:xid can see the index and use it.

In fact, the serializable transactions started before CREATE INDEX
can not anyway see the index so all this is done to handle
read-committed transactions.

In this proposal we indexed only the latest version. But none of the
transactions started after CREATE INDEX can anyway see the
older tuples and hence we should be fine even if we don't index
them in the new index. And none of the older transaction can see
the index, so again we are safe. The design also helps us to
preserve the heap HOT semantics and chain pruning and does not
need VACUUM or any special handling.

Can anyone spot a hole in this logic ? Comments ?

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com