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

4 files changed, 93 insertions, 1 deletions

diff --git a/src/test/regress/expected/func_index.out b/src/test/regress/expected/func_index.out
new file mode 100644
index 00000000000..e616ea2e557
--- /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_schedule
index d308a05117d..fda54d4b67d 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 45147e9328a..f79e31d0286 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 00000000000..b08f7544fc5
--- /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;
+
+