Thank you for that (speedy) explanation and patch Bruce! That all makes perfect sense.
Cheers,
- Chris
On December 16, 2021, Bruce Momjian <bruce@momjian.us> wrote:
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@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
