pg.ddx.io pgsql-committers@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql: Allow HOT updates for some expression indexes
7+ messages / 4 participants
[nested] [flat]
* pgsql: Allow HOT updates for some expression indexes
@ 2018-03-27 19:01 Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2018-03-27 19:01 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
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, giving
a significant performance boost in those cases.
Particularly useful for indexes such as JSON->>field where the
JSON value changes but the indexed value does not.
Submitted as "surjective indexes" patch, now enabled by use
of new "recheck_on_update" parameter.
Author: Konstantin Knizhnik
Reviewer: Simon Riggs, with much wordsmithing and some cleanup
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/c203d6cf81b4d7e43edb2b75ec1b741ba48e04e0
Modified Files
--------------
doc/src/sgml/ref/create_index.sgml | 37 +++++++++-
src/backend/access/common/reloptions.c | 45 ++++++++++++-
src/backend/access/heap/heapam.c | 105 +++++++++++++++++++++++++++--
src/backend/catalog/index.c | 3 +-
src/backend/utils/cache/relcache.c | 112 +++++++++++++++++++++++++++++--
src/bin/psql/tab-complete.c | 4 +-
src/include/access/reloptions.h | 2 +
src/include/utils/rel.h | 12 +++-
src/include/utils/relcache.h | 3 +-
src/test/regress/expected/func_index.out | 61 +++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/func_index.sql | 30 +++++++++
13 files changed, 395 insertions(+), 22 deletions(-)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-27 19:11 Simon Riggs <simon@2ndquadrant.com>
parent: Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2018-03-27 19:11 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Buildfarm failure seen, investigating
On 27 March 2018 at 20:01, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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, giving
> a significant performance boost in those cases.
>
> Particularly useful for indexes such as JSON->>field where the
> JSON value changes but the indexed value does not.
>
> Submitted as "surjective indexes" patch, now enabled by use
> of new "recheck_on_update" parameter.
>
> Author: Konstantin Knizhnik
> Reviewer: Simon Riggs, with much wordsmithing and some cleanup
>
> Branch
> ------
> master
>
> Details
> -------
> https://git.postgresql.org/pg/commitdiff/c203d6cf81b4d7e43edb2b75ec1b741ba48e04e0
>
> Modified Files
> --------------
> doc/src/sgml/ref/create_index.sgml | 37 +++++++++-
> src/backend/access/common/reloptions.c | 45 ++++++++++++-
> src/backend/access/heap/heapam.c | 105 +++++++++++++++++++++++++++--
> src/backend/catalog/index.c | 3 +-
> src/backend/utils/cache/relcache.c | 112 +++++++++++++++++++++++++++++--
> src/bin/psql/tab-complete.c | 4 +-
> src/include/access/reloptions.h | 2 +
> src/include/utils/rel.h | 12 +++-
> src/include/utils/relcache.h | 3 +-
> src/test/regress/expected/func_index.out | 61 +++++++++++++++++
> src/test/regress/parallel_schedule | 2 +-
> src/test/regress/serial_schedule | 1 +
> src/test/regress/sql/func_index.sql | 30 +++++++++
> 13 files changed, 395 insertions(+), 22 deletions(-)
>
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-27 19:19 Simon Riggs <simon@2ndquadrant.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2018-03-27 19:19 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
On 27 March 2018 at 20:11, Simon Riggs <simon@2ndquadrant.com> wrote:
> Buildfarm failure seen, investigating
Error on FreeBSD only , perhaps timing-related as a result of using
pg_stat_get_xact_tuples_hot_updated()
in tests.
Will wait to collect other failures
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-27 21:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-03-27 21:07 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-committers@lists.postgresql.org, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Simon Riggs <simon@2ndquadrant.com> writes:
> On 27 March 2018 at 20:11, Simon Riggs <simon@2ndquadrant.com> wrote:
>> Buildfarm failure seen, investigating
> Error on FreeBSD only , perhaps timing-related as a result of using
> pg_stat_get_xact_tuples_hot_updated()
> in tests.
It's by now obvious that it's not just "FreeBSD only".
The test script appears to imagine that pgstats outputs update
instantaneously. I'm rather astonished that it passes anywhere,
much less that it seemingly passes reliably enough for you
to have not been noticed in precommit testing.
The existing "stats" test moves mountains to get repeatable results,
and is pretty slow in consequence, and even so it doesn't always succeed.
I think that rather than going down that path, you should drop this test
approach and think of some other way.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-27 21:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-03-27 21:26 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-committers@lists.postgresql.org, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
I wrote:
> The test script appears to imagine that pgstats outputs update
> instantaneously.
Oh, wait, it's looking at pg_stat_xact, which is the *local*, unsent
stats information. So your problem is actually the reverse of that:
if the test runs too slowly, it fails, because at some point the
unsent stats information will get flushed out to the collector and
disappear from the pg_stat_xact view.
You could probably make this more reliable by wrapping each test
stanza in a transaction, ie instead of
create table keyvalue ...;
... do something to table ...
select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
drop table keyvalue;
do
begin;
create table keyvalue ...;
... do something to table ...
select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
rollback;
(no need for a DROP if you're rolling it all back)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-28 04:23 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2018-03-28 04:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.postgresql.org, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
On 27 March 2018 at 22:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> The test script appears to imagine that pgstats outputs update
>> instantaneously.
>
> Oh, wait, it's looking at pg_stat_xact, which is the *local*, unsent
> stats information. So your problem is actually the reverse of that:
> if the test runs too slowly, it fails, because at some point the
> unsent stats information will get flushed out to the collector and
> disappear from the pg_stat_xact view.
>
> You could probably make this more reliable by wrapping each test
> stanza in a transaction, ie instead of
>
> create table keyvalue ...;
> ... do something to table ...
> select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
> drop table keyvalue;
>
> do
>
> begin;
> create table keyvalue ...;
> ... do something to table ...
> select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
> rollback;
>
> (no need for a DROP if you're rolling it all back)
Oh wow, thanks. Fixed.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pgsql: Allow HOT updates for some expression indexes
@ 2018-03-28 07:44 Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 7+ messages in thread
From: Konstantin Knizhnik @ 2018-03-28 07:44 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-committers@lists.postgresql.org
On 28.03.2018 07:23, Simon Riggs wrote:
> On 27 March 2018 at 22:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I wrote:
>>> The test script appears to imagine that pgstats outputs update
>>> instantaneously.
>> Oh, wait, it's looking at pg_stat_xact, which is the *local*, unsent
>> stats information. So your problem is actually the reverse of that:
>> if the test runs too slowly, it fails, because at some point the
>> unsent stats information will get flushed out to the collector and
>> disappear from the pg_stat_xact view.
>>
>> You could probably make this more reliable by wrapping each test
>> stanza in a transaction, ie instead of
>>
>> create table keyvalue ...;
>> ... do something to table ...
>> select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
>> drop table keyvalue;
>>
>> do
>>
>> begin;
>> create table keyvalue ...;
>> ... do something to table ...
>> select pg_stat_get_xact_tuples_hot_updated('keyvalue'::regclass);
>> rollback;
>>
>> (no need for a DROP if you're rolling it all back)
> Oh wow, thanks. Fixed.
>
I am sorry, I missed the fact that local stat information can be reset.
Attached please find updated version of the patch with func_index.sql
test fixed.
Also I have renamed rd_indexattr to rd_nprjindexattr to avoid confusions.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
projection-8.patch (text/x-patch, 28.6K) download
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 0255375..acccaa5 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</literal></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.c
index 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.c
index 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.c
index 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.c
index 1908420..6690670 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_nprjindexattr);
+ bms_free(relation->rd_prjindexattr);
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_nprjindexattr 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_nprjindexattr 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_nprjindexattr set to get computed and cached. Likewise, we do not
* touch rd_keyattr, rd_pkattr or rd_idattr.
*/
void
@@ -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)
+ if (relation->rd_nprjindexattr != NULL)
{
switch (attrKind)
{
- case INDEX_ATTR_BITMAP_ALL:
- return bms_copy(relation->rd_indexattr);
+ case INDEX_ATTR_BITMAP_HOT:
+ return bms_copy(relation->rd_nprjindexattr);
+ case INDEX_ATTR_BITMAP_PROJ:
+ return bms_copy(relation->rd_prjindexattr);
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,23 +5089,29 @@ restart:
bms_free(pkindexattrs);
bms_free(idindexattrs);
bms_free(indexattrs);
+ bms_free(projindexattrs);
+ 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_nprjindexattr);
+ relation->rd_nprjindexattr = NULL;
+ bms_free(relation->rd_prjindexattr);
+ relation->rd_prjindexattr = 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_nprjindexattr 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 +5120,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_nprjindexattr = bms_copy(indexattrs);
+ relation->rd_prjindexattr = 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:
@@ -5661,10 +5749,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_nprjindexattr = NULL;
+ rel->rd_prjindexattr = 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.c
index 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.h
index 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.h
index 68fd6fb..332db5f 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_nprjindexattr; /* identifies columns used in non-projection indexes */
+ Bitmapset *rd_prjindexattr;/* 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.h
index 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.out
new file mode 100644
index 0000000..b7ad43b
--- /dev/null
+++ b/src/test/regress/expected/func_index.out
@@ -0,0 +1,67 @@
+begin;
+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;
+commit;
+begin;
+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;
+commit;
+begin;
+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;
+commit;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 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_schedule
index 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.sql
new file mode 100644
index 0000000..2bc96b2
--- /dev/null
+++ b/src/test/regress/sql/func_index.sql
@@ -0,0 +1,34 @@
+begin;
+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;
+commit;
+
+begin;
+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;
+commit;
+
+begin;
+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;
+commit;
end of thread, other threads:[~2018-03-28 07:44 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-27 19:01 pgsql: Allow HOT updates for some expression indexes Simon Riggs <simon@2ndQuadrant.com>
2018-03-27 19:11 ` Simon Riggs <simon@2ndquadrant.com>
2018-03-27 19:19 ` Simon Riggs <simon@2ndquadrant.com>
2018-03-27 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-27 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-28 04:23 ` Simon Riggs <simon@2ndquadrant.com>
2018-03-28 07:44 ` Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
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