Date: Fri, 17 Dec 2021 16:43:43 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Chris Lowder <clowder@hey.com>
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: 11.7. Indexes on Expressions
Message-ID: <20211217214343.GC5592@momjian.us>
References: <20211216205232.GA30462@momjian.us>
 <b4093b5a1383fe157e7dfd52606f4173352fd4fb@hey.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="vkogqOf2sHV7VnPd"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <b4093b5a1383fe157e7dfd52606f4173352fd4fb@hey.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20211217214343.GC5592%40momjian.us>
Precedence: bulk


--vkogqOf2sHV7VnPd
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Fri, Dec 17, 2021 at 09:41:32AM +0000, Chris Lowder wrote:
> Thank you for that (speedy) explanation and patch Bruce! That all makes perfect
> sense. 

There are actually two questions being addressed here:

1.  In what cases are new index entries added, and for which indexes?
2.  In what cases are index expressions evaluated?

For 1, I explained that for updates we either add new rows for all
indexes, or none of them.  We create new index rows for non-HOT updates,
which happens if the updated row does not change indexed columns (or
columns involved in index expressions), and if the new row fits on the
same 8k heap page.

What I recently learned, at least for PG 10-14, is that the index
expression is only called for non-HOT updates, not HOT ones.  Postgres
does not evaluate the index expression to determine if the indexed
expression has changed, but rather relies on column value comparisons.

I wrote the attached script which creates an expression index function
with a pg_sleep(1) call to determine if the function is called.  If you
run the attached SQL script in psql, you will see that times of ~1
second happens only when there is a change in the colummn referenced by
the index expression or when the new row is stored on a new heap page
(ctid page number changes).  The expression index function is not called
if the indexed column value does not change and remains on the same heap
page.

I am attaching an updated patch which mentions non-HOT updates for
expression indexes.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.


--vkogqOf2sHV7VnPd
Content-Type: application/x-sql
Content-Disposition: attachment; filename="expr.sql"
Content-Transfer-Encoding: quoted-printable

CREATE TEMPORARY TABLE test(a int, b int, c text);=0A=0ACREATE OR REPLACE F=
UNCTION expr(b int) RETURNS int AS $$=0ABEGIN=0APERFORM pg_sleep(1);=0ARETU=
RN b;=0AEND=0A$$ LANGUAGE plpgsql IMMUTABLE;=0A=0ACREATE INDEX i_test ON te=
st (expr(b));=0A=0A\timing=0A=0AINSERT INTO test VALUES (1, 1, NULL);=0A=0A=
UPDATE test SET a =3D 1;=0A=0AUPDATE test SET a =3D 2;=0A=0AUPDATE test SET=
 b =3D 1;=0A=0AUPDATE test SET b =3D 2;=0A=0AUPDATE test SET b =3D 3;=0A=0A=
UPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=
=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM t=
est;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FR=
OM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid=
) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(=
ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT =
max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASEL=
ECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=
=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000)=
;=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1=
000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repeat('x=
', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D repea=
t('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =3D r=
epeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SET c =
=3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE test SE=
T c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE tes=
t SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUPDATE=
 test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=0AUP=
DATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test;=0A=
=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM test=
;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) FROM =
test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(ctid) F=
ROM test;=0A=0AUPDATE test SET c =3D repeat('x', 1000);=0A=0ASELECT max(cti=
d) FROM test;=0A
--vkogqOf2sHV7VnPd
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..023157d888 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 non-HOT 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>

--vkogqOf2sHV7VnPd--