"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:

> Tom Lane wrote:
>> What if we only applied
>> HOT to primary-key indexes, so that there was certainly not more than
>> one index per table that the property applies to?
> The main objective of HOT is to enable retail vacuum of HOT-updated tuples.
> Doing the above would make it useless for that purpose, at least when there's
> more than one index on the table. Granted, there's a lot of tables with just
> one index out there, but it's a big limitation nevertheless.
> An extension of that idea, though is to store a flag per index in the
> HOT-updated tuple. We would then need a mapping between bits in the tuple
> header to indexes, for example as a new column in pg_index.

I had an interesting thought this morning, these bits might less us do retail
vacuum in a lot of cases.

When you traverse an index and find that an index pointer points to a DEAD
tuples you set the LP_DELETE flag. If at that time you set the tuple's bit
indicating that the index pointer for that index then if we find all the bits
are set (and whatever condition we have for ensuring that all indexes are
represented by bits) we know there are no index pointers left and the tuple
can now be retail vacuumed.

I think in order for this to work we may want a rule that we don't have to
dirty a page to set a "index pointer missing" bit though we would certainly
need to dirty it (and wal log) it if we *clear* a bit. In other words a set
bit would be a guarantee that the index pointer was missing but a clear bit
would only be a hint that it might be present.

The main problem with this is that it would necessitate WAL logging setting
the LP_DELETE flag on index pointers which could be a large overhead for a

This interacts with two other proposed changes, HOT and truncating line
pointers, which I think are both valuable. But I think it works with both.

What I would suggest is the following:

When we follow an index pointer, find a (non-truncated) DEAD tuple we truncate
the line pointer, and initialize the length bits to an empty bitmask of "index
pointer missing" flags. In the case of a HOT-updated tuple HOT would have to
provide enough information for us to initialize the bitmask -- it's the same
information that it needs anyways. Then we set the LP_DELETE flag on our own
index pointer (and wal log it) and set the corresponding bit on the page.

If we find an index pointer pointing to a truncated line pointer we set the
flag in the bitmask. If we find that all the bits are set indicating that all
indexes have successfully set their LP_DELETE flag then we the line pointer
can be marked as !LP_USED.

Of course only certain types of indexes would be able to do this, indexes that
only ever have exactly one pointer to every tuple, and which have space for an
LP_DELETED or equivalent flag. I believe currently this includes all except

The part I'm most worried about with both this and the equivalent bits for HOT
are maintaining the mapping from index to bit. I think it could be worked out,
but it has to be done carefully. Dropping an index can't ever change the
mapping and creating a new index can't ever leave a tuple with a bit
incorrectly set for that index.

  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to