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