From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Michael Paquier <michael.paquier@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: 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: Fri, 15 Dec 2017 12:14:05 +0300
Message-ID: <623fdf0f-c3d1-7553-e784-f8d5a900239b@postgrespro.ru> (raw)
In-Reply-To: <CAB7nPqR8GPni-Y_Tr1+6XFdYCuum8a5MuB9q2+hnVcA7BYrorw@mail.gmail.com>
References: <aad81201-8788-a621-2001-537d843186fb@postgrespro.ru>
<20171214211553.ijxfd4p52nz7qg44@alvherre.pgsql>
<CAB7nPqR8GPni-Y_Tr1+6XFdYCuum8a5MuB9q2+hnVcA7BYrorw@mail.gmail.com>
On 15.12.2017 01:21, Michael Paquier wrote:
> On Fri, Dec 15, 2017 at 6:15 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>> Konstantin Knizhnik wrote:
>>> If you still thing that additional 16 bytes per relation in statistic is too
>>> high overhead, then I will also remove autotune.
>> I think it's pretty clear that these additional bytes are excessive.
> The bar to add new fields in PgStat_TableCounts in very high, and one
> attempt to tackle its scaling problems with many relations is here by
> Horiguchi-san:
> https://www.postgresql.org/message-id/20171211.201523.24172046.horiguchi.kyotaro@lab.ntt.co.jp
> His patch may be worth a look if you need more fields for your
> feature. So it seems to me that the patch as currently presented has
> close to zero chance to be committed as long as you keep your changes
> to pgstat.c.
Ok, looks like everybody think that autotune based on statistic is bad idea.
Attached please find patch without autotune.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:projection-6.patch (text/x-patch, 27.0K) downloaddiff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgmlindex 0255375..c4ee15e 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>recheck_on_update</></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 recheck_on_update 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..1aaab78 100644--- a/src/backend/access/common/reloptions.c+++ b/src/backend/access/common/reloptions.c@@ -131,6 +131,15 @@ static relopt_bool boolRelOpts[] =
},
{
{
+ "recheck_on_update",+ "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);
}
@@ -1468,6 +1477,40 @@ index_reloptions(amoptions_function amoptions, Datum reloptions, bool validate)
}
/*
+ * Parse generic options for all indexes.+ *+ * reloptions options as text[] datum+ * validate error flag+ */+bytea *+index_generic_reloptions(Datum reloptions, bool validate)+{+ int numoptions;+ GenericIndexOpts *idxopts;+ relopt_value *options;+ static const relopt_parse_elt tab[] = {+ {"recheck_on_update", RELOPT_TYPE_BOOL, offsetof(GenericIndexOpts, recheck_on_update)}+ };++ options = parseRelOptions(reloptions, validate,+ RELOPT_KIND_INDEX,+ &numoptions);++ /* if none set, we're done */+ if (numoptions == 0)+ return NULL;++ idxopts = allocateReloptStruct(sizeof(GenericIndexOpts), options, numoptions);++ fillRelOptions((void *)idxopts, sizeof(GenericIndexOpts), options, numoptions,+ validate, tab, lengthof(tab));++ pfree(options);++ return (bytea*) idxopts;+}++/*
* Option parser for attribute reloptions
*/
bytea *
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.cindex 3acef27..bd26091 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 *proj_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);+ proj_attrs = RelationGetIndexAttrBitmap(relation, INDEX_ATTR_BITMAP_PROJ);
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, proj_attrs);
hot_attrs_checked = true;
}
interesting_attrs = bms_add_members(interesting_attrs, key_attrs);
@@ -3871,6 +3876,7 @@ l2:
if (vmbuffer != InvalidBuffer)
ReleaseBuffer(vmbuffer);
bms_free(hot_attrs);
+ bms_free(proj_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, proj_attrs)+ || ProjectionIsNotChanged(relation, &oldtup, newtup)))+ {
use_hot_update = true;
+ }
}
else
{
@@ -4344,6 +4355,7 @@ l2:
heap_freetuple(old_key_tuple);
bms_free(hot_attrs);
+ bms_free(proj_attrs);
bms_free(key_attrs);
bms_free(id_attrs);
bms_free(modified_attrs);
@@ -4431,6 +4443,82 @@ 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);++ return equals;+}+++/*
* Check which columns are being updated.
*
* Given an updated tuple, determine (and return into the output bitmapset),
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/utils/cache/relcache.c b/src/backend/utils/cache/relcache.cindex 1908420..9faea7f 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"
@@ -2350,9 +2352,11 @@ RelationDestroyRelation(Relation relation, bool remember_tupdesc)
list_free_deep(relation->rd_fkeylist);
list_free(relation->rd_indexlist);
bms_free(relation->rd_indexattr);
+ bms_free(relation->rd_projindexattr);
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)
@@ -4834,6 +4838,64 @@ RelationGetIndexPredicate(Relation relation)
return result;
}
+#define MAX_HOT_INDEX_EXPR_COST 1000++/*+ * 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 two sources:+ * 1. 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.+ * 2. "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;+ QualCost index_expr_cost;++ is_projection = true; /* by default functional index is considered as non-injective */++ 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)+ {+ GenericIndexOpts *idxopts = (GenericIndexOpts *)index_generic_reloptions(reloptions, false);+ if (idxopts != NULL)+ {+ is_projection = idxopts->recheck_on_update;+ pfree(idxopts);+ }+ }+ ReleaseSysCache(tuple);+ }+ return is_projection;+}+
/*
* RelationGetIndexAttrBitmap -- get a bitmap of index attribute numbers
*
@@ -4861,24 +4923,29 @@ RelationGetIndexPredicate(Relation relation)
Bitmapset *
RelationGetIndexAttrBitmap(Relation relation, IndexAttrBitmapKind attrKind)
{
- Bitmapset *indexattrs; /* indexed columns */+ Bitmapset *indexattrs; /* identifies columns used in non-projection indexes */+ Bitmapset *projindexattrs; /* 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)
{
switch (attrKind)
{
- case INDEX_ATTR_BITMAP_ALL:+ case INDEX_ATTR_BITMAP_HOT:
return bms_copy(relation->rd_indexattr);
+ case INDEX_ATTR_BITMAP_PROJ:+ return bms_copy(relation->rd_projindexattr);
case INDEX_ATTR_BITMAP_KEY:
return bms_copy(relation->rd_keyattr);
case INDEX_ATTR_BITMAP_PRIMARY_KEY:
@@ -4925,9 +4992,12 @@ restart:
* won't be returned at all by RelationGetIndexList.
*/
indexattrs = NULL;
+ projindexattrs = NULL;
uindexattrs = NULL;
pkindexattrs = NULL;
idindexattrs = NULL;
+ projindexes = NULL;+ indexno = 0;
foreach(l, indexoidlist)
{
Oid indexOid = lfirst_oid(l);
@@ -4978,13 +5048,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, &projindexattrs);+ }+ else+ {+ /* Collect all attributes used in expressions, too */+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);+ }
/* Collect all attributes in the index predicate, too */
pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
index_close(indexDesc, AccessShareLock);
+ indexno += 1;
}
/*
@@ -5011,6 +5089,8 @@ restart:
bms_free(pkindexattrs);
bms_free(idindexattrs);
bms_free(indexattrs);
+ bms_free(projindexattrs);+ bms_free(projindexes);
goto restart;
}
@@ -5018,12 +5098,16 @@ 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_projindexattr);+ relation->rd_projindexattr = 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
@@ -5037,13 +5121,17 @@ restart:
relation->rd_pkattr = bms_copy(pkindexattrs);
relation->rd_idattr = bms_copy(idindexattrs);
relation->rd_indexattr = bms_copy(indexattrs);
+ relation->rd_projindexattr = bms_copy(projindexattrs);+ 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:+ case INDEX_ATTR_BITMAP_HOT:
return indexattrs;
+ case INDEX_ATTR_BITMAP_PROJ:+ return projindexattrs;
case INDEX_ATTR_BITMAP_KEY:
return uindexattrs;
case INDEX_ATTR_BITMAP_PRIMARY_KEY:
@@ -5662,9 +5750,11 @@ load_relcache_init_file(bool shared)
rel->rd_pkindex = InvalidOid;
rel->rd_replidindex = InvalidOid;
rel->rd_indexattr = NULL;
+ rel->rd_projindexattr = 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..8b88efc 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", "recheck_on_update");
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 =", "recheck_on_update = ");
/* ALTER LANGUAGE <name> */
else if (Matches3("ALTER", "LANGUAGE", MatchAny))
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.hindex cd43e3a..46e1062 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;
@@ -276,6 +277,7 @@ extern bytea *heap_reloptions(char relkind, Datum reloptions, bool validate);
extern bytea *view_reloptions(Datum reloptions, bool validate);
extern bytea *index_reloptions(amoptions_function amoptions, Datum reloptions,
bool validate);
+extern bytea *index_generic_reloptions(Datum reloptions, bool validate);
extern bytea *attribute_reloptions(Datum reloptions, bool validate);
extern bytea *tablespace_reloptions(Datum reloptions, bool validate);
extern LOCKMODE AlterTableGetRelOptionsLockLevel(List *defList);
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.hindex 68fd6fb..bfe9296 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_indexattr; /* identifies columns used in non-projection indexes */+ Bitmapset *rd_projindexattr; /* 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 */
@@ -245,6 +247,14 @@ typedef struct ForeignKeyCacheInfo
Oid conpfeqop[INDEX_MAX_KEYS]; /* PK = FK operator OIDs */
} ForeignKeyCacheInfo;
+/*+ * Options common for all all indexes+ */+typedef struct GenericIndexOpts+{+ int32 vl_len_;+ bool recheck_on_update;+} GenericIndexOpts;
/*
* StdRdOptions
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.hindex 29c6d9b..fd43893 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_PROJ,
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..e616ea2--- /dev/null+++ b/src/test/regress/expected/func_index.out@@ -0,0 +1,61 @@+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (recheck_on_update=false);+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 0+(1 row)++drop table keyvalue;+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (recheck_on_update=true);+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 1+(1 row)++update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 1+(1 row)++update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 2+(1 row)++drop table keyvalue;+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name'));+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 1+(1 row)++update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 1+(1 row)++update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+ pg_stat_get_xact_tuples_hot_updated +-------------------------------------+ 2+(1 row)++drop table keyvalue;diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex e224977..8a38952 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -84,7 +84,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password+test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password func_index
# ----------
# 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..b08f754--- /dev/null+++ b/src/test/regress/sql/func_index.sql@@ -0,0 +1,30 @@+create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (recheck_on_update=false);+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+drop table keyvalue;++create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name')) with (recheck_on_update=true);+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+drop table keyvalue;++create table keyvalue(id integer primary key, info jsonb);+create index nameindex on keyvalue((info->>'name'));+insert into keyvalue values (1, '{"name": "john", "data": "some data"}');+update keyvalue set info='{"name": "john", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+update keyvalue set info='{"name": "smith", "data": "some other data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+update keyvalue set info='{"name": "smith", "data": "some more data"}' where id=1;+select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);+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, alvherre@alvh.no-ip.org, simon@2ndquadrant.com, myon@debian.org, thomas.munro@enterprisedb.com, peter.eisentraut@2ndquadrant.com
Subject: Re: [HACKERS] Surjective functional indexes
In-Reply-To: <623fdf0f-c3d1-7553-e784-f8d5a900239b@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