Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5753d4e

Browse files
committed
Ignore BRIN indexes when checking for HOT udpates
When determining whether an index update may be skipped by using HOT, wecan ignore attributes indexed only by BRIN indexes. There are no indexpointers to individual tuples in BRIN, and the page range summary willbe updated anyway as it relies on visibility info.This also removes rd_indexattr list, and replaces it with rd_attrsvalidflag. The list was not used anywhere, and a simple flag is sufficient.Patch by Josef Simanek, various fixes and improvements by me.Author: Josef SimanekReviewed-by: Tomas Vondra, Alvaro HerreraDiscussion:https://postgr.es/m/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg%40mail.gmail.com
1 parent4c83e59 commit5753d4e

File tree

15 files changed

+202
-25
lines changed

15 files changed

+202
-25
lines changed

‎doc/src/sgml/indexam.sgml

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,8 @@ typedef struct IndexAmRoutine
126126
bool amcaninclude;
127127
/* does AM use maintenance_work_mem? */
128128
bool amusemaintenanceworkmem;
129+
/* does AM block HOT update? */
130+
bool amhotblocking;
129131
/* OR of parallel vacuum flags */
130132
uint8 amparallelvacuumoptions;
131133
/* type of data stored in index, or InvalidOid if variable */
@@ -246,6 +248,15 @@ typedef struct IndexAmRoutine
246248
null, independently of <structfield>amoptionalkey</structfield>.
247249
</para>
248250

251+
<para>
252+
The <structfield>amhotblocking</structfield> flag indicates whether the
253+
access method blocks <acronym>HOT</acronym> when an indexed attribute is
254+
updated. Access methods without pointers to individual tuples (like
255+
<acronym>BRIN</acronym>) may allow <acronym>HOT</acronym> even in this
256+
case. This does not apply to attributes referenced in index predicates,
257+
an update of such attribute always disables <acronym>HOT</acronym>.
258+
</para>
259+
249260
</sect1>
250261

251262
<sect1 id="index-functions">

‎src/backend/access/brin/brin.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,7 @@ brinhandler(PG_FUNCTION_ARGS)
108108
amroutine->amcanparallel= false;
109109
amroutine->amcaninclude= false;
110110
amroutine->amusemaintenanceworkmem= false;
111+
amroutine->amhotblocking= false;
111112
amroutine->amparallelvacuumoptions=
112113
VACUUM_OPTION_PARALLEL_CLEANUP;
113114
amroutine->amkeytype=InvalidOid;

‎src/backend/access/gin/ginutil.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@ ginhandler(PG_FUNCTION_ARGS)
5656
amroutine->amcanparallel= false;
5757
amroutine->amcaninclude= false;
5858
amroutine->amusemaintenanceworkmem= true;
59+
amroutine->amhotblocking= true;
5960
amroutine->amparallelvacuumoptions=
6061
VACUUM_OPTION_PARALLEL_BULKDEL |VACUUM_OPTION_PARALLEL_CLEANUP;
6162
amroutine->amkeytype=InvalidOid;

‎src/backend/access/gist/gist.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ gisthandler(PG_FUNCTION_ARGS)
7777
amroutine->amcanparallel= false;
7878
amroutine->amcaninclude= true;
7979
amroutine->amusemaintenanceworkmem= false;
80+
amroutine->amhotblocking= true;
8081
amroutine->amparallelvacuumoptions=
8182
VACUUM_OPTION_PARALLEL_BULKDEL |VACUUM_OPTION_PARALLEL_COND_CLEANUP;
8283
amroutine->amkeytype=InvalidOid;

‎src/backend/access/hash/hash.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,7 @@ hashhandler(PG_FUNCTION_ARGS)
7474
amroutine->amcanparallel= false;
7575
amroutine->amcaninclude= false;
7676
amroutine->amusemaintenanceworkmem= false;
77+
amroutine->amhotblocking= true;
7778
amroutine->amparallelvacuumoptions=
7879
VACUUM_OPTION_PARALLEL_BULKDEL;
7980
amroutine->amkeytype=INT4OID;

‎src/backend/access/heap/heapam.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3223,7 +3223,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
32233223
* Note that we get copies of each bitmap, so we need not worry about
32243224
* relcache flush happening midway through.
32253225
*/
3226-
hot_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_ALL);
3226+
hot_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_HOT_BLOCKING);
32273227
key_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_KEY);
32283228
id_attrs=RelationGetIndexAttrBitmap(relation,
32293229
INDEX_ATTR_BITMAP_IDENTITY_KEY);

‎src/backend/access/nbtree/nbtree.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,7 @@ bthandler(PG_FUNCTION_ARGS)
113113
amroutine->amcanparallel= true;
114114
amroutine->amcaninclude= true;
115115
amroutine->amusemaintenanceworkmem= false;
116+
amroutine->amhotblocking= true;
116117
amroutine->amparallelvacuumoptions=
117118
VACUUM_OPTION_PARALLEL_BULKDEL |VACUUM_OPTION_PARALLEL_COND_CLEANUP;
118119
amroutine->amkeytype=InvalidOid;

‎src/backend/access/spgist/spgutils.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@ spghandler(PG_FUNCTION_ARGS)
6262
amroutine->amcanparallel= false;
6363
amroutine->amcaninclude= true;
6464
amroutine->amusemaintenanceworkmem= false;
65+
amroutine->amhotblocking= true;
6566
amroutine->amparallelvacuumoptions=
6667
VACUUM_OPTION_PARALLEL_BULKDEL |VACUUM_OPTION_PARALLEL_COND_CLEANUP;
6768
amroutine->amkeytype=InvalidOid;

‎src/backend/utils/cache/relcache.c

Lines changed: 29 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -2428,10 +2428,10 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc)
24282428
list_free_deep(relation->rd_fkeylist);
24292429
list_free(relation->rd_indexlist);
24302430
list_free(relation->rd_statlist);
2431-
bms_free(relation->rd_indexattr);
24322431
bms_free(relation->rd_keyattr);
24332432
bms_free(relation->rd_pkattr);
24342433
bms_free(relation->rd_idattr);
2434+
bms_free(relation->rd_hotblockingattr);
24352435
if (relation->rd_pubactions)
24362436
pfree(relation->rd_pubactions);
24372437
if (relation->rd_options)
@@ -5105,10 +5105,10 @@ RelationGetIndexPredicate(Relation relation)
51055105
Bitmapset*
51065106
RelationGetIndexAttrBitmap(Relationrelation,IndexAttrBitmapKindattrKind)
51075107
{
5108-
Bitmapset*indexattrs;/* indexed columns */
51095108
Bitmapset*uindexattrs;/* columns in unique indexes */
51105109
Bitmapset*pkindexattrs;/* columns in the primary index */
51115110
Bitmapset*idindexattrs;/* columns in the replica identity */
5111+
Bitmapset*hotblockingattrs;/* columns with HOT blocking indexes */
51125112
List*indexoidlist;
51135113
List*newindexoidlist;
51145114
Oidrelpkindex;
@@ -5117,18 +5117,18 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
51175117
MemoryContextoldcxt;
51185118

51195119
/* Quick exit if we already computed the result. */
5120-
if (relation->rd_indexattr!=NULL)
5120+
if (relation->rd_attrsvalid)
51215121
{
51225122
switch (attrKind)
51235123
{
5124-
caseINDEX_ATTR_BITMAP_ALL:
5125-
returnbms_copy(relation->rd_indexattr);
51265124
caseINDEX_ATTR_BITMAP_KEY:
51275125
returnbms_copy(relation->rd_keyattr);
51285126
caseINDEX_ATTR_BITMAP_PRIMARY_KEY:
51295127
returnbms_copy(relation->rd_pkattr);
51305128
caseINDEX_ATTR_BITMAP_IDENTITY_KEY:
51315129
returnbms_copy(relation->rd_idattr);
5130+
caseINDEX_ATTR_BITMAP_HOT_BLOCKING:
5131+
returnbms_copy(relation->rd_hotblockingattr);
51325132
default:
51335133
elog(ERROR,"unknown attrKind %u",attrKind);
51345134
}
@@ -5159,7 +5159,7 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
51595159
relreplindex=relation->rd_replidindex;
51605160

51615161
/*
5162-
* For each index, add referenced attributes toindexattrs.
5162+
* For each index, add referenced attributes toappropriate bitmaps.
51635163
*
51645164
* Note: we consider all indexes returned by RelationGetIndexList, even if
51655165
* they are not indisready or indisvalid. This is important because an
@@ -5168,10 +5168,10 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
51685168
* CONCURRENTLY is far enough along that we should ignore the index, it
51695169
* won't be returned at all by RelationGetIndexList.
51705170
*/
5171-
indexattrs=NULL;
51725171
uindexattrs=NULL;
51735172
pkindexattrs=NULL;
51745173
idindexattrs=NULL;
5174+
hotblockingattrs=NULL;
51755175
foreach(l,indexoidlist)
51765176
{
51775177
OidindexOid=lfirst_oid(l);
@@ -5236,8 +5236,9 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
52365236
*/
52375237
if (attrnum!=0)
52385238
{
5239-
indexattrs=bms_add_member(indexattrs,
5240-
attrnum-FirstLowInvalidHeapAttributeNumber);
5239+
if (indexDesc->rd_indam->amhotblocking)
5240+
hotblockingattrs=bms_add_member(hotblockingattrs,
5241+
attrnum-FirstLowInvalidHeapAttributeNumber);
52415242

52425243
if (isKey&&i<indexDesc->rd_index->indnkeyatts)
52435244
uindexattrs=bms_add_member(uindexattrs,
@@ -5254,10 +5255,15 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
52545255
}
52555256

52565257
/* Collect all attributes used in expressions, too */
5257-
pull_varattnos(indexExpressions,1,&indexattrs);
5258+
if (indexDesc->rd_indam->amhotblocking)
5259+
pull_varattnos(indexExpressions,1,&hotblockingattrs);
52585260

5259-
/* Collect all attributes in the index predicate, too */
5260-
pull_varattnos(indexPredicate,1,&indexattrs);
5261+
/*
5262+
* Collect all attributes in the index predicate, too. We have to ignore
5263+
* amhotblocking flag, because the row might become indexable, in which
5264+
* case we have to add it to the index.
5265+
*/
5266+
pull_varattnos(indexPredicate,1,&hotblockingattrs);
52615267

52625268
index_close(indexDesc,AccessShareLock);
52635269
}
@@ -5285,46 +5291,47 @@ RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
52855291
bms_free(uindexattrs);
52865292
bms_free(pkindexattrs);
52875293
bms_free(idindexattrs);
5288-
bms_free(indexattrs);
5294+
bms_free(hotblockingattrs);
52895295

52905296
gotorestart;
52915297
}
52925298

52935299
/* Don't leak the old values of these bitmaps, if any */
5294-
bms_free(relation->rd_indexattr);
5295-
relation->rd_indexattr=NULL;
52965300
bms_free(relation->rd_keyattr);
52975301
relation->rd_keyattr=NULL;
52985302
bms_free(relation->rd_pkattr);
52995303
relation->rd_pkattr=NULL;
53005304
bms_free(relation->rd_idattr);
53015305
relation->rd_idattr=NULL;
5306+
bms_free(relation->rd_hotblockingattr);
5307+
relation->rd_hotblockingattr=NULL;
53025308

53035309
/*
53045310
* Now save copies of the bitmaps in the relcache entry. We intentionally
5305-
* setrd_indexattr last, because that'sthe one thatsignals validity of
5306-
*thevalues; if we run out of memory before making that copy, we won't
5311+
* setrd_attrsvalid last, because that'swhatsignals validity of the
5312+
* values; if we run out of memory before making that copy, we won't
53075313
* leave the relcache entry looking like the other ones are valid but
53085314
* empty.
53095315
*/
53105316
oldcxt=MemoryContextSwitchTo(CacheMemoryContext);
53115317
relation->rd_keyattr=bms_copy(uindexattrs);
53125318
relation->rd_pkattr=bms_copy(pkindexattrs);
53135319
relation->rd_idattr=bms_copy(idindexattrs);
5314-
relation->rd_indexattr=bms_copy(indexattrs);
5320+
relation->rd_hotblockingattr=bms_copy(hotblockingattrs);
5321+
relation->rd_attrsvalid= true;
53155322
MemoryContextSwitchTo(oldcxt);
53165323

53175324
/* We return our original working copy for caller to play with */
53185325
switch (attrKind)
53195326
{
5320-
caseINDEX_ATTR_BITMAP_ALL:
5321-
returnindexattrs;
53225327
caseINDEX_ATTR_BITMAP_KEY:
53235328
returnuindexattrs;
53245329
caseINDEX_ATTR_BITMAP_PRIMARY_KEY:
53255330
returnpkindexattrs;
53265331
caseINDEX_ATTR_BITMAP_IDENTITY_KEY:
53275332
returnidindexattrs;
5333+
caseINDEX_ATTR_BITMAP_HOT_BLOCKING:
5334+
returnhotblockingattrs;
53285335
default:
53295336
elog(ERROR,"unknown attrKind %u",attrKind);
53305337
returnNULL;
@@ -6180,10 +6187,11 @@ load_relcache_init_file(bool shared)
61806187
rel->rd_indexlist=NIL;
61816188
rel->rd_pkindex=InvalidOid;
61826189
rel->rd_replidindex=InvalidOid;
6183-
rel->rd_indexattr=NULL;
6190+
rel->rd_attrsvalid=false;
61846191
rel->rd_keyattr=NULL;
61856192
rel->rd_pkattr=NULL;
61866193
rel->rd_idattr=NULL;
6194+
rel->rd_hotblockingattr=NULL;
61876195
rel->rd_pubactions=NULL;
61886196
rel->rd_statvalid= false;
61896197
rel->rd_statlist=NIL;

‎src/include/access/amapi.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -244,6 +244,8 @@ typedef struct IndexAmRoutine
244244
boolamcaninclude;
245245
/* does AM use maintenance_work_mem? */
246246
boolamusemaintenanceworkmem;
247+
/* does AM block HOT update? */
248+
boolamhotblocking;
247249
/* OR of parallel vacuum flags. See vacuum.h for flags. */
248250
uint8amparallelvacuumoptions;
249251
/* type of data stored in index, or InvalidOid if variable */

‎src/include/utils/rel.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -155,10 +155,11 @@ typedef struct RelationData
155155
List*rd_statlist;/* list of OIDs of extended stats */
156156

157157
/* data managed by RelationGetIndexAttrBitmap: */
158-
Bitmapset*rd_indexattr;/*identifies columns used in indexes */
158+
boolrd_attrsvalid;/*are bitmaps of attrs valid? */
159159
Bitmapset*rd_keyattr;/* cols that can be ref'd by foreign keys */
160160
Bitmapset*rd_pkattr;/* cols included in primary key */
161161
Bitmapset*rd_idattr;/* included in replica identity index */
162+
Bitmapset*rd_hotblockingattr;/* cols blocking HOT update */
162163

163164
PublicationActions*rd_pubactions;/* publication actions */
164165

‎src/include/utils/relcache.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -55,10 +55,10 @@ extern bytea **RelationGetIndexAttOptions(Relation relation, bool copy);
5555

5656
typedefenumIndexAttrBitmapKind
5757
{
58-
INDEX_ATTR_BITMAP_ALL,
5958
INDEX_ATTR_BITMAP_KEY,
6059
INDEX_ATTR_BITMAP_PRIMARY_KEY,
61-
INDEX_ATTR_BITMAP_IDENTITY_KEY
60+
INDEX_ATTR_BITMAP_IDENTITY_KEY,
61+
INDEX_ATTR_BITMAP_HOT_BLOCKING
6262
}IndexAttrBitmapKind;
6363

6464
externBitmapset*RelationGetIndexAttrBitmap(Relationrelation,

‎src/test/modules/dummy_index_am/dummy_index_am.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -298,6 +298,7 @@ dihandler(PG_FUNCTION_ARGS)
298298
amroutine->amcanparallel= false;
299299
amroutine->amcaninclude= false;
300300
amroutine->amusemaintenanceworkmem= false;
301+
amroutine->amhotblocking= true;
301302
amroutine->amparallelvacuumoptions=VACUUM_OPTION_NO_PARALLEL;
302303
amroutine->amkeytype=InvalidOid;
303304

‎src/test/regress/expected/brin.out

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -567,3 +567,88 @@ SELECT * FROM brintest_3 WHERE b < '0';
567567

568568
DROP TABLE brintest_3;
569569
RESET enable_seqscan;
570+
-- test BRIN index doesn't block HOT update
571+
CREATE TABLE brin_hot (
572+
id integer PRIMARY KEY,
573+
val integer NOT NULL
574+
) WITH (autovacuum_enabled = off, fillfactor = 70);
575+
INSERT INTO brin_hot SELECT *, 0 FROM generate_series(1, 235);
576+
CREATE INDEX val_brin ON brin_hot using brin(val);
577+
CREATE FUNCTION wait_for_hot_stats() RETURNS void AS $$
578+
DECLARE
579+
start_time timestamptz := clock_timestamp();
580+
updated bool;
581+
BEGIN
582+
-- we don't want to wait forever; loop will exit after 30 seconds
583+
FOR i IN 1 .. 300 LOOP
584+
SELECT (pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid) > 0) INTO updated;
585+
EXIT WHEN updated;
586+
587+
-- wait a little
588+
PERFORM pg_sleep_for('100 milliseconds');
589+
-- reset stats snapshot so we can test again
590+
PERFORM pg_stat_clear_snapshot();
591+
END LOOP;
592+
-- report time waited in postmaster log (where it won't change test output)
593+
RAISE log 'wait_for_hot_stats delayed % seconds',
594+
EXTRACT(epoch FROM clock_timestamp() - start_time);
595+
END
596+
$$ LANGUAGE plpgsql;
597+
UPDATE brin_hot SET val = -3 WHERE id = 42;
598+
-- We can't just call wait_for_hot_stats() at this point, because we only
599+
-- transmit stats when the session goes idle, and we probably didn't
600+
-- transmit the last couple of counts yet thanks to the rate-limiting logic
601+
-- in pgstat_report_stat(). But instead of waiting for the rate limiter's
602+
-- timeout to elapse, let's just start a new session. The old one will
603+
-- then send its stats before dying.
604+
\c -
605+
SELECT wait_for_hot_stats();
606+
wait_for_hot_stats
607+
--------------------
608+
609+
(1 row)
610+
611+
SELECT pg_stat_get_tuples_hot_updated('brin_hot'::regclass::oid);
612+
pg_stat_get_tuples_hot_updated
613+
--------------------------------
614+
1
615+
(1 row)
616+
617+
DROP TABLE brin_hot;
618+
DROP FUNCTION wait_for_hot_stats();
619+
-- Test handling of index predicates - updating attributes in precicates
620+
-- should block HOT even for BRIN. We update a row that was not indexed
621+
-- due to the index predicate, and becomes indexable.
622+
CREATE TABLE brin_hot_2 (a int, b int);
623+
INSERT INTO brin_hot_2 VALUES (1, 100);
624+
CREATE INDEX ON brin_hot_2 USING brin (b) WHERE a = 2;
625+
UPDATE brin_hot_2 SET a = 2;
626+
EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100;
627+
QUERY PLAN
628+
-----------------------------------
629+
Seq Scan on brin_hot_2
630+
Filter: ((a = 2) AND (b = 100))
631+
(2 rows)
632+
633+
SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100;
634+
count
635+
-------
636+
1
637+
(1 row)
638+
639+
SET enable_seqscan = off;
640+
EXPLAIN (COSTS OFF) SELECT * FROM brin_hot_2 WHERE a = 2 AND b = 100;
641+
QUERY PLAN
642+
---------------------------------------------
643+
Bitmap Heap Scan on brin_hot_2
644+
Recheck Cond: ((b = 100) AND (a = 2))
645+
-> Bitmap Index Scan on brin_hot_2_b_idx
646+
Index Cond: (b = 100)
647+
(4 rows)
648+
649+
SELECT COUNT(*) FROM brin_hot_2 WHERE a = 2 AND b = 100;
650+
count
651+
-------
652+
1
653+
(1 row)
654+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp