Re: 11.7. Indexes on Expressions

Thank you for that (speedy) explanation and patch Bruce! That all makes
perfect sense. 

Cheers,
- Chris

On December 16, 2021, Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> https://momjian.us
>  EDB https://enterprisedb.com
>
>  If only the physical world exists, free will is an illusion.