Date: Thu, 16 Dec 2021 15:52:32 -0500
From: Bruce Momjian <bruce@momjian.us>
To: clowder@hey.com, pgsql-docs@lists.postgresql.org
Subject: Re: 11.7. Indexes on Expressions
Message-ID: <20211216205232.GA30462@momjian.us>
References: <163967385701.26064.15365003480975321072@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="tKW2IUtsqtDRztdT"
Content-Disposition: inline
In-Reply-To: <163967385701.26064.15365003480975321072@wrigleys.postgresql.org>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20211216205232.GA30462%40momjian.us>
Precedence: bulk


--tKW2IUtsqtDRztdT
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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.


--tKW2IUtsqtDRztdT
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="expression.diff"

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>

--tKW2IUtsqtDRztdT--