Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit a1b395b

Browse filesBrowse files
MasaoFujiiMasaoFujii
committed
Add GUC and storage parameter to set the maximum size of GIN pending list.
Previously the maximum size of GIN pending list was controlled only by work_mem. But the reasonable value of work_mem and the reasonable size of the list are basically not the same, so it was not appropriate to control both of them by only one GUC, i.e., work_mem. This commit separates new GUC, pending_list_cleanup_size, from work_mem to allow users to control only the size of the list. Also this commit adds pending_list_cleanup_size as new storage parameter to allow users to specify the size of the list per index. This is useful, for example, when users want to increase the size of the list only for the GIN index which can be updated heavily, and decrease it otherwise. Reviewed by Etsuro Fujita.
1 parent ae667f7 commit a1b395b
Copy full SHA for a1b395b

File tree

Expand file treeCollapse file tree

16 files changed

+125
-31
lines changed
Filter options
Expand file treeCollapse file tree

16 files changed

+125
-31
lines changed

‎doc/src/sgml/config.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/config.sgml
+21Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5911,6 +5911,27 @@ SET XML OPTION { DOCUMENT | CONTENT };
59115911
</listitem>
59125912
</varlistentry>
59135913

5914+
<varlistentry id="guc-pending-list-cleanup-size" xreflabel="pending_list_cleanup_size">
5915+
<term><varname>pending_list_cleanup_size</varname> (<type>integer</type>)
5916+
<indexterm>
5917+
<primary><varname>pending_list_cleanup_size</> configuration parameter</primary>
5918+
</indexterm>
5919+
</term>
5920+
<listitem>
5921+
<para>
5922+
Sets the maximum size of the GIN pending list which is used
5923+
when <literal>fastupdate</> is enabled. If the list grows
5924+
larger than this maximum size, it is cleaned up by moving
5925+
the entries in it to the main GIN data structure in bulk.
5926+
The default is four megabytes (<literal>4MB</>). This setting
5927+
can be overridden for individual GIN indexes by changing
5928+
storage parameters.
5929+
See <xref linkend="gin-fast-update"> and <xref linkend="gin-tips">
5930+
for more information.
5931+
</para>
5932+
</listitem>
5933+
</varlistentry>
5934+
59145935
</variablelist>
59155936
</sect2>
59165937
<sect2 id="runtime-config-client-format">

‎doc/src/sgml/gin.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/gin.sgml
+19-11Lines changed: 19 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -728,8 +728,8 @@
728728
from the indexed item). As of <productname>PostgreSQL</productname> 8.4,
729729
<acronym>GIN</> is capable of postponing much of this work by inserting
730730
new tuples into a temporary, unsorted list of pending entries.
731-
When the table is vacuumed, or if the pending list becomes too large
732-
(larger than <xref linkend="guc-work-mem">), the entries are moved to the
731+
When the table is vacuumed, or if the pending list becomes larger than
732+
<xref linkend="guc-pending-list-cleanup-size">, the entries are moved to the
733733
main <acronym>GIN</acronym> data structure using the same bulk insert
734734
techniques used during initial index creation. This greatly improves
735735
<acronym>GIN</acronym> index update speed, even counting the additional
@@ -750,7 +750,7 @@
750750
<para>
751751
If consistent response time is more important than update speed,
752752
use of pending entries can be disabled by turning off the
753-
<literal>FASTUPDATE</literal> storage parameter for a
753+
<literal>fastupdate</literal> storage parameter for a
754754
<acronym>GIN</acronym> index. See <xref linkend="sql-createindex">
755755
for details.
756756
</para>
@@ -812,18 +812,26 @@
812812
</varlistentry>
813813

814814
<varlistentry>
815-
<term><xref linkend="guc-work-mem"></term>
815+
<term><xref linkend="guc-pending-list-cleanup-size"></term>
816816
<listitem>
817817
<para>
818818
During a series of insertions into an existing <acronym>GIN</acronym>
819-
index that has <literal>FASTUPDATE</> enabled, the system will clean up
819+
index that has <literal>fastupdate</> enabled, the system will clean up
820820
the pending-entry list whenever the list grows larger than
821-
<varname>work_mem</>. To avoid fluctuations in observed response time,
822-
it's desirable to have pending-list cleanup occur in the background
823-
(i.e., via autovacuum). Foreground cleanup operations can be avoided by
824-
increasing <varname>work_mem</> or making autovacuum more aggressive.
825-
However, enlarging <varname>work_mem</> means that if a foreground
826-
cleanup does occur, it will take even longer.
821+
<varname>pending_list_cleanup_size</>. To avoid fluctuations in observed
822+
response time, it's desirable to have pending-list cleanup occur in the
823+
background (i.e., via autovacuum). Foreground cleanup operations
824+
can be avoided by increasing <varname>pending_list_cleanup_size</>
825+
or making autovacuum more aggressive.
826+
However, enlarging the threshold of the cleanup operation means that
827+
if a foreground cleanup does occur, it will take even longer.
828+
</para>
829+
<para>
830+
<varname>pending_list_cleanup_size</> can be overridden for individual
831+
GIN indexes by changing storage parameters, and which allows each
832+
GIN index to have its own cleanup threshold.
833+
For example, it's possible to increase the threshold only for the GIN
834+
index which can be updated heavily, and decrease it otherwise.
827835
</para>
828836
</listitem>
829837
</varlistentry>

