Thread: 11.7. Indexes on Expressions
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 in the expression? Does updating any column of a row trigger an update to all indexes with expressions? Thanks in advance for your time and reply. Cheers, Chris Lowder
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.
Attachment
Thank you for that (speedy) explanation and patch Bruce! That all makes perfect sense.
Cheers,
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:
>
> 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.
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.