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

Commitff301d6

Browse files
committed
Implement "fastupdate" support for GIN indexes, in which we try to accumulate
multiple index entries in a holding area before adding them to the main indexstructure. This helps because bulk insert is (usually) significantly fasterthan retail insert for GIN.This patch also removes GIN support for amgettuple-style index scans. TheAPI defined for amgettuple is difficult to support with fastupdate, andthe previously committed partial-match feature didn't really work withit either. We might eventually figure a way to put back amgettuplesupport, but it won't happen for 8.4.catversion bumped because of change in GIN's pg_am entry, and becausethe format of GIN indexes changed on-disk (there's a metapage now,and possibly a pending list).Teodor Sigaev
1 parent9987f66 commitff301d6

File tree

30 files changed

+2011
-176
lines changed

30 files changed

+2011
-176
lines changed

‎doc/src/sgml/gin.sgml

Lines changed: 69 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/gin.sgml,v 2.16 2008/07/22 22:05:24 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/gin.sgml,v 2.17 2009/03/24 20:17:07 tgl Exp $ -->
22

33
<chapter id="GIN">
44
<title>GIN Indexes</title>
@@ -100,11 +100,11 @@
100100
to consult <literal>n</> to determine the data type of
101101
<literal>query</> and the key values that need to be extracted.
102102
The number of returned keys must be stored into <literal>*nkeys</>.
103-
If the query contains no keys then <function>extractQuery</>
103+
If the query contains no keys then <function>extractQuery</>
104104
should store 0 or -1 into <literal>*nkeys</>, depending on the
105105
semantics of the operator. 0 means that every
106-
value matches the <literal>query</> and a sequential scan should be
107-
produced. -1 means nothing can match the <literal>query</>.
106+
value matches the <literal>query</> and a sequential scan should be
107+
produced. -1 means nothing can match the <literal>query</>.
108108
<literal>pmatch</> is an output argument for use when partial match
109109
is supported. To use it, <function>extractQuery</> must allocate
110110
an array of <literal>*nkeys</> booleans and store its address at
@@ -188,9 +188,47 @@
188188
list of heap pointers (PL, posting list) if the list is small enough.
189189
</para>
190190

191+
<sect2 id="gin-fast-update">
192+
<title>GIN fast update technique</title>
193+
194+
<para>
195+
Updating a <acronym>GIN</acronym> index tends to be slow because of the
196+
intrinsic nature of inverted indexes: inserting or updating one heap row
197+
can cause many inserts into the index (one for each key extracted
198+
from the indexed value). As of <productname>PostgreSQL</productname> 8.4,
199+
<acronym>GIN</> is capable of postponing much of this work by inserting
200+
new tuples into a temporary, unsorted list of pending entries.
201+
When the table is vacuumed, or if the pending list becomes too large
202+
(larger than <xref linkend="guc-work-mem">), the entries are moved to the
203+
main <acronym>GIN</acronym> data structure using the same bulk insert
204+
techniques used during initial index creation. This greatly improves
205+
<acronym>GIN</acronym> index update speed, even counting the additional
206+
vacuum overhead. Moreover the overhead can be done by a background
207+
process instead of in foreground query processing.
208+
</para>
209+
210+
<para>
211+
The main disadvantage of this approach is that searches must scan the list
212+
of pending entries in addition to searching the regular index, and so
213+
a large list of pending entries will slow searches significantly.
214+
Another disadvantage is that, while most updates are fast, an update
215+
that causes the pending list to become <quote>too large</> will incur an
216+
immediate cleanup cycle and thus be much slower than other updates.
217+
Proper use of autovacuum can minimize both of these problems.
218+
</para>
219+
220+
<para>
221+
If consistent response time is more important than update speed,
222+
use of pending entries can be disabled by turning off the
223+
<literal>FASTUPDATE</literal> storage parameter for a
224+
<acronym>GIN</acronym> index. See <xref linkend="sql-createindex"
225+
endterm="sql-createindex-title"> for details.
226+
</para>
227+
</sect2>
228+
191229
<sect2 id="gin-partial-match">
192230
<title>Partial match algorithm</title>
193-
231+
194232
<para>
195233
GIN can support <quote>partial match</> queries, in which the query
196234
does not determine an exact match for one or more keys, but the possible
@@ -205,14 +243,6 @@
205243
to be searched, or greater than zero if the index key is past the range
206244
that could match.
207245
</para>
208-
209-
<para>
210-
During a partial-match scan, all <literal>itemPointer</>s for matching keys
211-
are OR'ed into a <literal>TIDBitmap</>.
212-
The scan fails if the <literal>TIDBitmap</> becomes lossy.
213-
In this case an error message will be reported with advice
214-
to increase <literal>work_mem</>.
215-
</para>
216246
</sect2>
217247

