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.
Let's assume that we'd use one byte in the tuple header to store
HOT-flags. That's enough to store the HOT-status for 8 indexes. A set
bit means that the index corresponding that bit doesn't have an index
pointer in it.
When you CREATE INDEX, assign a bit for the new index that's not
currently in use. When you scan the table to build the index, clear that
bit for every tuple if set and insert index entry as usual.
DROP INDEX wouldn't need to scan the heap to clear the flags, because we
clear them on CREATE INDEX when necessary.
If you run out of bits in the header, IOW have more than 8 indexes on a
table, indexes unlucky enough to not have a bit assigned to them
wouldn't be HOT-updateable.
This would also enable us to skip index inserts for those indexes whose
key columns are not updated, and do the index inserts as usual for the
rest. The limitation that you can only retail vacuum HOT-updated tuples
when none of the indexed keys were changed remains, but we've accepted
that already.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
