Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commita1b395b

Browse files
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 bywork_mem. But the reasonable value of work_mem and the reasonable sizeof the list are basically not the same, so it was not appropriate tocontrol both of them by only one GUC, i.e., work_mem. This commitseparates new GUC, pending_list_cleanup_size, from work_mem to allowusers to control only the size of the list.Also this commit adds pending_list_cleanup_size as new storage parameterto 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 forthe GIN index which can be updated heavily, and decrease it otherwise.Reviewed by Etsuro Fujita.
1 parentae667f7 commita1b395b

File tree

16 files changed

+125
-31
lines changed

16 files changed

+125
-31
lines changed

‎doc/src/sgml/config.sgml

Lines 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

Lines 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 becomestoo 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 becomeslarger 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

Lines 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

Lines 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

Lines 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 acceptadifferentparameter:
344+
GIN indexes accept differentparameters:
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

Lines 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

Lines 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+
intpending_list_cleanup_size=0;
2830

2931
#defineGIN_PAGE_FREESIZE \
3032
( BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - MAXALIGN(sizeof(GinPageOpaqueData)) )
@@ -228,6 +230,7 @@ ginHeapTupleFastInsert(GinState *ginstate, GinTupleCollector *collector)
228230
ginxlogUpdateMetadata;
229231
boolseparateList= false;
230232
boolneedCleanup= false;
233+
intcleanupSize;
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

Lines 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
intnumoptions;
527527
staticconstrelopt_parse_elttab[]= {
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

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -96,14 +96,6 @@
9696
#defineCONFIG_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-
#ifSIZEOF_SIZE_T>4&&SIZEOF_LONG>4
102-
#defineMAX_KILOBYTESINT_MAX
103-
#else
104-
#defineMAX_KILOBYTES(INT_MAX / 1024)
105-
#endif
106-
10799
#defineKB_PER_MB (1024)
108100
#defineKB_PER_GB (1024*1024)
109101
#defineKB_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

Lines 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

Lines 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
staticconstchar*constlist_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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -65,8 +65,9 @@ typedef char GinTernaryValue;
6565
#defineGinTernaryValueGetDatum(X) ((Datum)(X))
6666
#definePG_RETURN_GIN_TERNARY_VALUE(x) return GinTernaryValueGetDatum(x)
6767

68-
/* GUCparameter */
68+
/* GUCparameters */
6969
externPGDLLIMPORTintGinFuzzySearchLimit;
70+
externintpending_list_cleanup_size;
7071

7172
/* ginutil.c */
7273
externvoidginGetStats(Relationindex,GinStatsData*stats);

‎src/include/access/gin_private.h

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -315,12 +315,18 @@ typedef struct GinOptions
315315
{
316316
int32vl_len_;/* varlena header (do not touch directly!) */
317317
booluseFastUpdate;/* use fast updates? */
318+
intpendingListCleanupSize;/* maximum size of pending list */
318319
}GinOptions;
319320

320321
#defineGIN_DEFAULT_USE_FASTUPDATEtrue
321322
#defineGinGetUseFastUpdate(relation) \
322323
((relation)->rd_options ? \
323324
((GinOptions *) (relation)->rd_options)->useFastUpdate : GIN_DEFAULT_USE_FASTUPDATE)
325+
#defineGinGetPendingListCleanupSize(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

Lines 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+
#ifSIZEOF_SIZE_T>4&&SIZEOF_LONG>4
24+
#defineMAX_KILOBYTESINT_MAX
25+
#else
26+
#defineMAX_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

Lines 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

Lines 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
DROPTABLE array_gin_test;
656656

657+
--
658+
-- Test GIN index's reloptions
659+
--
660+
CREATEINDEXgin_relopts_testON 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)
[8]ページ先頭
©2009-2025 Movatter.jp