218248
</sect1>
@@ -225,11 +255,18 @@
225255
<term>Create vs insert</term>
226256
<listitem>
227257
<para>
228-
In most cases, insertioninto a <acronym>GIN</acronym> indexis slow
258+
Insertioninto a <acronym>GIN</acronym> indexcan be slow
229259
due to the likelihood of many keys being inserted for each value.
230260
So, for bulk insertions into a table it is advisable to drop the GIN
231261
index and recreate it after finishing bulk insertion.
232262
</para>
263+
264+
<para>
265+
As of <productname>PostgreSQL</productname> 8.4, this advice is less
266+
necessary since delayed indexing is used (see <xref
267+
linkend="gin-fast-update"> for details). But for very large updates
268+
it may still be best to drop and recreate the index.
269+
</para>
233270
</listitem>
234271
</varlistentry>
235272

@@ -244,6 +281,23 @@
244281
</listitem>
245282
</varlistentry>
246283

284+
<varlistentry>
285+
<term><xref linkend="guc-work-mem"></term>
286+
<listitem>
287+
<para>
288+
During a series of insertions into an existing <acronym>GIN</acronym>
289+
index that has <literal>FASTUPDATE</> enabled, the system will clean up
290+
the pending-entry list whenever it grows larger than
291+
<varname>work_mem</>. To avoid fluctuations in observed response time,
292+
it's desirable to have pending-list cleanup occur in the background
293+
(i.e., via autovacuum). Foreground cleanup operations can be avoided by
294+
increasing <varname>work_mem</> or making autovacuum more aggressive.
295+
However, enlarging <varname>work_mem</> means that if a foreground
296+
cleanup does occur, it will take even longer.
297+
</para>
298+
</listitem>
299+
</varlistentry>
300+
247301
<varlistentry>
248302
<term><xref linkend="guc-gin-fuzzy-search-limit"></term>
249303
<listitem>
@@ -311,8 +365,7 @@
311365
<function>extractQuery</function> must convert an unrestricted search into
312366
a partial-match query that will scan the whole index. This is inefficient
313367
but might be necessary to avoid corner-case failures with operators such
314-
as LIKE. Note however that failure could still occur if the intermediate
315-
<literal>TIDBitmap</> becomes lossy.
368+
as <literal>LIKE</>.
316369
</para>
317370
</sect1>
318371

‎doc/src/sgml/indexam.sgml

Lines changed: 19 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.29 2009/03/05 23:06:45 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.30 2009/03/24 20:17:08 tgl Exp $ -->
22

33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
@@ -79,7 +79,7 @@
7979
</para>
8080

