pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feed11.7. Indexes on Expressions
14+ messages / 6 participants
[nested] [flat]
* 11.7. Indexes on Expressions
@ 2020-04-10 02:47 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 14+ messages in thread
From: PG Doc comments form @ 2020-04-10 02:47 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: tlamarre91@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/12/indexes-expressional.html
Description:
It appears as if the paragraph beginning: "If we were to declare this index
UNIQUE..."
actually belongs just before the paragraph beginning: "Expression indexes
also allow control over..."
as it appears to pertain to the previous example block.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2020-04-10 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2020-04-10 14:46 UTC (permalink / raw)
To: tlamarre91@gmail.com; +Cc: pgsql-docs@lists.postgresql.org
PG Doc comments form <noreply@postgresql.org> writes:
> Page: https://www.postgresql.org/docs/12/indexes-expressional.html
> It appears as if the paragraph beginning: "If we were to declare this index
> UNIQUE..."
> actually belongs just before the paragraph beginning: "Expression indexes
> also allow control over..."
> as it appears to pertain to the previous example block.
Yeah, I agree, somebody was sloppy about inserting a new paragraph.
Will fix, thanks for the report!
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* 11.7. Indexes on Expressions
@ 2021-12-16 16:57 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 14+ messages in thread
From: PG Doc comments form @ 2021-12-16 16:57 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: clowder@hey.com
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-16 20:52 Bruce Momjian <bruce@momjian.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2021-12-16 20:52 UTC (permalink / raw)
To: clowder@hey.com; pgsql-docs@lists.postgresql.org
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.
Attachments:
[text/x-diff] expression.diff (832B, ../../20211216205232.GA30462@momjian.us/2-expression.diff)
download | inline diff:
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 56fbd45178..783042e5c3 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 row 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>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-17 09:41 Chris Lowder <clowder@hey.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 14+ messages in thread
From: Chris Lowder @ 2021-12-17 09:41 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org, Bruce Momjian <bruce@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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-17 21:43 Bruce Momjian <bruce@momjian.us>
parent: Chris Lowder <clowder@hey.com>
0 siblings, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2021-12-17 21:43 UTC (permalink / raw)
To: Chris Lowder <clowder@hey.com>; +Cc: pgsql-docs@lists.postgresql.org
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.
Attachments:
[application/x-sql] expr.sql (1.9K, ../../20211217214343.GC5592@momjian.us/2-expr.sql)
download
[text/x-diff] expression.diff (836B, ../../20211217214343.GC5592@momjian.us/3-expression.diff)
download | inline 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>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 21:40 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2021-12-22 21:40 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Fri, Dec 17, 2021 at 2:43 PM Bruce Momjian <bruce@momjian.us> wrote:
> I am attaching an updated patch which mentions non-HOT updates for
> expression indexes.
>
Thanks. I'm a bit concerned that before this patch the only place we
mentioned HOTin the docs is in the appendix acronym listing which points to
the readme file in the source code.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 21:45 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2021-12-22 21:45 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 02:40:12PM -0700, David G. Johnston wrote:
> On Fri, Dec 17, 2021 at 2:43 PM Bruce Momjian <bruce@momjian.us> wrote:
>
> I am attaching an updated patch which mentions non-HOT updates for
> expression indexes.
>
>
> Thanks. I'm a bit concerned that before this patch the only place we mentioned
> HOTin the docs is in the appendix acronym listing which points to the readme
> file in the source code.
Uh, I had a similar concern and found these cases:
monitoring.sgml: Number of rows updated (includes HOT updated rows)
monitoring.sgml: Number of rows HOT updated (i.e., with no separate index
Not sure what else we can do --- it would be odd to explain it in the
expression index docs.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2021-12-22 22:06 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 2:45 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Dec 22, 2021 at 02:40:12PM -0700, David G. Johnston wrote:
> > On Fri, Dec 17, 2021 at 2:43 PM Bruce Momjian <bruce@momjian.us> wrote:
> >
> > I am attaching an updated patch which mentions non-HOT updates for
> > expression indexes.
> >
> >
> > Thanks. I'm a bit concerned that before this patch the only place we
> mentioned
> > HOTin the docs is in the appendix acronym listing which points to the
> readme
> > file in the source code.
>
> Uh, I had a similar concern and found these cases:
>
> monitoring.sgml: Number of rows updated (includes HOT
> updated rows)
> monitoring.sgml: Number of rows HOT updated (i.e., with no
> separate index
>
> Not sure what else we can do --- it would be odd to explain it in the
> expression index docs.
>
>
Found another one:
https://www.postgresql.org/docs/current/btree-implementation.html
"...(where most individual updates cannot apply the HOT optimization."
Make the acronym HOT in all these places take the reader to the acronym
page, and add a brief description there so they don't need to consume the
readme file?
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:09 Bruce Momjian <bruce@momjian.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Bruce Momjian @ 2021-12-22 22:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 03:06:12PM -0700, David G. Johnston wrote:
> On Wed, Dec 22, 2021 at 2:45 PM Bruce Momjian <bruce@momjian.us> wrote:
> Not sure what else we can do --- it would be odd to explain it in the
> expression index docs.
>
>
>
> Found another one:
>
> https://www.postgresql.org/docs/current/btree-implementation.html
> "...(where most individual updates cannot apply the HOT optimization."
>
> Make the acronym HOT in all these places take the reader to the acronym page,
> and add a brief description there so they don't need to consume the readme
> file?
Wow, I don't think the acronym page is the right place for this --- I
think we need a new section for this somewhere.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:13 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 2 replies; 14+ messages in thread
From: Bruce Momjian @ 2021-12-22 22:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 05:09:53PM -0500, Bruce Momjian wrote:
> On Wed, Dec 22, 2021 at 03:06:12PM -0700, David G. Johnston wrote:
> > On Wed, Dec 22, 2021 at 2:45 PM Bruce Momjian <bruce@momjian.us> wrote:
> > Not sure what else we can do --- it would be odd to explain it in the
> > expression index docs.
> >
> >
> >
> > Found another one:
> >
> > https://www.postgresql.org/docs/current/btree-implementation.html
> > "...(where most individual updates cannot apply the HOT optimization."
> >
> > Make the acronym HOT in all these places take the reader to the acronym page,
> > and add a brief description there so they don't need to consume the readme
> > file?
>
> Wow, I don't think the acronym page is the right place for this --- I
> think we need a new section for this somewhere.
I think our _big_ missing information is a definition of HOT updates and
that updating a column involved in an index disables HOT updates.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:14 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2021-12-22 22:14 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 3:09 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Dec 22, 2021 at 03:06:12PM -0700, David G. Johnston wrote:
> > On Wed, Dec 22, 2021 at 2:45 PM Bruce Momjian <bruce@momjian.us> wrote:
> > Not sure what else we can do --- it would be odd to explain it in the
> > expression index docs.
> >
> >
> >
> > Found another one:
> >
> > https://www.postgresql.org/docs/current/btree-implementation.html
> > "...(where most individual updates cannot apply the HOT optimization."
> >
> > Make the acronym HOT in all these places take the reader to the acronym
> page,
> > and add a brief description there so they don't need to consume the
> readme
> > file?
>
> Wow, I don't think the acronym page is the right place for this --- I
> think we need a new section for this somewhere.
>
>
I don't disagree, but it also doesn't seem so wrong that it is
unacceptable; and it is minimally invasive. I don't really see a great
existing place to slot it in.
Now, it seems like it would best fit as a sibling to the other index
internals that we cover - which are all listed on the main table of
contents. i.e., In the current docs my best choice we be to add a new
Chapter 64 for this. I would have wished for all of those index chapters
to be in grouped together under "64. Index Internals" and this would just
go in there. The Database Physical Storage would be Chapter 65.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:18 David G. Johnston <david.g.johnston@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2021-12-22 22:18 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 3:13 PM Bruce Momjian <bruce@momjian.us> wrote:
> I think our _big_ missing information is a definition of HOT updates and
> that updating a column involved in an index disables HOT updates.
>
Along those lines adding "51.7 Special Considerations for Updates" would be
minimally invasive. Having this under "Overview of PostgreSQL Internals"
feels ok.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 11.7. Indexes on Expressions
@ 2021-12-22 22:30 Peter Geoghegan <pg@bowt.ie>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 14+ messages in thread
From: Peter Geoghegan @ 2021-12-22 22:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Chris Lowder <clowder@hey.com>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Wed, Dec 22, 2021 at 2:13 PM Bruce Momjian <bruce@momjian.us> wrote:
> I think our _big_ missing information is a definition of HOT updates and
> that updating a column involved in an index disables HOT updates.
I *strongly* agree that that's the single most important piece of
information, by far. Though we could and should have several
paragraphs about it.
We never get around to explaining HOT at all, which is just bizarre,
considering its importance. Many very popular user-level talks have
mostly just talked about this subject (e.g., a talk from Grant
McAlister from a couple of years back comes to mind).
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-12-22 22:30 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-10 02:47 11.7. Indexes on Expressions PG Doc comments form <noreply@postgresql.org>
2020-04-10 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-16 16:57 11.7. Indexes on Expressions PG Doc comments form <noreply@postgresql.org>
2021-12-16 20:52 ` Bruce Momjian <bruce@momjian.us>
2021-12-17 09:41 ` Chris Lowder <clowder@hey.com>
2021-12-17 21:43 ` Bruce Momjian <bruce@momjian.us>
2021-12-22 21:40 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-22 21:45 ` Bruce Momjian <bruce@momjian.us>
2021-12-22 22:06 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-22 22:09 ` Bruce Momjian <bruce@momjian.us>
2021-12-22 22:13 ` Bruce Momjian <bruce@momjian.us>
2021-12-22 22:18 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-22 22:30 ` Peter Geoghegan <pg@bowt.ie>
2021-12-22 22:14 ` David G. Johnston <david.g.johnston@gmail.com>
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