‎doc/src/sgml/gist.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/gist.sgml
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -861,7 +861,7 @@ my_distance(PG_FUNCTION_ARGS)
861861
<para>
862862
By default, a GiST index build switches to the buffering method when the
863863
index size reaches <xref linkend="guc-effective-cache-size">. It can
864-
be manually turned on or off by the <literal>BUFFERING</literal> parameter
864+
be manually turned on or off by the <literal>buffering</literal> parameter
865865
to the CREATE INDEX command. The default behavior is good for most cases,
866866
but turning buffering off might speed up the build somewhat if the input
867867
data is ordered.

‎doc/src/sgml/ref/cluster.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/cluster.sgml
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -46,7 +46,7 @@ CLUSTER [VERBOSE]
4646
not clustered. That is, no attempt is made to store new or
4747
updated rows according to their index order. (If one wishes, one can
4848
periodically recluster by issuing the command again. Also, setting
49-
the table's <literal>FILLFACTOR</literal> storage parameter to less than
49+
the table's <literal>fillfactor</literal> storage parameter to less than
5050
100% can aid in preserving cluster ordering during updates, since updated
5151
rows are kept on the same page if enough space is available there.)
5252
</para>

‎doc/src/sgml/ref/create_index.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_index.sgml
+16-5Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -300,7 +300,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
300300

301301
<variablelist>
302302
<varlistentry>
303-
<term><literal>FILLFACTOR</></term>
303+
<term><literal>fillfactor</></term>
304304
<listitem>
305305
<para>
306306
The fillfactor for an index is a percentage that determines how full
@@ -327,7 +327,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
327327

328328
<variablelist>
329329
<varlistentry>
330-
<term><literal>BUFFERING</></term>
330+
<term><literal>buffering</></term>
331331
<listitem>
332332
<para>
333333
Determines whether the buffering build technique described in
@@ -341,12 +341,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
341341
</variablelist>
342342

343343
<para>
344-
GIN indexes accept a different parameter:
344+
GIN indexes accept different parameters:
345345
</para>
346346

347347
<variablelist>
348348
<varlistentry>
349-
<term><literal>FASTUPDATE</></term>
349+
<term><literal>fastupdate</></term>
350350
<listitem>
351351
<para>
352352
This setting controls usage of the fast update technique described in
@@ -359,7 +359,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
359359

360360
<note>
361361
<para>
362-
Turning <literal>FASTUPDATE</> off via <command>ALTER INDEX</> prevents
362+
Turning <literal>fastupdate</> off via <command>ALTER INDEX</> prevents
363363
future insertions from going into the list of pending index entries,
364364
but does not in itself flush previous entries. You might want to
365365
<command>VACUUM</> the table afterward to ensure the pending list is
@@ -369,6 +369,17 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
369369
</listitem>
370370
</varlistentry>
371371
</variablelist>
372+
<variablelist>
373+
<varlistentry>
374+
<term><literal>pending_list_cleanup_size</></term>
375+
<listitem>
376+
<para>
377+
Custom <xref linkend="guc-pending-list-cleanup-size"> parameter.
378+
This value is specified in kilobytes.
379+
</para>
380+
</listitem>
381+
</varlistentry>
382+
</variablelist>
372383
</refsect2>
373384

374385
<refsect2 id="SQL-CREATEINDEX-CONCURRENTLY">

‎src/backend/access/common/reloptions.c

Copy file name to clipboardExpand all lines: src/backend/access/common/reloptions.c
+8Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,6 +216,14 @@ static relopt_int intRelOpts[] =
216216
RELOPT_KIND_BRIN
217217
}, 128, 1, 131072
218218
},
219+
{
220+
{
221+
"pending_list_cleanup_size",
222+
"Maximum size of the pending list for this GIN index, in kilobytes.",
223+
RELOPT_KIND_GIN
224+
},
225+
-1, 64, MAX_KILOBYTES
226+
},
219227

220228
/* list terminator */
221229
{{NULL}}

‎src/backend/access/gin/ginfast.c

Copy file name to clipboardExpand all lines: src/backend/access/gin/ginfast.c
+7-2Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@
2525
#include "utils/memutils.h"
2626
#include "utils/rel.h"
2727

28+
/* GUC parameter */
29+
int pending_list_cleanup_size = 0;
2830

2931
#define GIN_PAGE_FREESIZE \
3032
( BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - MAXALIGN(sizeof(GinPageOpaqueData)) )
@@ -228,6 +230,7 @@ ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)
228230
ginxlogUpdateMeta data;
229231
bool separateList = false;
230232
bool needCleanup = false;
233+
int cleanupSize;
231234

232235
if (collector->ntuples == 0)
233236
return;
@@ -422,11 +425,13 @@ ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)
422425
* ginInsertCleanup could take significant amount of time, so we prefer to
423426
* call it when it can do all the work in a single collection cycle. In
424427
* non-vacuum mode, it shouldn't require maintenance_work_mem, so fire it
425-
* while pending list is still small enough to fit into work_mem.
428+
* while pending list is still small enough to fit into
429+
* pending_list_cleanup_size.
426430
*
427431
* ginInsertCleanup() should not be called inside our CRIT_SECTION.
428432
*/
429-
if (metadata->nPendingPages * GIN_PAGE_FREESIZE > work_mem * 1024L)
433+
cleanupSize = GinGetPendingListCleanupSize(index);
434+
if (metadata->nPendingPages * GIN_PAGE_FREESIZE > cleanupSize * 1024L)
430435
needCleanup = true;
431436

432437
UnlockReleaseBuffer(metabuffer);

‎src/backend/access/gin/ginutil.c

