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

Commit7f46eaf

Browse files
committed
Add gin_clean_pending_list function to clean up GIN pending list
This function cleans up the pending list of the GIN index bymoving entries in it to the main GIN data structure in bulk.It returns the number of pages cleaned up from the pending list.This function is useful, for example, when the pending listneeds to be cleaned up *quickly* to improve the performance ofthe search using GIN index. VACUUM can do the same thing, too,but it may take days to run on a large table.Jeff Janes,reviewed by Julien Rouhaud, Jaime Casanova, Alvaro Herrera and me.Discussion: CAMkU=1x8zFkpfnozXyt40zmR3Ub_kHu58LtRmwHUKRgQss7=iQ@mail.gmail.com
1 parenteaf7b1f commit7f46eaf

File tree

9 files changed

+108
-4
lines changed

9 files changed

+108
-4
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18036,9 +18036,16 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1803618036
<primary>brin_summarize_new_values</primary>
1803718037
</indexterm>
1803818038

18039+
<indexterm>
18040+
<primary>gin_clean_pending_list</primary>
18041+
</indexterm>
18042+
1803918043
<para>
1804018044
<xref linkend="functions-admin-index-table"> shows the functions
1804118045
available for index maintenance tasks.
18046+
These functions cannot be executed during recovery.
18047+
Use of these functions is restricted to superusers and the owner
18048+
of the given index.
1804218049
</para>
1804318050

1804418051
<table id="functions-admin-index-table">
@@ -18056,6 +18063,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1805618063
<entry><type>integer</type></entry>
1805718064
<entry>summarize page ranges not already summarized</entry>
1805818065
</row>
18066+
<row>
18067+
<entry>
18068+
<literal><function>gin_clean_pending_list(<parameter>index</> <type>regclass</>)</function></literal>
18069+
</entry>
18070+
<entry><type>bigint</type></entry>
18071+
<entry>move GIN pending list entries into main index structure</entry>
18072+
</row>
1805918073
</tbody>
1806018074
</tgroup>
1806118075
</table>
@@ -18069,6 +18083,18 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1806918083
into the index.
1807018084
</para>
1807118085

18086+
<para>
18087+
<function>gin_clean_pending_list</> accepts the OID or name of
18088+
a GIN index and cleans up the pending list of the specified GIN index
18089+
by moving entries in it to the main GIN data structure in bulk.
18090+
It returns the number of pages cleaned up from the pending list.
18091+
Note that if the argument is a GIN index built with <literal>fastupdate</>
18092+
option disabled, the cleanup does not happen and the return value is 0
18093+
because the index doesn't have a pending list.
18094+
Please see <xref linkend="gin-fast-update"> and <xref linkend="gin-tips">
18095+
for details of the pending list and <literal>fastupdate</> option.
18096+
</para>
18097+
1807218098
</sect2>
1807318099

1807418100
<sect2 id="functions-admin-genfile">

‎doc/src/sgml/gin.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -734,7 +734,9 @@
734734
from the indexed item). As of <productname>PostgreSQL</productname> 8.4,
735735
<acronym>GIN</> is capable of postponing much of this work by inserting
736736
new tuples into a temporary, unsorted list of pending entries.
737-
When the table is vacuumed, or if the pending list becomes larger than
737+
When the table is vacuumed or autoanalyzed, or when
738+
<function>gin_clean_pending_list</function> function is called, or if the
739+
pending list becomes larger than
738740
<xref linkend="guc-gin-pending-list-limit">, the entries are moved to the
739741
main <acronym>GIN</acronym> data structure using the same bulk insert
740742
techniques used during initial index creation. This greatly improves

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -362,8 +362,8 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
362362
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
365-
<command>VACUUM</> the tableafterward to ensure the pending list is
366-
emptied.
365+
<command>VACUUM</> the tableor call <function>gin_clean_pending_list</>
366+
function afterward to ensure the pending list isemptied.
367367
</para>
368368
</note>
369369
</listitem>

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

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,10 +20,13 @@
2020

2121
#include"access/gin_private.h"
2222
#include"access/xloginsert.h"
23+
#include"access/xlog.h"
2324
#include"commands/vacuum.h"
25+
#include"catalog/pg_am.h"
2426
#include"miscadmin.h"
2527
#include"utils/memutils.h"
2628
#include"utils/rel.h"
29+
#include"utils/acl.h"
2730
#include"storage/indexfsm.h"
2831

