Skip to content

Commit 9bd7ed8

Browse files
tglsfdctglsfdc
committed
Clean up some obsolete statements about GiST indexes, and add a section
documenting GiST crash recovery procedures, as requested some time ago by Teodor. (The GiST chapter doesn't seem quite the right place for the latter, but I'm not sure what else to do with it.)
1 parent d1959f9 commit 9bd7ed8

File tree

3 files changed

+99
-53
lines changed

3 files changed

+99
-53
lines changed

‎doc/src/sgml/gist.sgml

Lines changed: 47 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,11 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.22 2005/10/21 01:41:28 tgl Exp $
33
-->
44

55
<chapter id="GiST">
66
<title>GiST Indexes</title>
77

8-
<sect1 id="intro">
8+
<sect1 id="gist-intro">
99
<title>Introduction</title>
1010

1111
<para>
@@ -44,7 +44,7 @@ $PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp
4444

4545
</sect1>
4646

47-
<sect1 id="extensibility">
47+
<sect1 id="gist-extensibility">
4848
<title>Extensibility</title>
4949

5050
<para>
@@ -92,7 +92,7 @@ $PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp
9292

9393
</sect1>
9494

95-
<sect1 id="implementation">
95+
<sect1 id="gist-implementation">
9696
<title>Implementation</title>
9797

9898
<para>
@@ -180,19 +180,24 @@ $PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp
180180

181181
</sect1>
182182

183-
<sect1 id="examples">
183+
<sect1 id="gist-examples">
184184
<title>Examples</title>
185185

186186
<para>
187-
To see example implementations of index methods implemented using
188-
<acronym>GiST</acronym>, examine the following contrib modules:
187+
The <productname>PostgreSQL</productname> source distribution includes
188+
several examples of index methods implemented using
189+
<acronym>GiST</acronym>. The core system currently provides R-Tree
190+
equivalent functionality for some of the built-in geometric datatypes
191+
(see <filename>src/backend/access/gist/gistproc.c</>). The following
192+
<filename>contrib</> modules also contain <acronym>GiST</acronym>
193+
operator classes:
189194
</para>
190195

191196
<variablelist>
192197
<varlistentry>
193198
<term>btree_gist</term>
194199
<listitem>
195-
<para>B-Tree</para>
200+
<para>B-Tree equivalent functionality for several datatypes</para>
196201
</listitem>
197202
</varlistentry>
198203

@@ -213,26 +218,26 @@ $PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp
213218
<varlistentry>
214219
<term>ltree</term>
215220
<listitem>
216-
<para>Indexing for tree-like stuctures</para>
221+
<para>Indexing for tree-like structures</para>
217222
</listitem>
218223
</varlistentry>
219224

220225
<varlistentry>
221-
<term>rtree_gist</term>
226+
<term>pg_trgm</term>
222227
<listitem>
223-
<para>R-Tree</para>
228+
<para>Text similarity using trigram matching</para>
224229
</listitem>
225230
</varlistentry>
226231

227232
<varlistentry>
228233
<term>seg</term>
229234
<listitem>
230-
<para>Storage and indexed access for <quote>float ranges</quote></para>
235+
<para>Indexing for <quote>float ranges</quote></para>
231236
</listitem>
232237
</varlistentry>
233238

234239
<varlistentry>
235-
<term>tsearch and tsearch2</term>
240+
<term>tsearch2</term>
236241
<listitem>
237242
<para>Full text indexing</para>
238243
</listitem>
@@ -241,4 +246,33 @@ $PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.21 2005/07/02 20:08:27 momjian Exp
241246

242247
</sect1>
243248

249+
<sect1 id="gist-recovery">
250+
<title>Crash Recovery</title>
251+
252+
<para>
253+
Usually, replay of the WAL log is sufficient to restore the integrity
254+
of a GiST index following a database crash. However, there are some
255+
corner cases in which the index state is not fully rebuilt. The index
256+
will still be functionally correct, but there may be some performance
257+
degradation. When this occurs, the index can be repaired by
258+
<command>VACUUM</>ing its table, or by rebuilding the index using
259+
<command>REINDEX</>. In some cases a plain <command>VACUUM</> is
260+
not sufficient, and either <command>VACUUM FULL</> or <command>REINDEX</>
261+
is needed. The need for one of these procedures is indicated by occurrence
262+
of this log message during crash recovery:
263+
<programlisting>
264+
LOG: index NNN/NNN/NNN needs VACUUM or REINDEX to finish crash recovery
265+
</programlisting>
266+
or this log message during routine index insertions:
267+
<programlisting>
268+
LOG: index "FOO" needs VACUUM or REINDEX to finish crash recovery
269+
</programlisting>
270+
If a plain <command>VACUUM</> finds itself unable to complete recovery
271+
fully, it will return a notice:
272+
<programlisting>
273+
NOTICE: index "FOO" needs VACUUM FULL or REINDEX to finish crash recovery
274+
</programlisting>
275+
</para>
276+
</sect1>
277+
244278
</chapter>

‎doc/src/sgml/indices.sgml

Lines changed: 33 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.52 2005/09/12 19:17:45 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.53 2005/10/21 01:41:28 tgl Exp $ -->
22

33
<chapter id="indexes">
44
<title id="indexes-title">Indexes</title>
@@ -206,14 +206,6 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
206206
<synopsis>
207207
CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING hash (<replaceable>column</replaceable>);
208208
</synopsis>
209-
<note>
210-
<para>
211-
Testing has shown <productname>PostgreSQL</productname>'s hash
212-
indexes to perform no better than B-tree indexes, and the
213-
index size and build time for hash indexes is much worse. For
214-
these reasons, hash index use is presently discouraged.
215-
</para>
216-
</note>
217209
</para>
218210

