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>
