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

Commitc203d6c

Browse files
Allow HOT updates for some expression indexes
If the value of an index expression is unchanged after UPDATE,allow HOT updates where previously we disallowed them, givinga significant performance boost in those cases.Particularly useful for indexes such as JSON->>field where theJSON value changes but the indexed value does not.Submitted as "surjective indexes" patch, now enabled by useof new "recheck_on_update" parameter.Author: Konstantin KnizhnikReviewer: Simon Riggs, with much wordsmithing and some cleanup
1 parent1944cdc commitc203d6c

File tree

13 files changed

+395
-22
lines changed

13 files changed

+395
-22
lines changed

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 35 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -309,8 +309,41 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
309309
<para>
310310
The optional <literal>WITH</literal> clause specifies <firstterm>storage
311311
parameters</firstterm> for the index. Each index method has its own set of allowed
312-
storage parameters. The B-tree, hash, GiST and SP-GiST index methods all
313-
accept this parameter:
312+
storage parameters. All indexes accept the following parameter:
313+
</para>
314+
315+
<variablelist>
316+
<varlistentry>
317+
<term><literal>recheck_on_update</literal></term>
318+
<listitem>
319+
<para>
320+
Specifies whether to recheck a functional index value to see whether
321+
we can use a HOT update or not. The default value is on for functional
322+
indexes with an total expression cost less than 1000, otherwise off.
323+
You might decide to turn this off if you knew that a function used in
324+
an index is unlikely to return the same value when one of the input
325+
columns is updated and so the recheck is not worth the additional cost
326+
of executing the function.
327+
</para>
328+
329+
<para>
330+
Functional indexes are used frequently for the case where the function
331+
returns a subset of the argument. Examples of this would be accessing
332+
part of a string with <literal>SUBSTR()</literal> or accessing a single
333+
field in a JSON document using an expression such as
334+
<literal>(bookinfo-&gt;&gt;'isbn')</literal>. In this example, the JSON
335+
document might be updated frequently, yet it is uncommon for the ISBN
336+
field for a book to change so we would keep the parameter set to on
337+
for that index. A more frequently changing field might have an index
338+
with this parameter turned off, while very frequently changing fields
339+
might be better to avoid indexing at all under high load.
340+
</para>
341+
</listitem>
342+
</varlistentry>
343+
</variablelist>
344+
345+
<para>
346+
The B-tree, hash, GiST and SP-GiST index methods all accept this parameter:
314347
</para>
315348

316349
<variablelist>

‎src/backend/access/common/reloptions.c

Lines changed: 44 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,15 @@ static relopt_bool boolRelOpts[] =
129129
},
130130
true
131131
},
132+
{
133+
{
134+
"recheck_on_update",
135+
"Recheck functional index expression for changed value after update",
136+
RELOPT_KIND_INDEX,
137+
ShareUpdateExclusiveLock/* since only applies to later UPDATEs */
138+
},
139+
true
140+
},
132141
{
133142
{
134143
"security_barrier",
@@ -1310,7 +1319,7 @@ fillRelOptions(void *rdopts, Size basesize,
13101319
break;
13111320
}
13121321
}
1313-
if (validate&& !found)
1322+
if (validate&& !found&&options[i].gen->kinds!=RELOPT_KIND_INDEX)
13141323
elog(ERROR,"reloption \"%s\" not found in parse table",
13151324
options[i].gen->name);
13161325
}
@@ -1466,6 +1475,40 @@ index_reloptions(amoptions_function amoptions, Datum reloptions, bool validate)
14661475
returnamoptions(reloptions,validate);
14671476
}
14681477

1478+
/*
1479+
* Parse generic options for all indexes.
1480+
*
1481+
*reloptionsoptions as text[] datum
1482+
*validateerror flag
1483+
*/
1484+
bytea*
1485+
index_generic_reloptions(Datumreloptions,boolvalidate)
1486+
{
1487+
intnumoptions;
1488+
GenericIndexOpts*idxopts;
1489+
relopt_value*options;
1490+
staticconstrelopt_parse_elttab[]= {
1491+
{"recheck_on_update",RELOPT_TYPE_BOOL, offsetof(GenericIndexOpts,recheck_on_update)}
1492+
};
1493+
1494+
options=parseRelOptions(reloptions,validate,
1495+
RELOPT_KIND_INDEX,
1496+
&numoptions);
1497+
1498+
/* if none set, we're done */
1499+
if (numoptions==0)
1500+
returnNULL;
1501+
1502+
idxopts=allocateReloptStruct(sizeof(GenericIndexOpts),options,numoptions);
1503+
1504+
fillRelOptions((void*)idxopts,sizeof(GenericIndexOpts),options,numoptions,
1505+
validate,tab,lengthof(tab));
1506+
1507+
pfree(options);
1508+
1509+
return (bytea*)idxopts;
1510+
}
1511+
14691512
/*
14701513
* Option parser for attribute reloptions
14711514
*/

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

Lines changed: 98 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
#include"access/xlogutils.h"
5757
#include"catalog/catalog.h"
5858
#include"catalog/namespace.h"
59+
#include"catalog/index.h"
5960
#include"miscadmin.h"
6061
#include"pgstat.h"
6162
#include"port/atomics.h"
@@ -74,7 +75,9 @@
7475
#include"utils/snapmgr.h"
7576
#include"utils/syscache.h"
7677
#include"utils/tqual.h"
77-
78+
#include"utils/memutils.h"
79+
#include"nodes/execnodes.h"
80+
#include"executor/executor.h"
7881

7982
/* GUC variable */
8083
boolsynchronize_seqscans= true;
@@ -126,6 +129,7 @@ static bool ConditionalMultiXactIdWait(MultiXactId multi, MultiXactStatus status
126129
staticXLogRecPtrlog_heap_new_cid(Relationrelation,HeapTupletup);
127130
staticHeapTupleExtractReplicaIdentity(Relationrel,HeapTupletup,boolkey_modified,
128131
bool*copy);
132+
staticboolProjIndexIsUnchanged(Relationrelation,HeapTupleoldtup,HeapTuplenewtup);
129133

130134

131135
/*
@@ -3508,6 +3512,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
35083512
HTSU_Resultresult;
35093513
TransactionIdxid=GetCurrentTransactionId();
35103514
Bitmapset*hot_attrs;
3515+
Bitmapset*proj_idx_attrs;
35113516
Bitmapset*key_attrs;
35123517
Bitmapset*id_attrs;
35133518
Bitmapset*interesting_attrs;
@@ -3571,12 +3576,11 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
35713576
* Note that we get copies of each bitmap, so we need not worry about
35723577
* relcache flush happening midway through.
35733578
*/
3574-
hot_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_ALL);
3579+
hot_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_HOT);
3580+
proj_idx_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_PROJ);
35753581
key_attrs=RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_KEY);
35763582
id_attrs=RelationGetIndexAttrBitmap(relation,
35773583
INDEX_ATTR_BITMAP_IDENTITY_KEY);
3578-
3579-
35803584
block=ItemPointerGetBlockNumber(otid);
35813585
buffer=ReadBuffer(relation,block);
35823586
page=BufferGetPage(buffer);
@@ -3596,6 +3600,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
35963600
if (!PageIsFull(page))
35973601
{
35983602
interesting_attrs=bms_add_members(interesting_attrs,hot_attrs);
3603+
interesting_attrs=bms_add_members(interesting_attrs,proj_idx_attrs);
35993604
hot_attrs_checked= true;
36003605
}
36013606
interesting_attrs=bms_add_members(interesting_attrs,key_attrs);
@@ -3894,6 +3899,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
38943899
if (vmbuffer!=InvalidBuffer)
38953900
ReleaseBuffer(vmbuffer);
38963901
bms_free(hot_attrs);
3902+
bms_free(proj_idx_attrs);
38973903
bms_free(key_attrs);
38983904
bms_free(id_attrs);
38993905
bms_free(modified_attrs);
@@ -4201,11 +4207,18 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
42014207
/*
42024208
* Since the new tuple is going into the same page, we might be able
42034209
* to do a HOT update. Check if any of the index columns have been
4204-
* changed. If the page was already full, we may have skipped checking
4205-
* for index columns. If so, HOT update is possible.
4210+
* changed, or if we have projection functional indexes, check whether
4211+
* the old and the new values are the same. If the page was already
4212+
* full, we may have skipped checking for index columns. If so, HOT
4213+
* update is possible.
42064214
*/
4207-
if (hot_attrs_checked&& !bms_overlap(modified_attrs,hot_attrs))
4215+
if (hot_attrs_checked
4216+
&& !bms_overlap(modified_attrs,hot_attrs)
4217+
&& (!bms_overlap(modified_attrs,proj_idx_attrs)
4218+
||ProjIndexIsUnchanged(relation,&oldtup,newtup)))
4219+
{
42084220
use_hot_update= true;
4221+
}
42094222
}
42104223
else
42114224
{
@@ -4367,6 +4380,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
43674380
heap_freetuple(old_key_tuple);
43684381

43694382
bms_free(hot_attrs);
4383+
bms_free(proj_idx_attrs);
43704384
bms_free(key_attrs);
43714385
bms_free(id_attrs);
43724386
bms_free(modified_attrs);
@@ -4453,6 +4467,83 @@ heap_tuple_attr_equals(TupleDesc tupdesc, int attrnum,
44534467
}
44544468
}
44554469

