Re: 11.7. Indexes on Expressions

On Thu, Dec 16, 2021 at 04:57:37PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/indexes-expressional.html
> Description:
>
> Regarding the paragraph (_emphasis_ added):
>
> > Index expressions are relatively expensive to maintain, because the
> derived expression(s) must be computed for _each row upon insertion and
> whenever it is updated_.
>
> Does the "it" in the above refer to the entire row or just the columns used

So, here 'it' refers the "row" being updated. I am attaching a doc
patch which clarifies this.

> in the expression? Does updating any column of a row trigger an update to
> all indexes with expressions?

Well, that depends. If _no_ columns involved in any indexes are
changed, and the row is placed in the same page as the previous row (a
HOT update), then the indexes do not need to be updated. If any indexed
column changes, including those involved in expression indexes, or the
updated row isn't placed in the same page as the previous row, all new
index entries will need to be created. We don't really have the concept
of updating some indexes and not others --- we either update them all,
or update none of them --- this is because Postgres uses an MVCC system
of versioning.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.