219211
<para>
@@ -226,15 +218,33 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
226218
equivalent to the R-tree operator classes, and many other GiST operator
227219
classes are available in the <literal>contrib</> collection or as separate
228220
projects. For more information see <xref linkend="GiST">.
229-
<note>
230-
<para>
231-
It is likely that the R-tree index type will be retired in a future
232-
release, as GiST indexes appear to do everything R-trees can do with
233-
similar or better performance. Users are encouraged to migrate
234-
applications that use R-tree indexes to GiST indexes.
235-
</para>
236-
</note>
237221
</para>
222+
223+
<note>
224+
<para>
225+
Testing has shown <productname>PostgreSQL</productname>'s hash
226+
indexes to perform no better than B-tree indexes, and the
227+
index size and build time for hash indexes is much worse.
228+
Furthermore, hash index operations are not presently WAL-logged,
229+
so hash indexes may need to be rebuilt with <command>REINDEX</>
230+
after a database crash.
231+
For these reasons, hash index use is presently discouraged.
232+
</para>
233+
234+
<para>
235+
Similarly, R-tree indexes do not seem to have any performance
236+
advantages compared to the equivalent operations of GiST indexes.
237+
Like hash indexes, they are not WAL-logged and may need
238+
<command>REINDEX</>ing after a database crash.
239+
</para>
240+
241+
<para>
242+
While the problems with hash indexes may be fixed eventually,
243+
it is likely that the R-tree index type will be retired in a future
244+
release. Users are encouraged to migrate applications that use R-tree
245+
indexes to GiST indexes.
246+
</para>
247+
</note>
238248
</sect1>
239249

240250

@@ -300,9 +310,12 @@ CREATE INDEX test2_mm_idx ON test2 (major, minor);
300310

301311
<para>
302312
A multicolumn GiST index can only be used when there is a query condition
303-
on its leading column. As with B-trees, conditions on additional columns
304-
restrict the entries returned by the index, but do not in themselves aid
305-
the index search.
313+
on its leading column. Conditions on additional columns restrict the
314+
entries returned by the index, but the condition on the first column is the
315+
most important one for determining how much of the index needs to be
316+
scanned. A GiST index will be relatively ineffective if its first column
317+
has only a few distinct values, even if there are many distinct values in
318+
additional columns.
306319
</para>
307320

308321
<para>

‎doc/src/sgml/mvcc.sgml

Lines changed: 19 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/mvcc.sgml,v 2.51 2005/06/13 02:40:05 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/mvcc.sgml,v 2.52 2005/10/21 01:41:28 tgl Exp $
33
-->
44

55
<chapter id="mvcc">
@@ -965,56 +965,55 @@ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
965965
<variablelist>
966966
<varlistentry>
967967
<term>
968-
B-tree indexes
968+
B-tree and <acronym>GiST</acronym> indexes
969969
</term>
970970
<listitem>
971971
<para>
972-
Short-term share/exclusive page-level locks are used for
973-
read/write access. Locks are released immediately after each
974-
index row is fetched or inserted. B-tree indexes provide
975-
the highest concurrency without deadlock conditions.
972+
Short-term share/exclusive page-level locks are used for
973+
read/write access. Locks are released immediately after each
974+
index row is fetched or inserted. These index types provide
975+
the highest concurrency without deadlock conditions.
976976
</para>
977977
</listitem>
978978
</varlistentry>
979979

980980
<varlistentry>
981981
<term>
982-
<acronym>GiST</acronym> and R-tree indexes
982+
Hash indexes
983983
</term>
984984
<listitem>
985985
<para>
986-
Share/exclusive index-level locks are used for read/write access.
987-
Locks are released after the command is done.
986+
Share/exclusive hash-bucket-level locks are used for read/write
987+
access. Locks are released after the whole bucket is processed.
988+
Bucket-level locks provide better concurrency than index-level
989+
ones, but deadlock is possible since the locks are held longer
990+
than one index operation.
988991
</para>
989992
</listitem>
990993
</varlistentry>
991994

992995
<varlistentry>
993996
<term>
994-
Hash indexes
997+
R-tree indexes
995998
</term>
996999
<listitem>
9971000
<para>
998-
Share/exclusive hash-bucket-level locks are used for read/write
999-
access. Locks are released after the whole bucket is processed.
1000-
Bucket-level locks provide better concurrency than index-level
1001-
ones, but deadlock is possible since the locks are held longer
1002-
than one index operation.
1001+
Share/exclusive index-level locks are used for read/write access.
1002+
Locks are released after the entire command is done.
10031003
</para>
10041004
</listitem>
10051005
</varlistentry>
10061006
</variablelist>
10071007
</para>
10081008

10091009
<para>
1010-
In short, B-tree indexes offer the best performance for concurrent
1010+
Currently, B-tree indexes offer the best performance for concurrent
10111011
applications; since they also have more features than hash
10121012
indexes, they are the recommended index type for concurrent
10131013
applications that need to index scalar data. When dealing with
1014-
non-scalar data, B-trees obviously cannot be used; in that
1015-
situation, application developers should be aware of the
1016-
relatively poor concurrent performance of GiST and R-tree
1017-
indexes.
1014+
non-scalar data, B-trees are not useful, and GiST indexes should
1015+
be used instead. R-tree indexes are deprecated and are likely
1016+
to disappear entirely in a future release.
10181017
</para>
10191018
</sect1>
10201019
</chapter>

0 commit comments

Comments
 (0)