pgsql: Ignore BRIN indexes when checking for HOT udpates

Started by Tomas Vondraabout 4 years ago4 messages
#1Tomas Vondra
tomas.vondra@postgresql.org

Ignore BRIN indexes when checking for HOT udpates

When determining whether an index update may be skipped by using HOT, we
can ignore attributes indexed only by BRIN indexes. There are no index
pointers to individual tuples in BRIN, and the page range summary will
be updated anyway as it relies on visibility info.

This also removes rd_indexattr list, and replaces it with rd_attrsvalid
flag. The list was not used anywhere, and a simple flag is sufficient.

Patch by Josef Simanek, various fixes and improvements by me.

Author: Josef Simanek
Reviewed-by: Tomas Vondra, Alvaro Herrera
Discussion: /messages/by-id/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/5753d4ee320b3f6fb2ff734667a1ce1d9d8615a1

Modified Files
--------------
doc/src/sgml/indexam.sgml | 11 +++
src/backend/access/brin/brin.c | 1 +
src/backend/access/gin/ginutil.c | 1 +
src/backend/access/gist/gist.c | 1 +
src/backend/access/hash/hash.c | 1 +
src/backend/access/heap/heapam.c | 2 +-
src/backend/access/nbtree/nbtree.c | 1 +
src/backend/access/spgist/spgutils.c | 1 +
src/backend/utils/cache/relcache.c | 50 ++++++++------
src/include/access/amapi.h | 2 +
src/include/utils/rel.h | 3 +-
src/include/utils/relcache.h | 4 +-
src/test/modules/dummy_index_am/dummy_index_am.c | 1 +
src/test/regress/expected/brin.out | 85 ++++++++++++++++++++++++
src/test/regress/sql/brin.sql | 63 ++++++++++++++++++
15 files changed, 202 insertions(+), 25 deletions(-)

#2Alvaro Herrera
alvherre@alvh.no-ip.org
In reply to: Tomas Vondra (#1)
1 attachment(s)
Re: pgsql: Ignore BRIN indexes when checking for HOT udpates

On 2021-Nov-30, Tomas Vondra wrote:

Ignore BRIN indexes when checking for HOT udpates

I was trying to use RelationGetIndexAttrBitmap for something and
realized that its header comment does not really explain things very
well. That was already the case before this commit, but it (this
commit) did add new possible values without mentioning them. I propose
the attached comment-only patch.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Al principio era UNIX, y UNIX habló y dijo: "Hello world\n".
No dijo "Hello New Jersey\n", ni "Hello USA\n".

Attachments:

0001-Document-RelationGetIndexAttrBitmap-better.patchtext/x-diff; charset=us-asciiDownload
From db4c89d15d121bf0e15c4160cb2f0770bc98195a Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: Wed, 12 Jul 2023 14:19:23 +0200
Subject: [PATCH] Document RelationGetIndexAttrBitmap better

Commit 19d8e2308bc5 changed the list of set-of-columns that can be
returned by RelationGetIndexAttrBitmap, but didn't update its
"documentation".  That was pretty hard to read already, so rewrite to
make it more comprehensible, adding the missing values while at it.

Backpatch to 16, like that commit.
---
 src/backend/utils/cache/relcache.c | 12 +++++++++---
 src/include/utils/relcache.h       |  3 +++
 2 files changed, 12 insertions(+), 3 deletions(-)

diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 8e28335915..8e08ca1c68 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -5150,9 +5150,15 @@ RelationGetIndexPredicate(Relation relation)
  * simple index keys, but attributes used in expressions and partial-index
  * predicates.)
  *
- * Depending on attrKind, a bitmap covering the attnums for all index columns,
- * for all potential foreign key columns, or for all columns in the configured
- * replica identity index is returned.
+ * Depending on attrKind, a bitmap covering attnums for certain columns is
+ * returned:
+ *	INDEX_ATTR_BITMAP_KEY			Columns in non-partial unique indexes not
+ *									in expressions (i.e., usable for FKs)
+ *	INDEX_ATTR_BITMAP_PRIMARY_KEY	Columns in the table's primary key
+ *	INDEX_ATTR_BITMAP_IDENTITY_KEY	Columns in the table's replica identity
+ *									index (empty if FULL)
+ *	INDEX_ATTR_BITMAP_HOT_BLOCKING	Columns that block updates from being HOT
+ *	INDEX_ATTR_BITMAP_SUMMARIZED	Columns included in summarizing indexes
  *
  * Attribute numbers are offset by FirstLowInvalidHeapAttributeNumber so that
  * we can include system attributes (e.g., OID) in the bitmap representation.
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index beeb28b83c..38524641f4 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -54,6 +54,9 @@ extern List *RelationGetIndexPredicate(Relation relation);
 extern Datum *RelationGetIndexRawAttOptions(Relation indexrel);
 extern bytea **RelationGetIndexAttOptions(Relation relation, bool copy);
 
+/*
+ * Which set of columns to return by RelationGetIndexAttrBitmap.
+ */
 typedef enum IndexAttrBitmapKind
 {
 	INDEX_ATTR_BITMAP_KEY,
-- 
2.39.2

#3Tomas Vondra
tomas.vondra@enterprisedb.com
In reply to: Alvaro Herrera (#2)
Re: pgsql: Ignore BRIN indexes when checking for HOT udpates

On 8/9/23 11:11, Alvaro Herrera wrote:

On 2021-Nov-30, Tomas Vondra wrote:

Ignore BRIN indexes when checking for HOT udpates

I was trying to use RelationGetIndexAttrBitmap for something and
realized that its header comment does not really explain things very
well. That was already the case before this commit, but it (this
commit) did add new possible values without mentioning them. I propose
the attached comment-only patch.

+1

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

#4Alvaro Herrera
alvherre@alvh.no-ip.org
In reply to: Tomas Vondra (#3)
Re: pgsql: Ignore BRIN indexes when checking for HOT udpates

On 2023-Aug-09, Tomas Vondra wrote:

On 8/9/23 11:11, Alvaro Herrera wrote:

I was trying to use RelationGetIndexAttrBitmap for something and
realized that its header comment does not really explain things very
well. That was already the case before this commit, but it (this
commit) did add new possible values without mentioning them. I propose
the attached comment-only patch.

+1

Thanks for looking! Pushed now.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/