8181
<para>
82-
An individual index is defined by a
82+
An individual index is defined by a
8383
<link linkend="catalog-pg-class"><structname>pg_class</structname></link>
8484
entry that describes it as a physical relation, plus a
8585
<link linkend="catalog-pg-index"><structname>pg_index</structname></link>
@@ -239,6 +239,16 @@ amvacuumcleanup (IndexVacuumInfo *info,
239239
be returned.
240240
</para>
241241

242+
<para>
243+
As of <productname>PostgreSQL</productname> 8.4,
244+
<function>amvacuumcleanup</> will also be called at completion of an
245+
<command>ANALYZE</> operation. In this case <literal>stats</> is always
246+
NULL and any return value will be ignored. This case can be distinguished
247+
by checking <literal>info-&gt;analyze_only</literal>. It is recommended
248+
that the access method do nothing except post-insert cleanup in such a
249+
call, and that only in an autovacuum worker process.
250+
</para>
251+
242252
<para>
243253
<programlisting>
244254
void
@@ -344,7 +354,8 @@ amgetbitmap (IndexScanDesc scan,
344354
</programlisting>
345355
Fetch all tuples in the given scan and add them to the caller-supplied
346356
TIDBitmap (that is, OR the set of tuple IDs into whatever set is already
347-
in the bitmap). The number of tuples fetched is returned.
357+
in the bitmap). The number of tuples fetched is returned (this might be
358+
just an approximate count, for instance some AMs do not detect duplicates).
348359
While inserting tuple IDs into the bitmap, <function>amgetbitmap</> can
349360
indicate that rechecking of the scan conditions is required for specific
350361
tuple IDs. This is analogous to the <literal>xs_recheck</> output parameter
@@ -521,14 +532,14 @@ amrestrpos (IndexScanDesc scan);
521532
</para>
522533

523534
<para>
524-
Instead of using <function>amgettuple</>, an index scan can be done with
535+
Instead of using <function>amgettuple</>, an index scan can be done with
525536
<function>amgetbitmap</> to fetch all tuples in one call. This can be
526537
noticeably more efficient than <function>amgettuple</> because it allows
527538
avoiding lock/unlock cycles within the access method. In principle
528539
<function>amgetbitmap</> should have the same effects as repeated
529540
<function>amgettuple</> calls, but we impose several restrictions to
530-
simplify matters. First of all, <function>amgetbitmap</> returns all
531-
tuples at once and marking or restoring scan positions isn't
541+
simplify matters. First of all, <function>amgetbitmap</> returns all
542+
tuples at once and marking or restoring scan positions isn't
532543
supported. Secondly, the tuples are returned in a bitmap which doesn't
533544
have any specific ordering, which is why <function>amgetbitmap</> doesn't
534545
take a <literal>direction</> argument. Finally, <function>amgetbitmap</>
@@ -572,7 +583,7 @@ amrestrpos (IndexScanDesc scan);
572583
Aside from the index's own internal consistency requirements, concurrent
573584
updates create issues about consistency between the parent table (the
574585
<firstterm>heap</>) and the index. Because
575-
<productname>PostgreSQL</productname> separates accesses
586+
<productname>PostgreSQL</productname> separates accesses
576587
and updates of the heap from those of the index, there are windows in
577588
which the index might be inconsistent with the heap. We handle this problem
578589
with the following rules:
@@ -701,7 +712,7 @@ amrestrpos (IndexScanDesc scan);
701712
no error should be raised. (This case cannot occur during the
702713
ordinary scenario of inserting a row that's just been created by
703714
the current transaction. It can happen during
704-
<command>CREATE UNIQUE INDEX CONCURRENTLY</>, however.)
715+
<command>CREATE UNIQUE INDEX CONCURRENTLY</>, however.)
705716
</para>
706717

707718
<para>

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

Lines changed: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.70 2009/02/02 19:31:38 alvherre Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.71 2009/03/24 20:17:08 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -294,6 +294,37 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] <replaceable class="parameter">name</re
294294

295295
</variablelist>
296296

297+
<para>
298+
<literal>GIN</literal> indexes accept a different parameter:
299+
</para>
300+
301+
<variablelist>
302+
303+
<varlistentry>
304+
<term><literal>FASTUPDATE</></term>
305+
<listitem>
306+
<para>
307+
This setting controls usage of the fast update technique described in
308+
<xref linkend="gin-fast-update">. It is a Boolean parameter:
309+
<literal>ON</> enables fast update, <literal>OFF</> disables it.
310+
(Alternative spellings of <literal>ON</> and <literal>OFF</> are
311+
allowed as described in <xref linkend="config-setting">.) The
312+
default is <literal>ON</>.
313+
</para>
314+
315+
<note>
316+
<para>
317+
Turning <literal>FASTUPDATE</> off via <command>ALTER INDEX</> prevents
318+
future insertions from going into the list of pending index entries,
319+
but does not in itself flush previous entries. You might want to
320+
<command>VACUUM</> the table afterward to ensure the pending list is
321+
emptied.
322+
</para>
323+
</note>
324+
</listitem>
325+
</varlistentry>
326+
327+
</variablelist>
297328
</refsect2>
298329

299330
<refsect2 id="SQL-CREATEINDEX-CONCURRENTLY">
@@ -501,6 +532,13 @@ CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
501532
</programlisting>
502533
</para>
503534

