Обсуждение: HOT update: why must ALL indexes should be update?
We have many indexes on a table. The chance is that any column could be some part of an index. I understand that when an index has a column that is updated, we can't use HOT, because the index entry could point to a totally different row. But if I have 10 indexes, if only 1 index will be updated, why the rest 9 indexes couldn't use HOT update?
Daniel Wu <staywithpin@gmail.com> writes: > We have many indexes on a table. The chance is that any column could be > some part of an index. I understand that when an index has a column that is > updated, we can't use HOT, because the index entry could point to a totally > different row. But if I have 10 indexes, if only 1 index will be updated, > why the rest 9 indexes couldn't use HOT update? Whether it's a HOT update is a property of the table row, not of individual indexes. regards, tom lane
On Fri, Apr 21, 2023 at 10:19:42AM -0400, Tom Lane wrote: > Daniel Wu <staywithpin@gmail.com> writes: > > We have many indexes on a table. The chance is that any column could be > > some part of an index. I understand that when an index has a column that is > > updated, we can't use HOT, because the index entry could point to a totally > > different row. But if I have 10 indexes, if only 1 index will be updated, > > why the rest 9 indexes couldn't use HOT update? > > Whether it's a HOT update is a property of the table row, not of > individual indexes. Right, specifically the item pointer on the heap/table page can't be reused because _one_ of the indexes needs a new item pointer to point to. See the later sections of this talk for diagrams: https://momjian.us/main/presentations/internals.html#mvcc -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.