Copy file name to clipboardExpand all lines: src/backend/access/gin/ginutil.c
+3-1Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -525,7 +525,9 @@ ginoptions(PG_FUNCTION_ARGS)
525525
GinOptions *rdopts;
526526
int numoptions;
527527
static const relopt_parse_elt tab[] = {
528-
{"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)}
528+
{"fastupdate", RELOPT_TYPE_BOOL, offsetof(GinOptions, useFastUpdate)},
529+
{"pending_list_cleanup_size", RELOPT_TYPE_INT, offsetof(GinOptions,
530+
pendingListCleanupSize)}
529531
};
530532

531533
options = parseRelOptions(reloptions, validate, RELOPT_KIND_GIN,

‎src/backend/utils/misc/guc.c

Copy file name to clipboardExpand all lines: src/backend/utils/misc/guc.c
+11-8Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -96,14 +96,6 @@
9696
#define CONFIG_EXEC_PARAMS_NEW "global/config_exec_params.new"
9797
#endif
9898

99-
/* upper limit for GUC variables measured in kilobytes of memory */
100-
/* note that various places assume the byte size fits in a "long" variable */
101-
#if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4
102-
#define MAX_KILOBYTES INT_MAX
103-
#else
104-
#define MAX_KILOBYTES (INT_MAX / 1024)
105-
#endif
106-
10799
#define KB_PER_MB (1024)
108100
#define KB_PER_GB (1024*1024)
109101
#define KB_PER_TB (1024*1024*1024)
@@ -2550,6 +2542,17 @@ static struct config_int ConfigureNamesInt[] =
25502542
NULL, NULL, NULL
25512543
},
25522544

2545+
{
2546+
{"pending_list_cleanup_size", PGC_USERSET, CLIENT_CONN_STATEMENT,
2547+
gettext_noop("Sets the maximum size of the pending list for GIN index."),
2548+
NULL,
2549+
GUC_UNIT_KB
2550+
},
2551+
&pending_list_cleanup_size,
2552+
4096, 64, MAX_KILOBYTES,
2553+
NULL, NULL, NULL
2554+
},
2555+
25532556
/* End-of-list marker */
25542557
{
25552558
{NULL, 0, 0, NULL, NULL}, NULL, 0, 0, 0, NULL, NULL, NULL

‎src/backend/utils/misc/postgresql.conf.sample

Copy file name to clipboardExpand all lines: src/backend/utils/misc/postgresql.conf.sample
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -519,6 +519,7 @@
519519
#bytea_output = 'hex' # hex, escape
520520
#xmlbinary = 'base64'
521521
#xmloption = 'content'
522+
#pending_list_cleanup_size = 4MB
522523

523524
# - Locale and Formatting -
524525

‎src/bin/psql/tab-complete.c

Copy file name to clipboardExpand all lines: src/bin/psql/tab-complete.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1172,7 +1172,7 @@ psql_completion(const char *text, int start, int end)
11721172
pg_strcasecmp(prev_wd, "(") == 0)
11731173
{
11741174
static const char *const list_INDEXOPTIONS[] =
1175-
{"fillfactor", "fastupdate", NULL};
1175+
{"fillfactor", "fastupdate", "pending_list_cleanup_size", NULL};
11761176

11771177
COMPLETE_WITH_LIST(list_INDEXOPTIONS);
11781178
}

‎src/include/access/gin.h

Copy file name to clipboardExpand all lines: src/include/access/gin.h
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -65,8 +65,9 @@ typedef char GinTernaryValue;
6565
#define GinTernaryValueGetDatum(X) ((Datum)(X))
6666
#define PG_RETURN_GIN_TERNARY_VALUE(x) return GinTernaryValueGetDatum(x)
6767

68-
/* GUC parameter */
68+
/* GUC parameters */
6969
extern PGDLLIMPORT int GinFuzzySearchLimit;
70+
extern int pending_list_cleanup_size;
7071

7172
/* ginutil.c */
7273
extern void ginGetStats(Relation index, GinStatsData *stats);

‎src/include/access/gin_private.h

Copy file name to clipboardExpand all lines: src/include/access/gin_private.h
+6Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -315,12 +315,18 @@ typedef struct GinOptions
315315
{
316316
int32 vl_len_; /* varlena header (do not touch directly!) */
317317
bool useFastUpdate; /* use fast updates? */
318+
int pendingListCleanupSize; /* maximum size of pending list */
318319
} GinOptions;
319320

320321
#define GIN_DEFAULT_USE_FASTUPDATE true
321322
#define GinGetUseFastUpdate(relation) \
322323
((relation)->rd_options ? \
323324
((GinOptions *) (relation)->rd_options)->useFastUpdate : GIN_DEFAULT_USE_FASTUPDATE)
325+
#define GinGetPendingListCleanupSize(relation) \
326+
((relation)->rd_options && \
327+
((GinOptions *) (relation)->rd_options)->pendingListCleanupSize != -1 ? \
328+
((GinOptions *) (relation)->rd_options)->pendingListCleanupSize : \
329+
pending_list_cleanup_size)
324330

325331

326332
/* Macros for buffer lock/unlock operations */

‎src/include/utils/guc.h

Copy file name to clipboardExpand all lines: src/include/utils/guc.h
+8Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,14 @@
1818
#include "utils/array.h"
1919

2020

21+
/* upper limit for GUC variables measured in kilobytes of memory */
22+
/* note that various places assume the byte size fits in a "long" variable */
23+
#if SIZEOF_SIZE_T > 4 && SIZEOF_LONG > 4
24+
#define MAX_KILOBYTES INT_MAX
25+
#else
26+
#define MAX_KILOBYTES (INT_MAX / 1024)
27+
#endif
28+
2129
/*
2230
* Certain options can only be set at certain times. The rules are
2331
* like this:

‎src/test/regress/expected/create_index.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/create_index.out
+13Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2240,6 +2240,19 @@ SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}';
22402240
(1 row)
22412241

22422242
DROP TABLE array_gin_test;
2243+
--
2244+
-- Test GIN index's reloptions
2245+
--
2246+
CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i)
2247+
WITH (FASTUPDATE=on, PENDING_LIST_CLEANUP_SIZE=128);
2248+
\d+ gin_relopts_test
2249+
Index "public.gin_relopts_test"
2250+
Column | Type | Definition | Storage
2251+
--------+---------+------------+---------
2252+
i | integer | i | plain
2253+
gin, for table "public.array_index_op_test"
2254+
Options: fastupdate=on, pending_list_cleanup_size=128
2255+
22432256
--
22442257
-- HASH
22452258
--

‎src/test/regress/sql/create_index.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/create_index.sql
+7Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -654,6 +654,13 @@ SELECT COUNT(*) FROM array_gin_test WHERE a @> '{2}';
654654

655655
DROP TABLE array_gin_test;
656656

657+
--
658+
-- Test GIN index's reloptions
659+
--
660+
CREATE INDEX gin_relopts_test ON array_index_op_test USING gin (i)
661+
WITH (FASTUPDATE=on, PENDING_LIST_CLEANUP_SIZE=128);
662+
\d+ gin_relopts_test
663+
657664
--
658665
-- HASH
659666
--

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.