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.
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 56fbd45178..783042e5c3 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -748,8 +748,8 @@ CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); <para> 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. However, the index expressions are + derived expression(s) must be computed for each row insertion + and row update. However, the index expressions are <emphasis>not</emphasis> recomputed during an indexed search, since they are already stored in the index. In both examples above, the system sees the query as just <literal>WHERE indexedcolumn = 'constant'</literal>