Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1myF7A-0006Wv-D0 for pgsql-docs@arkaria.postgresql.org; Fri, 17 Dec 2021 15:25:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1myF6d-00087s-Pt for pgsql-docs@arkaria.postgresql.org; Fri, 17 Dec 2021 15:25:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1my9kD-0001mu-7e for pgsql-docs@lists.postgresql.org; Fri, 17 Dec 2021 09:41:41 +0000 Received: from 102d.relay.hey.com ([204.62.115.202]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1my9k6-0005WO-Gx for pgsql-docs@lists.postgresql.org; Fri, 17 Dec 2021 09:41:40 +0000 Received: from hey.com (bigip-vip.rw-ash-int.37signals.com [10.20.0.24]) by 102.relay.hey.com (Postfix) with ESMTPS id 2633A204E0; Fri, 17 Dec 2021 09:41:32 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hey.com; s=heymail; t=1639734093; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to; bh=hxJ1kpR7ct12zuWnlMJV1+CrlBr5dTU7K8P0m4SiB6s=; b=g2wDuC1/RdDMlMGFQH/YinpDH/gJ0t1RfbX0KKouGXvX569FpSZXKJSTPme/zIWZGUbAmn JdOtZ6piWXlKikE1w9jaYnqtPYlaxb2iKcG0fbTPUiX3agc9yrjhHRprbhVsDH8v8cgulZ jK/NFw+vcoTowObxaw5Oq5INeQC5E802/h7wNhHALndrc+hyqcgHmSwbOmjS7BxqLlKoUx b8RdXL0Ce//wVOVeu+0QkIg1ng1l35TwHCBvnYJ3MsSOdVkYpEfKBqlGLbjyntriDiccI/ Aqt8+lbYAMtumtVQyo3b78BNb1VkYx9E+Dy+J/xEI8z0mrDajO2/As4kVJjO3g== Date: Fri, 17 Dec 2021 09:41:32 +0000 From: Chris Lowder To: pgsql-docs@lists.postgresql.org, Bruce Momjian Message-ID: In-Reply-To: <20211216205232.GA30462@momjian.us> Subject: Re: 11.7. Indexes on Expressions Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="--==_mimepart_61bc5b4ccef50_50e52580609d4"; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ----==_mimepart_61bc5b4ccef50_50e52580609d4 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thank you for that (speedy) explanation and patch Bruce! That all makes perfect sense.=C2=A0 Cheers, - Chris On December 16, 2021, Bruce Momjian 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: > >=C2=A0 > > Page: https://www.postgresql.org/docs/14/indexes-expressional.html > > Description: > >=C2=A0 > > Regarding the paragraph (_emphasis_ added): > >=C2=A0 > > > 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_. > >=C2=A0 > > 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?=C2=A0 > > 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. > > --=C2=A0 > =C2=A0Bruce Momjian https://momjian.us > =C2=A0EDB https://enterprisedb.com > > =C2=A0If only the physical world exists, free will is an illusion. ----==_mimepart_61bc5b4ccef50_50e52580609d4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thank you for that (speedy) explanation and patch Bruce! That all = makes perfect sense.=C2=A0

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:
>=C2=A0
> Page: https://www.postgresq= l.org/docs/14/indexes-expressional.html
> Description:
>=C2=A0=
> Regarding the paragraph (_emphasis_ added):
>=C2=A0
>= ; > Index expressions are relatively expensive to maintain, because th= e
> derived expression(s) must be computed for _each row upon inser= tion and
> whenever it is updated_.
>=C2=A0
> Does the = "it" in the above refer to the entire row or just the columns u= sed

So, here 'it' refers the "row" being updated. I am a= ttaching a doc
patch which clarifies this.

> in the expressi= on? Does updating any column of a row trigger an update to
> all in= dexes with expressions?=C2=A0

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 involve= d in expression indexes, or the
updated row isn't placed in the same p= age 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 ot= hers --- we either update them all,
or update none of them --- this is= because Postgres uses an MVCC system
of versioning.

--=C2=A0=C2=A0Bruce Momjian <bruce@momjian.us> https://momjian.us
=C2=A0= EDB https://enterprisedb.com

=C2=A0If only the physical world exis= ts, free will is an illusion.
----==_mimepart_61bc5b4ccef50_50e52580609d4--