Thread: pgsql: Allow HOT updates for some expression indexes
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(-)
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