pgsql: Allow HOT updates for some expression indexes

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

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

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

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

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

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