2932
/* GUC parameter */
@@ -958,3 +961,52 @@ ginInsertCleanup(GinState *ginstate,
958961
MemoryContextSwitchTo(oldCtx);
959962
MemoryContextDelete(opCtx);
960963
}
964+
965+
/*
966+
* SQL-callable function to clean the insert pending list
967+
*/
968+
Datum
969+
gin_clean_pending_list(PG_FUNCTION_ARGS)
970+
{
971+
Oidindexoid=PG_GETARG_OID(0);
972+
RelationindexRel=index_open(indexoid,AccessShareLock);
973+
IndexBulkDeleteResultstats;
974+
GinStateginstate;
975+
976+
if (RecoveryInProgress())
977+
ereport(ERROR,
978+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
979+
errmsg("recovery is in progress"),
980+
errhint("GIN pending list cannot be cleaned up during recovery.")));
981+
982+
/* Must be a GIN index */
983+
if (indexRel->rd_rel->relkind!=RELKIND_INDEX||
984+
indexRel->rd_rel->relam!=GIN_AM_OID)
985+
ereport(ERROR,
986+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
987+
errmsg("\"%s\" is not a GIN index",
988+
RelationGetRelationName(indexRel))));
989+
990+
/*
991+
* Reject attempts to read non-local temporary relations; we would be
992+
* likely to get wrong data since we have no visibility into the owning
993+
* session's local buffers.
994+
*/
995+
if (RELATION_IS_OTHER_TEMP(indexRel))
996+
ereport(ERROR,
997+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
998+
errmsg("cannot access temporary indexes of other sessions")));
999+
1000+
/* User must own the index (comparable to privileges needed for VACUUM) */
1001+
if (!pg_class_ownercheck(indexoid,GetUserId()))
1002+
aclcheck_error(ACLCHECK_NOT_OWNER,ACL_KIND_CLASS,
1003+
RelationGetRelationName(indexRel));
1004+
1005+
memset(&stats,0,sizeof(stats));
1006+
initGinState(&ginstate,indexRel);
1007+
ginInsertCleanup(&ginstate, true,&stats);
1008+
1009+
index_close(indexRel,AccessShareLock);
1010+
1011+
PG_RETURN_INT64((int64)stats.pages_deleted);
1012+
}

‎src/include/access/gin_private.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -881,6 +881,9 @@ extern void ginFreeScanKeys(GinScanOpaque so);
881881
/* ginget.c */
882882
externint64gingetbitmap(IndexScanDescscan,TIDBitmap*tbm);
883883

884+
/* ginfast.c */
885+
externDatumgin_clean_pending_list(PG_FUNCTION_ARGS);
886+
884887
/* ginlogic.c */
885888
externvoidginInitConsistentFunction(GinState*ginstate,GinScanKeykey);
886889

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201601271
56+
#defineCATALOG_VERSION_NO201601281
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4517,6 +4517,8 @@ DATA(insert OID = 3087 ( gin_extract_tsqueryPGNSP PGUID 12 1 0 0 0 f f f f t f
45174517
DESCR("GIN tsvector support (obsolete)");
45184518
DATA(insert OID = 3088 ( gin_tsquery_consistent PGNSP PGUID 12 1 0 0 0 f f f f t f i s 6 0 16 "2281 21 3615 23 2281 2281" _null_ _null_ _null_ _null_ _null_ gin_tsquery_consistent_6args _null_ _null_ _null_ ));
45194519
DESCR("GIN tsvector support (obsolete)");
4520+
DATA(insert OID = 3789 ( gin_clean_pending_list PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ gin_clean_pending_list _null_ _null_ _null_ ));
4521+
DESCR("clean up GIN pending list");
45204522

45214523
DATA(insert OID = 3662 ( tsquery_ltPGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3615 3615" _null_ _null_ _null_ _null_ _null_ tsquery_lt _null_ _null_ _null_ ));
45224524
DATA(insert OID = 3663 ( tsquery_lePGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "3615 3615" _null_ _null_ _null_ _null_ _null_ tsquery_le _null_ _null_ _null_ ));

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,20 @@ create table gin_test_tbl(i int4[]);
88
create index gin_test_idx on gin_test_tbl using gin (i) with (fastupdate = on);
99
insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 20000) g;
1010
insert into gin_test_tbl select array[1, 3, g] from generate_series(1, 1000) g;
11+
select gin_clean_pending_list('gin_test_idx')>10 as many; -- flush the fastupdate buffers
12+
many
13+
------
14+
t
15+
(1 row)
16+
17+
insert into gin_test_tbl select array[3, 1, g] from generate_series(1, 1000) g;
1118
vacuum gin_test_tbl; -- flush the fastupdate buffers
19+
select gin_clean_pending_list('gin_test_idx'); -- nothing to flush
20+
gin_clean_pending_list
21+
------------------------
22+
0
23+
(1 row)
24+
1225
-- Test vacuuming
1326
delete from gin_test_tbl where i @> array[2];
1427
vacuum gin_test_tbl;

‎src/test/regress/sql/gin.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,8 +10,14 @@ create index gin_test_idx on gin_test_tbl using gin (i) with (fastupdate = on);
1010
insert into gin_test_tblselect array[1,2, g]from generate_series(1,20000) g;
1111
insert into gin_test_tblselect array[1,3, g]from generate_series(1,1000) g;
1212

13+
select gin_clean_pending_list('gin_test_idx')>10as many;-- flush the fastupdate buffers
14+
15+
insert into gin_test_tblselect array[3,1, g]from generate_series(1,1000) g;
16+
1317
vacuum gin_test_tbl;-- flush the fastupdate buffers
1418

19+
select gin_clean_pending_list('gin_test_idx');-- nothing to flush
20+
1521
-- Test vacuuming
1622
deletefrom gin_test_tblwhere i @> array[2];
1723
vacuum gin_test_tbl;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp