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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
