On Thu, 2023-03-02 at 15:53 +0800, Navindren Baskaran wrote:
> We would like to understand in which scenario an index on a table will be rebuilt.
I assume that you are meaning "updated" or "modified" rather than rebuilt from scratch.
> Example if we have a table, which has two columns and one if it is indexed,
> which of the below operation trigger an index rebuild:
>
> 1. updating the value of the indexed column.
> 2. updating the value of the non-indexed column.
If the indexed column is updated, a new index entry has to be added.
If the other column is updated, it depends. If the updated column is not
indexed and there is enough room for the new row version in the same
table block, the index doesn't have to be modified. Otherwise it is.
See https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/
Yours,
Laurenz Albe