From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Michael Paquier <michael.paquier@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Christoph Berg <myon@debian.org>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Surjective functional indexes
Date: Mon, 4 Dec 2017 18:35:31 +0300
Message-ID: <02236ec2-8d17-9845-07fe-e36f7a5339e4@postgrespro.ru> (raw)
In-Reply-To: <CAB7nPqQJP+dwpN1fq7nxo5VJ6FE5U-5_kQM4HzRSm+OTTCKjOA@mail.gmail.com>
References: <592BBD90.3060104@postgrespro.ru>
<CAEepm=3B8HuoX9mR0qxedm1LPKAh8C_299zkL6+DD+y28KCsXw@mail.gmail.com>
<CANP8+jK9118ANxFY1Nj_tya-yTYX14ALrnOgMrO2qxiUYhyU_w@mail.gmail.com>
<f530ede0-1bf6-879c-c362-34325514f692@postgrespro.ru>
<20170913075144.auntuti3usqp2jl5@msg.df7cb.de>
<2393c4b3-2ec4-dc68-4ea9-670597b561fe@postgrespro.ru>
<20170913101459.bj6f4wiplvyxup5n@msg.df7cb.de>
<29009e90-fd29-b67b-11b9-cb737d03635b@postgrespro.ru>
<CANP8+jJTADnufZjZ9gMqkaV1mgn_6VHmLJj7Gg3YepOvujmKWg@mail.gmail.com>
<6776afb5-2ebd-ea5a-a6f8-ef0898c9f66d@postgrespro.ru>
<CANP8+jK+YCqfAhK-V0Sct0gPr3stkWS9EuM05VobQ_ow04KRww@mail.gmail.com>
<bd2b12b9-ed2a-75d3-c501-128f78831a61@postgrespro.ru>
<CANP8+jJHNuBHGPzmvYi5gbTB3Ofx5nufZo5HC6N89OwptPMucg@mail.gmail.com>
<b2d91183-289d-f6e4-1366-1864a3503a6e@postgrespro.ru>
<CANP8+jLaJVGow1J4iJrEQwZdWZKxynNwXtr1b0kd=1vRbKz3Gg@mail.gmail.com>
<CAB7nPqQJP+dwpN1fq7nxo5VJ6FE5U-5_kQM4HzRSm+OTTCKjOA@mail.gmail.com>
On 30.11.2017 05:02, Michael Paquier wrote:
> On Wed, Sep 27, 2017 at 4:07 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On 15 September 2017 at 16:34, Konstantin Knizhnik
>> <k.knizhnik@postgrespro.ru> wrote:
>>
>>> Attached please find yet another version of the patch.
>> Thanks. I'm reviewing it.
> Two months later, this patch is still waiting for a review (you are
> listed as well as a reviewer of this patch). The documentation of the
> patch has conflicts, please provide a rebased version. I am moving
> this patch to next CF with waiting on author as status.
Attached please find new patch with resolved documentation conflict.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:projection-autotune4.patch (text/x-patch, 30.9K) downloaddiff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex 0255375..7ec6a0d 100644--- a/doc/src/sgml/ref/create_index.sgml+++ b/doc/src/sgml/ref/create_index.sgml@@ -294,8 +294,33 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
<para>
The optional <literal>WITH</literal> clause specifies <firstterm>storage
parameters</firstterm> for the index. Each index method has its own set of allowed
- storage parameters. The B-tree, hash, GiST and SP-GiST index methods all- accept this parameter:+ storage parameters. All indexes accept the following parameter:+ </para>++ <variablelist>+ <varlistentry>+ <term><literal>projection</></term>+ <listitem>+ <para>+ Functional index is based on on projection function: function which extract subset of its argument.+ In mathematic such functions are called non-injective. For injective function if any attribute used in the indexed+ expression is changed, then value of index expression is also changed. So to check that index is affected by the+ update, it is enough to check the set of changed fields. By default this parameters is assigned true value and function is considered+ as non-injective.+ In this case change of any of indexed key doesn't mean that value of the function is changed. For example, for+ the expression expression<literal>(bookinfo->>'isbn')</literal> defined+ for column of JSON type is changed only when ISBN is changed, which rarely happen. The same is true for most+ functional indexes. For non-injective functions, Postgres compares values of indexed expression for old and updated tuple and updates+ index only when function results are different. It allows to eliminate index update and use HOT update.+ But there are extra evaluations of the functions. So if function is expensive or probability that change of indexed column will not effect+ the function value is small, then marking index as projection may increase update speed.+ </para>+ </listitem>+ </varlistentry>+ </variablelist>++ <para>+ The B-tree, hash, GiST and SP-GiST index methods all accept this parameter:
</para>
<variablelist>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.cindex aa9c0f1..0dae5f1 100644--- a/src/backend/access/common/reloptions.c+++ b/src/backend/access/common/reloptions.c@@ -131,6 +131,15 @@ static relopt_bool boolRelOpts[] =
},
{
{
+ "projection",+ "Evaluate functional index expression on update to check if its values is changed",+ RELOPT_KIND_INDEX,+ AccessExclusiveLock+ },+ true+ },+ {+ {
"security_barrier",
"View acts as a row security barrier",
RELOPT_KIND_VIEW,
@@ -1311,7 +1320,7 @@ fillRelOptions(void *rdopts, Size basesize,
break;
}
}
- if (validate && !found)+ if (validate && !found && options[i].gen->kinds != RELOPT_KIND_INDEX)
elog(ERROR, "reloption \"%s\" not found in parse table",
options[i].gen->name);
}
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.cindex 3acef27..7f356e7 100644--- a/src/backend/access/heap/heapam.c+++ b/src/backend/access/heap/heapam.c@@ -56,6 +56,7 @@
#include "access/xlogutils.h"
#include "catalog/catalog.h"
#include "catalog/namespace.h"
+#include "catalog/index.h"
#include "miscadmin.h"
#include "pgstat.h"
#include "port/atomics.h"
@@ -74,7 +75,9 @@
#include "utils/snapmgr.h"
#include "utils/syscache.h"
#include "utils/tqual.h"
-+#include "utils/memutils.h"+#include "nodes/execnodes.h"+#include "executor/executor.h"
/* GUC variable */
bool synchronize_seqscans = true;
@@ -126,6 +129,7 @@ static bool ConditionalMultiXactIdWait(MultiXactId multi, MultiXactStatus status
static XLogRecPtr log_heap_new_cid(Relation relation, HeapTuple tup);
static HeapTuple ExtractReplicaIdentity(Relation rel, HeapTuple tup, bool key_modified,
bool *copy);
+static bool ProjectionIsNotChanged(Relation relation, HeapTuple oldtup, HeapTuple newtup);
/*
@@ -3485,6 +3489,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
HTSU_Result result;
TransactionId xid = GetCurrentTransactionId();
Bitmapset *hot_attrs;
+ Bitmapset *warm_attrs;
Bitmapset *key_attrs;
Bitmapset *id_attrs;
Bitmapset *interesting_attrs;
@@ -3548,12 +3553,11 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
* Note that we get copies of each bitmap, so we need not worry about
* relcache flush happening midway through.
*/
- hot_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_ALL);+ hot_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_HOT);+ warm_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_WARM);
key_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_KEY);
id_attrs = RelationGetIndexAttrBitmap(relation,
INDEX_ATTR_BITMAP_IDENTITY_KEY);
--
block = ItemPointerGetBlockNumber(otid);
buffer = ReadBuffer(relation, block);
page = BufferGetPage(buffer);
@@ -3573,6 +3577,7 @@ heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
if (!PageIsFull(page))
{
interesting_attrs = bms_add_members(interesting_attrs, hot_attrs);
+ interesting_attrs = bms_add_members(interesting_attrs, warm_attrs);
hot_attrs_checked = true;
}
interesting_attrs = bms_add_members(interesting_attrs, key_attrs);
@@ -3810,7 +3815,7 @@ l2:
* preserve it as locker.
*/
checked_lockers = true;
- locker_remains = true;+ locker_remains = true;
can_continue = true;
}
else
@@ -3871,6 +3876,7 @@ l2:
if (vmbuffer != InvalidBuffer)
ReleaseBuffer(vmbuffer);
bms_free(hot_attrs);
+ bms_free(warm_attrs);
bms_free(key_attrs);
bms_free(id_attrs);
bms_free(modified_attrs);
@@ -4181,8 +4187,13 @@ l2:
* changed. If the page was already full, we may have skipped checking
* for index columns. If so, HOT update is possible.
*/
- if (hot_attrs_checked && !bms_overlap(modified_attrs, hot_attrs))+ if (hot_attrs_checked+ && !bms_overlap(modified_attrs, hot_attrs)+ && (!bms_overlap(modified_attrs, warm_attrs)+ || ProjectionIsNotChanged(relation, &oldtup, newtup)))+ {
use_hot_update = true;
+ }
}
else
{
@@ -4219,6 +4230,7 @@ l2:
if (use_hot_update)
{
+ elog(DEBUG1, "Use hot update");
/* Mark the old tuple as HOT-updated */
HeapTupleSetHotUpdated(&oldtup);
/* And mark the new tuple as heap-only */
@@ -4344,6 +4356,7 @@ l2:
heap_freetuple(old_key_tuple);
bms_free(hot_attrs);
+ bms_free(warm_attrs);
bms_free(key_attrs);
bms_free(id_attrs);
bms_free(modified_attrs);
@@ -4431,6 +4444,93 @@ heap_tuple_attr_equals(TupleDesc tupdesc, int attrnum,
}
/*
+ * For functional projection index compare new and old values of indexed expression.+ * This function is used instead of comparison of modified attributes sets for non-injective functions.+ */+static bool ProjectionIsNotChanged(Relation relation, HeapTuple oldtup, HeapTuple newtup)+{+ ListCell *l;+ List *indexoidlist = RelationGetIndexList(relation);+ EState *estate = CreateExecutorState();+ ExprContext *econtext = GetPerTupleExprContext(estate);+ TupleTableSlot *slot = MakeSingleTupleTableSlot(RelationGetDescr(relation));+ bool equals = true;+ Datum old_values[INDEX_MAX_KEYS];+ bool old_isnull[INDEX_MAX_KEYS];+ Datum new_values[INDEX_MAX_KEYS];+ bool new_isnull[INDEX_MAX_KEYS];+ int indexno = 0;+ econtext->ecxt_scantuple = slot;++ foreach(l, indexoidlist)+ {+ if (bms_is_member(indexno, relation->rd_projidx))+ {+ Oid indexOid = lfirst_oid(l);+ Relation indexDesc = index_open(indexOid, AccessShareLock);+ IndexInfo *indexInfo = BuildIndexInfo(indexDesc);+ int i;++ ResetExprContext(econtext);+ ExecStoreTuple(oldtup, slot, InvalidBuffer, false);+ FormIndexDatum(indexInfo,+ slot,+ estate,+ old_values,+ old_isnull);++ ExecStoreTuple(newtup, slot, InvalidBuffer, false);+ FormIndexDatum(indexInfo,+ slot,+ estate,+ new_values,+ new_isnull);++ for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)+ {+ if (old_isnull[i] != new_isnull[i])+ {+ equals = false;+ break;+ }+ else if (!old_isnull[i])+ {+ Form_pg_attribute att = TupleDescAttr(RelationGetDescr(indexDesc), i);+ if (!datumIsEqual(old_values[i], new_values[i], att->attbyval, att->attlen))+ {+ equals = false;+ break;+ }+ }+ }+ index_close(indexDesc, AccessShareLock);++ if (!equals)+ {+ break;+ }+ }+ indexno += 1;+ }+ ExecDropSingleTupleTableSlot(slot);+ FreeExecutorState(estate);++ if (relation->pgstat_info)+ {+ if (equals)+ {+ relation->pgstat_info->t_counts.t_hot_update_hits += 1;+ }+ else+ {+ relation->pgstat_info->t_counts.t_hot_update_misses += 1;+ }+ }+ return equals;+}+++/*
* Check which columns are being updated.
*
* Given an updated tuple, determine (and return into the output bitmapset),
@@ -4455,7 +4555,6 @@ HeapDetermineModifiedColumns(Relation relation, Bitmapset *interesting_cols,
modified = bms_add_member(modified,
attnum - FirstLowInvalidHeapAttributeNumber);
}
-
return modified;
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 0125c18..aa470d3 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -26,6 +26,7 @@
#include "access/amapi.h"
#include "access/multixact.h"
#include "access/relscan.h"
+#include "access/reloptions.h"
#include "access/sysattr.h"
#include "access/transam.h"
#include "access/visibilitymap.h"
@@ -3565,7 +3566,7 @@ reindex_relation(Oid relid, int flags, int options)
/* Ensure rd_indexattr is valid; see comments for RelationSetIndexList */
if (is_pg_class)
- (void) RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_ALL);+ (void) RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_HOT);
PG_TRY();
{
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.cindex 5c256ff..b26fd49 100644--- a/src/backend/postmaster/pgstat.c+++ b/src/backend/postmaster/pgstat.c@@ -4582,6 +4582,8 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->tuples_updated = 0;
result->tuples_deleted = 0;
result->tuples_hot_updated = 0;
+ result->hot_update_hits = 0;+ result->hot_update_misses = 0;
result->n_live_tuples = 0;
result->n_dead_tuples = 0;
result->changes_since_analyze = 0;
@@ -5712,6 +5714,8 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
tabentry->tuples_updated = tabmsg->t_counts.t_tuples_updated;
tabentry->tuples_deleted = tabmsg->t_counts.t_tuples_deleted;
tabentry->tuples_hot_updated = tabmsg->t_counts.t_tuples_hot_updated;
+ tabentry->hot_update_hits = tabmsg->t_counts.t_hot_update_hits;+ tabentry->hot_update_misses = tabmsg->t_counts.t_hot_update_misses;
tabentry->n_live_tuples = tabmsg->t_counts.t_delta_live_tuples;
tabentry->n_dead_tuples = tabmsg->t_counts.t_delta_dead_tuples;
tabentry->changes_since_analyze = tabmsg->t_counts.t_changed_tuples;
@@ -5739,6 +5743,8 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
tabentry->tuples_updated += tabmsg->t_counts.t_tuples_updated;
tabentry->tuples_deleted += tabmsg->t_counts.t_tuples_deleted;
tabentry->tuples_hot_updated += tabmsg->t_counts.t_tuples_hot_updated;
+ tabentry->hot_update_hits += tabmsg->t_counts.t_hot_update_hits;+ tabentry->hot_update_misses += tabmsg->t_counts.t_hot_update_misses;
/* If table was truncated, first reset the live/dead counters */
if (tabmsg->t_counts.t_truncated)
{
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.cindex 1908420..15c02b9 100644--- a/src/backend/utils/cache/relcache.c+++ b/src/backend/utils/cache/relcache.c@@ -69,8 +69,10 @@
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
+#include "optimizer/cost.h"
#include "optimizer/prep.h"
#include "optimizer/var.h"
+#include "pgstat.h"
#include "rewrite/rewriteDefine.h"
#include "rewrite/rowsecurity.h"
#include "storage/lmgr.h"
@@ -2349,10 +2351,12 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc)
FreeTriggerDesc(relation->trigdesc);
list_free_deep(relation->rd_fkeylist);
list_free(relation->rd_indexlist);
- bms_free(relation->rd_indexattr);+ bms_free(relation->rd_hotattr);+ bms_free(relation->rd_warmattr);
bms_free(relation->rd_keyattr);
bms_free(relation->rd_pkattr);
bms_free(relation->rd_idattr);
+ bms_free(relation->rd_projidx);
if (relation->rd_pubactions)
pfree(relation->rd_pubactions);
if (relation->rd_options)
@@ -4599,12 +4603,12 @@ insert_ordered_oid(List *list, Oid datum)
*
* It is up to the caller to make sure the given list is correctly ordered.
*
- * We deliberately do not change rd_indexattr here: even when operating+ * We deliberately do not change rd_hotattr here: even when operating
* with a temporary partial index list, HOT-update decisions must be made
* correctly with respect to the full index set. It is up to the caller
- * to ensure that a correct rd_indexattr set has been cached before first+ * to ensure that a correct rd_hotattr set has been cached before first
* calling RelationSetIndexList; else a subsequent inquiry might cause a
- * wrong rd_indexattr set to get computed and cached. Likewise, we do not+ * wrong rd_hotattr set to get computed and cached. Likewise, we do not
* touch rd_keyattr, rd_pkattr or rd_idattr.
*/
void
@@ -4834,6 +4838,97 @@ RelationGetIndexPredicate(Relation relation)
return result;
}
+#define MIN_UPDATES_THRESHOLD 10+#define MIN_HOT_HITS_PERCENT 10+#define MAX_HOT_INDEX_EXPR_COST 1000++/* options common to all indexes */+typedef struct+{+ int32 vl_len_;+ bool projection;+} index_options;++/*+ * Check if functional index is projection: index expression returns some subset of+ * its argument values. During hot update check projection indexes are handled in special way:+ * instead of checking if any of attributes used in indexed expression was updated,+ * we should calculate and compare values of index expression for old and new tuple values.+ *+ * Decision made by this function is based on three sources:+ * 1. Calculated hot update statistic: we compare number of hot updates which are performed+ * because projection index check shows that index expression is not changed with total+ * number of updates affecting attributes used in projection indexes. If it is smaller than+ * some threshold (10%), then index is considered as non-projective.+ * 2. Calculated cost of index expression: if it is higher than some threshold (1000) then+ * extra comparison of index expression values is expected to be too expensive.+ * 3. "projection" index option explicitly set by user. This setting overrides 1) and 2)+ */+static bool IsProjectionFunctionalIndex(Relation index, IndexInfo* ii)+{+ bool is_projection = false;++ if (ii->ii_Expressions)+ {+ HeapTuple tuple;+ Datum reloptions;+ bool isnull;+ PgStat_StatTabEntry* stat = pgstat_fetch_stat_tabentry(index->rd_index->indrelid);++ is_projection = true; /* by default functional index is considered as non-injective */++ if (stat != NULL+ && stat->hot_update_hits + stat->hot_update_misses > MIN_UPDATES_THRESHOLD+ && stat->hot_update_hits*100 / (stat->hot_update_hits + stat->hot_update_misses) < MIN_HOT_HITS_PERCENT)+ {+ /* If percent of hot updates is small, then disable projection index function+ * optimization to eliminate overhead of extra index expression evaluations.+ */+ is_projection = false;+ }+ else+ {+ QualCost index_expr_cost;+ cost_qual_eval(&index_expr_cost, ii->ii_Expressions, NULL);+ /*+ * If index expression is too expensive, then disable projection optimization, because+ * extra evaluation of index expression is expected to be more expensive than index update.+ * Current implementation of projection optimization has to calculate index expression twice+ * in case of hit (value of index expression is not changed) and three times if values are different.+ */+ if (index_expr_cost.startup + index_expr_cost.per_tuple > MAX_HOT_INDEX_EXPR_COST)+ {+ is_projection = false;+ }+ }+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(RelationGetRelid(index)));+ if (!HeapTupleIsValid(tuple))+ elog(ERROR, "cache lookup failed for relation %u", RelationGetRelid(index));++ reloptions = SysCacheGetAttr(RELOID, tuple,+ Anum_pg_class_reloptions, &isnull);+ if (!isnull)+ {+ static const relopt_parse_elt tab[] = {+ {"projection", RELOPT_TYPE_BOOL, offsetof(index_options, projection)}+ };+ int numoptions;+ relopt_value *options = parseRelOptions(reloptions, false,+ RELOPT_KIND_INDEX,+ &numoptions);+ if (numoptions != 0)+ {+ index_options optstruct;+ fillRelOptions((void *)&optstruct, sizeof(bool), options, numoptions, false, tab, lengthof(tab));+ is_projection = optstruct.projection;+ pfree(options);+ }+ }+ ReleaseSysCache(tuple);+ }+ return is_projection;+}+
/*
* RelationGetIndexAttrBitmap -- get a bitmap of index attribute numbers
*
@@ -4861,24 +4956,29 @@ RelationGetIndexPredicate(Relation relation)
Bitmapset *
RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
{
- Bitmapset *indexattrs; /* indexed columns */+ Bitmapset *hotattrs; /* identifies columns used in non-projection indexes */+ Bitmapset *warmattrs; /* identifies columns used in projection indexes */
Bitmapset *uindexattrs; /* columns in unique indexes */
Bitmapset *pkindexattrs; /* columns in the primary index */
Bitmapset *idindexattrs; /* columns in the replica identity */
+ Bitmapset *projindexes; /* projection indexes */
List *indexoidlist;
List *newindexoidlist;
Oid relpkindex;
Oid relreplindex;
ListCell *l;
MemoryContext oldcxt;
+ int indexno;
/* Quick exit if we already computed the result. */
- if (relation->rd_indexattr != NULL)+ if (relation->rd_hotattr != NULL)
{
switch (attrKind)
{
- case INDEX_ATTR_BITMAP_ALL:- return bms_copy(relation->rd_indexattr);+ case INDEX_ATTR_BITMAP_HOT:+ return bms_copy(relation->rd_hotattr);+ case INDEX_ATTR_BITMAP_WARM:+ return bms_copy(relation->rd_warmattr);
case INDEX_ATTR_BITMAP_KEY:
return bms_copy(relation->rd_keyattr);
case INDEX_ATTR_BITMAP_PRIMARY_KEY:
@@ -4915,7 +5015,7 @@ restart:
relreplindex = relation->rd_replidindex;
/*
- * For each index, add referenced attributes to indexattrs.+ * For each index, add referenced attributes to hotattrs.
*
* Note: we consider all indexes returned by RelationGetIndexList, even if
* they are not indisready or indisvalid. This is important because an
@@ -4924,10 +5024,13 @@ restart:
* CONCURRENTLY is far enough along that we should ignore the index, it
* won't be returned at all by RelationGetIndexList.
*/
- indexattrs = NULL;+ hotattrs = NULL;+ warmattrs = NULL;
uindexattrs = NULL;
pkindexattrs = NULL;
idindexattrs = NULL;
+ projindexes = NULL;+ indexno = 0;
foreach(l, indexoidlist)
{
Oid indexOid = lfirst_oid(l);
@@ -4961,8 +5064,8 @@ restart:
if (attrnum != 0)
{
- indexattrs = bms_add_member(indexattrs,- attrnum - FirstLowInvalidHeapAttributeNumber);+ hotattrs = bms_add_member(hotattrs,+ attrnum - FirstLowInvalidHeapAttributeNumber);
if (isKey)
uindexattrs = bms_add_member(uindexattrs,
@@ -4978,13 +5081,21 @@ restart:
}
}
- /* Collect all attributes used in expressions, too */- pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);-+ if (IsProjectionFunctionalIndex(indexDesc, indexInfo))+ {+ projindexes = bms_add_member(projindexes, indexno);+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &warmattrs);+ }+ else+ {+ /* Collect all attributes used in expressions, too */+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &hotattrs);+ }
/* Collect all attributes in the index predicate, too */
- pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);+ pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &hotattrs);
index_close(indexDesc, AccessShareLock);
+ indexno += 1;
}
/*
@@ -5010,24 +5121,30 @@ restart:
bms_free(uindexattrs);
bms_free(pkindexattrs);
bms_free(idindexattrs);
- bms_free(indexattrs);+ bms_free(hotattrs);+ bms_free(warmattrs);+ bms_free(projindexes);
goto restart;
}
/* Don't leak the old values of these bitmaps, if any */
- bms_free(relation->rd_indexattr);- relation->rd_indexattr = NULL;+ bms_free(relation->rd_hotattr);+ relation->rd_hotattr = NULL;+ bms_free(relation->rd_warmattr);+ relation->rd_warmattr = NULL;
bms_free(relation->rd_keyattr);
relation->rd_keyattr = NULL;
bms_free(relation->rd_pkattr);
relation->rd_pkattr = NULL;
bms_free(relation->rd_idattr);
relation->rd_idattr = NULL;
+ bms_free(relation->rd_projidx);+ relation->rd_projidx = NULL;
/*
* Now save copies of the bitmaps in the relcache entry. We intentionally
- * set rd_indexattr last, because that's the one that signals validity of+ * set rd_hotattr last, because that's the one that signals validity of
* the values; if we run out of memory before making that copy, we won't
* leave the relcache entry looking like the other ones are valid but
* empty.
@@ -5036,14 +5153,18 @@ restart:
relation->rd_keyattr = bms_copy(uindexattrs);
relation->rd_pkattr = bms_copy(pkindexattrs);
relation->rd_idattr = bms_copy(idindexattrs);
- relation->rd_indexattr = bms_copy(indexattrs);+ relation->rd_hotattr = bms_copy(hotattrs);+ relation->rd_warmattr = bms_copy(warmattrs);+ relation->rd_projidx = bms_copy(projindexes);
MemoryContextSwitchTo(oldcxt);
/* We return our original working copy for caller to play with */
switch (attrKind)
{
- case INDEX_ATTR_BITMAP_ALL:- return indexattrs;+ case INDEX_ATTR_BITMAP_HOT:+ return hotattrs;+ case INDEX_ATTR_BITMAP_WARM:+ return warmattrs;
case INDEX_ATTR_BITMAP_KEY:
return uindexattrs;
case INDEX_ATTR_BITMAP_PRIMARY_KEY:
@@ -5661,10 +5782,12 @@ load_relcache_init_file(bool shared)
rel->rd_oidindex = InvalidOid;
rel->rd_pkindex = InvalidOid;
rel->rd_replidindex = InvalidOid;
- rel->rd_indexattr = NULL;+ rel->rd_hotattr = NULL;+ rel->rd_warmattr = NULL;
rel->rd_keyattr = NULL;
rel->rd_pkattr = NULL;
rel->rd_idattr = NULL;
+ rel->rd_projidx = NULL;
rel->rd_pubactions = NULL;
rel->rd_statvalid = false;
rel->rd_statlist = NIL;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex 468e50a..3e70663 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1688,11 +1688,11 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_CONST("(");
/* ALTER INDEX <foo> SET|RESET ( */
else if (Matches5("ALTER", "INDEX", MatchAny, "RESET", "("))
- COMPLETE_WITH_LIST3("fillfactor", "fastupdate",- "gin_pending_list_limit");+ COMPLETE_WITH_LIST4("fillfactor", "fastupdate",+ "gin_pending_list_limit", "projection");
else if (Matches5("ALTER", "INDEX", MatchAny, "SET", "("))
- COMPLETE_WITH_LIST3("fillfactor =", "fastupdate =",- "gin_pending_list_limit =");+ COMPLETE_WITH_LIST4("fillfactor =", "fastupdate =",+ "gin_pending_list_limit =", "projection = ");
/* ALTER LANGUAGE <name> */
else if (Matches3("ALTER", "LANGUAGE", MatchAny))
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.hindex cd43e3a..5445d12 100644--- a/src/include/access/reloptions.h+++ b/src/include/access/reloptions.h@@ -51,6 +51,7 @@ typedef enum relopt_kind
RELOPT_KIND_PARTITIONED = (1 << 11),
/* if you add a new kind, make sure you update "last_default" too */
RELOPT_KIND_LAST_DEFAULT = RELOPT_KIND_PARTITIONED,
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,
/* some compilers treat enums as signed ints, so we can't use 1 << 31 */
RELOPT_KIND_MAX = (1 << 30)
} relopt_kind;
diff --git a/src/include/pgstat.h b/src/include/pgstat.hindex 089b7c3..872f962 100644--- a/src/include/pgstat.h+++ b/src/include/pgstat.h@@ -105,6 +105,8 @@ typedef struct PgStat_TableCounts
PgStat_Counter t_tuples_updated;
PgStat_Counter t_tuples_deleted;
PgStat_Counter t_tuples_hot_updated;
+ PgStat_Counter t_hot_update_hits;+ PgStat_Counter t_hot_update_misses;
bool t_truncated;
PgStat_Counter t_delta_live_tuples;
@@ -566,7 +568,7 @@ typedef union PgStat_Msg
* ------------------------------------------------------------
*/
-#define PGSTAT_FILE_FORMAT_ID 0x01A5BC9D+#define PGSTAT_FILE_FORMAT_ID 0x01A5BC9E
/* ----------
* PgStat_StatDBEntry The collector's data per database
@@ -626,6 +628,9 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter tuples_deleted;
PgStat_Counter tuples_hot_updated;
+ PgStat_Counter hot_update_hits;+ PgStat_Counter hot_update_misses;+
PgStat_Counter n_live_tuples;
PgStat_Counter n_dead_tuples;
PgStat_Counter changes_since_analyze;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.hindex 68fd6fb..3da96b1 100644--- a/src/include/utils/rel.h+++ b/src/include/utils/rel.h@@ -141,10 +141,12 @@ typedef struct RelationData
List *rd_statlist; /* list of OIDs of extended stats */
/* data managed by RelationGetIndexAttrBitmap: */
- Bitmapset *rd_indexattr; /* identifies columns used in indexes */+ Bitmapset *rd_hotattr; /* identifies columns used in non-projection indexes */+ Bitmapset *rd_warmattr; /* identifies columns used in projection indexes */
Bitmapset *rd_keyattr; /* cols that can be ref'd by foreign keys */
Bitmapset *rd_pkattr; /* cols included in primary key */
Bitmapset *rd_idattr; /* included in replica identity index */
+ Bitmapset *rd_projidx; /* projection indexes */
PublicationActions *rd_pubactions; /* publication actions */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.hindex 29c6d9b..87df256 100644--- a/src/include/utils/relcache.h+++ b/src/include/utils/relcache.h@@ -53,7 +53,8 @@ extern List *RelationGetIndexPredicate(Relation relation);
typedef enum IndexAttrBitmapKind
{
- INDEX_ATTR_BITMAP_ALL,+ INDEX_ATTR_BITMAP_HOT,+ INDEX_ATTR_BITMAP_WARM,
INDEX_ATTR_BITMAP_KEY,
INDEX_ATTR_BITMAP_PRIMARY_KEY,
INDEX_ATTR_BITMAP_IDENTITY_KEY
diff --git a/src/test/regress/expected/func_index.out b/src/test/regress/expected/func_index.outnew file mode 100644index 0000000..06f0de9--- /dev/null+++ b/src/test/regress/expected/func_index.out@@ -0,0 +1,29 @@+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (projection=false);+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+set client_min_messages=notice;+drop table keyvalue;+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (projection=true);+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+DEBUG: Use hot update+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+DEBUG: Use hot update+set client_min_messages=notice;+drop table keyvalue;+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name'));+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+DEBUG: Use hot update+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+DEBUG: Use hot update+set client_min_messages=notice;+drop table keyvalue;diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex e224977..5a4e9e5 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -79,7 +79,7 @@ ignore: random
# ----------
# Another group of parallel tests
# ----------
-test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update namespace prepared_xacts delete+test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index func_index update namespace prepared_xacts delete
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_scheduleindex 9fc5f1a..498c698 100644--- a/src/test/regress/serial_schedule+++ b/src/test/regress/serial_schedule@@ -101,6 +101,7 @@ test: portals
test: arrays
test: btree_index
test: hash_index
+test: func_index
test: update
test: delete
test: namespace
diff --git a/src/test/regress/sql/func_index.sql b/src/test/regress/sql/func_index.sqlnew file mode 100644index 0000000..9540c07--- /dev/null+++ b/src/test/regress/sql/func_index.sql@@ -0,0 +1,29 @@+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (projection=false);+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+set client_min_messages=notice;+drop table keyvalue;++create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (projection=true);+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+set client_min_messages=notice;+drop table keyvalue;++create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name'));+set client_min_messages=debug1;+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+set client_min_messages=notice;+drop table keyvalue;++
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-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru, michael.paquier@gmail.com, simon@2ndquadrant.com, myon@debian.org, thomas.munro@enterprisedb.com, peter.eisentraut@2ndquadrant.com
Subject: Re: [HACKERS] Surjective functional indexes
In-Reply-To: <02236ec2-8d17-9845-07fe-e36f7a5339e4@postgrespro.ru>
* 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