"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 SELECT. 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 GIN. 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
