From c203d6cf81b4d7e43edb2b75ec1b741ba48e04e0 Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Tue, 27 Mar 2018 19:57:02 +0100 Subject: 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 --- doc/src/sgml/ref/create_index.sgml | 37 +++++++++++++++++++++++++++++++++++-- 1 file changed, 35 insertions(+), 2 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 1fd21e12bd7..ba1c5d63925 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -309,8 +309,41 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] The optional WITH clause specifies storage parameters 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: + + + + + recheck_on_update + + + Specifies whether to recheck a functional index value to see whether + we can use a HOT update or not. The default value is on for functional + indexes with an total expression cost less than 1000, otherwise off. + You might decide to turn this off if you knew that a function used in + an index is unlikely to return the same value when one of the input + columns is updated and so the recheck is not worth the additional cost + of executing the function. + + + + Functional indexes are used frequently for the case where the function + returns a subset of the argument. Examples of this would be accessing + part of a string with SUBSTR() or accessing a single + field in a JSON document using an expression such as + (bookinfo->>'isbn'). In this example, the JSON + document might be updated frequently, yet it is uncommon for the ISBN + field for a book to change so we would keep the parameter set to on + for that index. A more frequently changing field might have an index + with this parameter turned off, while very frequently changing fields + might be better to avoid indexing at all under high load. + + + + + + + The B-tree, hash, GiST and SP-GiST index methods all accept this parameter: -- cgit v1.2.3