535+
<para>
536+
To create a <acronym>GIN</> index with fast updates disabled:
537+
<programlisting>
538+
CREATE INDEX gin_idx ON documents_table (locations) WITH (fastupdate = off);
539+
</programlisting>
540+
</para>
541+
504542
<para>
505543
To create an index on the column <literal>code</> in the table
506544
<literal>films</> and have the index reside in the tablespace

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.54 2008/12/11 18:16:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/vacuum.sgml,v 1.55 2009/03/24 20:17:08 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -160,6 +160,13 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">
160160
<command>VACUUM</> cannot be executed inside a transaction block.
161161
</para>
162162

163+
<para>
164+
For tables with <acronym>GIN</> indexes, <command>VACUUM</command> (in
165+
any form) also completes any pending index insertions, by moving pending
166+
index entries to the appropriate places in the main <acronym>GIN</> index
167+
structure. See <xref linkend="gin-fast-update"> for details.
168+
</para>
169+
163170
<para>
164171
We recommend that active production databases be
165172
vacuumed frequently (at least nightly), in order to

‎doc/src/sgml/textsearch.sgml

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/textsearch.sgml,v 1.47 2009/01/07 22:40:49 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/textsearch.sgml,v 1.48 2009/03/24 20:17:08 tgl Exp $ -->
22

33
<chapter id="textsearch">
44
<title id="textsearch-title">Full Text Search</title>
@@ -3224,12 +3224,14 @@ SELECT plainto_tsquery('supernovae stars');
32243224
</listitem>
32253225
<listitem>
32263226
<para>
3227-
GIN indexes are about ten times slower to update than GiST
3227+
GIN indexes are moderately slower to update than GiST indexes, but
3228+
about 10 times slower if fast-update support was disabled
3229+
(see <xref linkend="gin-fast-update"> for details)
32283230
</para>
32293231
</listitem>
32303232
<listitem>
32313233
<para>
3232-
GIN indexes are two-to-three times larger than GiST
3234+
GIN indexes are two-to-three times larger than GiST indexes
32333235
</para>
32343236
</listitem>
32353237
</itemizedlist>

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

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.23 2009/03/23 16:36:27 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/common/reloptions.c,v 1.24 2009/03/24 20:17:09 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -56,6 +56,14 @@ static relopt_bool boolRelOpts[] =
5656
},
5757
true
5858
},
59+
{
60+
{
61+
"fastupdate",
62+
"Enables \"fast update\" feature for this GIN index",
63+
RELOPT_KIND_GIN
64+
},
65+
true
66+
},
5967
/* list terminator */
6068
{ {NULL } }
6169
};

‎src/backend/access/gin/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
# Makefile for access/gin
55
#
66
# IDENTIFICATION
7-
# $PostgreSQL: pgsql/src/backend/access/gin/Makefile,v 1.3 2008/02/19 10:30:06 petere Exp $
7+
# $PostgreSQL: pgsql/src/backend/access/gin/Makefile,v 1.4 2009/03/24 20:17:10 tgl Exp $
88
#
99
#-------------------------------------------------------------------------
1010

@@ -14,6 +14,6 @@ include $(top_builddir)/src/Makefile.global
1414

1515
OBJS = ginutil.o gininsert.o ginxlog.o ginentrypage.o gindatapage.o\
1616
ginbtree.o ginscan.o ginget.o ginvacuum.o ginarrayproc.o\
17-
ginbulk.o
17+
ginbulk.o ginfast.o
1818

1919
include$(top_srcdir)/src/backend/common.mk

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
*$PostgreSQL: pgsql/src/backend/access/gin/ginbulk.c,v 1.14 2009/01/0117:23:34 momjian Exp $
11+
*$PostgreSQL: pgsql/src/backend/access/gin/ginbulk.c,v 1.15 2009/03/24 20:17:10 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -197,6 +197,8 @@ ginInsertRecordBA(BuildAccumulator *accum, ItemPointer heapptr, OffsetNumber att
197197
if (nentry <=0)
198198
return;
199199

200+
Assert(ItemPointerIsValid(heapptr)&&attnum >=FirstOffsetNumber);
201+
200202
i=nentry-1;
201203
for (;i>0;i >>=1)
202204
nbit++;

0 commit comments

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