4470+
/*
4471+
* Check whether the value is unchanged after update of a projection
4472+
* functional index. Compare the new and old values of the indexed
4473+
* expression to see if we are able to use a HOT update or not.
4474+
*/
4475+
staticboolProjIndexIsUnchanged(Relationrelation,HeapTupleoldtup,HeapTuplenewtup)
4476+
{
4477+
ListCell*l;
4478+
List*indexoidlist=RelationGetIndexList(relation);
4479+
EState*estate=CreateExecutorState();
4480+
ExprContext*econtext=GetPerTupleExprContext(estate);
4481+
TupleTableSlot*slot=MakeSingleTupleTableSlot(RelationGetDescr(relation));
4482+
boolequals= true;
4483+
Datumold_values[INDEX_MAX_KEYS];
4484+
boolold_isnull[INDEX_MAX_KEYS];
4485+
Datumnew_values[INDEX_MAX_KEYS];
4486+
boolnew_isnull[INDEX_MAX_KEYS];
4487+
intindexno=0;
4488+
econtext->ecxt_scantuple=slot;
4489+
4490+
foreach(l,indexoidlist)
4491+
{
4492+
if (bms_is_member(indexno,relation->rd_projidx))
4493+
{
4494+
OidindexOid=lfirst_oid(l);
4495+
RelationindexDesc=index_open(indexOid,AccessShareLock);
4496+
IndexInfo*indexInfo=BuildIndexInfo(indexDesc);
4497+
inti;
4498+
4499+
ResetExprContext(econtext);
4500+
ExecStoreTuple(oldtup,slot,InvalidBuffer, false);
4501+
FormIndexDatum(indexInfo,
4502+
slot,
4503+
estate,
4504+
old_values,
4505+
old_isnull);
4506+
4507+
ExecStoreTuple(newtup,slot,InvalidBuffer, false);
4508+
FormIndexDatum(indexInfo,
4509+
slot,
4510+
estate,
4511+
new_values,
4512+
new_isnull);
4513+
4514+
for (i=0;i<indexInfo->ii_NumIndexAttrs;i++)
4515+
{
4516+
if (old_isnull[i]!=new_isnull[i])
4517+
{
4518+
equals= false;
4519+
break;
4520+
}
4521+
elseif (!old_isnull[i])
4522+
{
4523+
Form_pg_attributeatt=TupleDescAttr(RelationGetDescr(indexDesc),i);
4524+
if (!datumIsEqual(old_values[i],new_values[i],att->attbyval,att->attlen))
4525+
{
4526+
equals= false;
4527+
break;
4528+
}
4529+
}
4530+
}
4531+
index_close(indexDesc,AccessShareLock);
4532+
4533+
if (!equals)
4534+
{
4535+
break;
4536+
}
4537+
}
4538+
indexno+=1;
4539+
}
4540+
ExecDropSingleTupleTableSlot(slot);
4541+
FreeExecutorState(estate);
4542+
4543+
returnequals;
4544+
}
4545+
4546+
44564547
/*
44574548
* Check which columns are being updated.
44584549
*

‎src/backend/catalog/index.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
#include"access/amapi.h"
2727
#include"access/multixact.h"
2828
#include"access/relscan.h"
29+
#include"access/reloptions.h"
2930
#include"access/sysattr.h"
3031
#include"access/transam.h"
3132
#include"access/visibilitymap.h"
@@ -3863,7 +3864,7 @@ reindex_relation(Oid relid, int flags, int options)
38633864

38643865
/* Ensure rd_indexattr is valid; see comments for RelationSetIndexList */
38653866
if (is_pg_class)
3866-
(void)RelationGetIndexAttrBitmap(rel,INDEX_ATTR_BITMAP_ALL);
3867+
(void)RelationGetIndexAttrBitmap(rel,INDEX_ATTR_BITMAP_HOT);
38673868

38683869
PG_TRY();
38693870
{

0 commit comments

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