From: Chris Lowder <clowder@hey.com>
To: pgsql-docs@lists.postgresql.org, Bruce Momjian <bruce@momjian.us>
Subject: Re: 11.7. Indexes on Expressions
Date: Fri, 17 Dec 2021 09:41:32 +0000
Message-ID: <b4093b5a1383fe157e7dfd52606f4173352fd4fb@hey.com> (raw)
In-Reply-To: <20211216205232.GA30462@momjian.us>
Thank you for that (speedy) explanation and patch Bruce! That all makes
perfect sense.
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: clowder@hey.com, bruce@momjian.us
Subject: Re: 11.7. Indexes on Expressions
In-Reply-To: <b4093b5a1383fe157e7dfd52606f4173352fd4fb@hey.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox