← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

« back to all changes in this revision

Viewing changes to doc/src/sgml/html/textsearch-tables.html

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/src/sgml/html/textsearch-tables.html
 
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
2
<HTML
 
3
><HEAD
 
4
><TITLE
 
5
>Tables and Indexes</TITLE
 
6
><META
 
7
NAME="GENERATOR"
 
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
 
9
REV="MADE"
 
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
 
11
REL="HOME"
 
12
TITLE="PostgreSQL 9.1beta1 Documentation"
 
13
HREF="index.html"><LINK
 
14
REL="UP"
 
15
TITLE="Full Text Search"
 
16
HREF="textsearch.html"><LINK
 
17
REL="PREVIOUS"
 
18
TITLE="Introduction"
 
19
HREF="textsearch-intro.html"><LINK
 
20
REL="NEXT"
 
21
TITLE="Controlling Text Search"
 
22
HREF="textsearch-controls.html"><LINK
 
23
REL="STYLESHEET"
 
24
TYPE="text/css"
 
25
HREF="stylesheet.css"><META
 
26
HTTP-EQUIV="Content-Type"
 
27
CONTENT="text/html; charset=ISO-8859-1"><META
 
28
NAME="creation"
 
29
CONTENT="2011-04-27T21:20:33"></HEAD
 
30
><BODY
 
31
CLASS="SECT1"
 
32
><DIV
 
33
CLASS="NAVHEADER"
 
34
><TABLE
 
35
SUMMARY="Header navigation table"
 
36
WIDTH="100%"
 
37
BORDER="0"
 
38
CELLPADDING="0"
 
39
CELLSPACING="0"
 
40
><TR
 
41
><TH
 
42
COLSPAN="5"
 
43
ALIGN="center"
 
44
VALIGN="bottom"
 
45
><A
 
46
HREF="index.html"
 
47
>PostgreSQL 9.1beta1 Documentation</A
 
48
></TH
 
49
></TR
 
50
><TR
 
51
><TD
 
52
WIDTH="10%"
 
53
ALIGN="left"
 
54
VALIGN="top"
 
55
><A
 
56
TITLE="Introduction"
 
57
HREF="textsearch-intro.html"
 
58
ACCESSKEY="P"
 
59
>Prev</A
 
60
></TD
 
61
><TD
 
62
WIDTH="10%"
 
63
ALIGN="left"
 
64
VALIGN="top"
 
65
><A
 
66
TITLE="Full Text Search"
 
67
HREF="textsearch.html"
 
68
>Fast Backward</A
 
69
></TD
 
70
><TD
 
71
WIDTH="60%"
 
72
ALIGN="center"
 
73
VALIGN="bottom"
 
74
>Chapter 12. Full Text Search</TD
 
75
><TD
 
76
WIDTH="10%"
 
77
ALIGN="right"
 
78
VALIGN="top"
 
79
><A
 
80
TITLE="Full Text Search"
 
81
HREF="textsearch.html"
 
82
>Fast Forward</A
 
83
></TD
 
84
><TD
 
85
WIDTH="10%"
 
86
ALIGN="right"
 
87
VALIGN="top"
 
88
><A
 
89
TITLE="Controlling Text Search"
 
90
HREF="textsearch-controls.html"
 
91
ACCESSKEY="N"
 
92
>Next</A
 
93
></TD
 
94
></TR
 
95
></TABLE
 
96
><HR
 
97
ALIGN="LEFT"
 
98
WIDTH="100%"></DIV
 
99
><DIV
 
100
CLASS="SECT1"
 
101
><H1
 
102
CLASS="SECT1"
 
103
><A
 
104
NAME="TEXTSEARCH-TABLES"
 
105
>12.2. Tables and Indexes</A
 
106
></H1
 
107
><P
 
108
>   The examples in the previous section illustrated full text matching using
 
109
   simple constant strings.  This section shows how to search table data,
 
110
   optionally using indexes.
 
111
  </P
 
112
><DIV
 
113
CLASS="SECT2"
 
114
><H2
 
115
CLASS="SECT2"
 
116
><A
 
117
NAME="TEXTSEARCH-TABLES-SEARCH"
 
118
>12.2.1. Searching a Table</A
 
119
></H2
 
120
><P
 
121
>    It is possible to do a full text search without an index.  A simple query
 
122
    to print the <TT
 
123
CLASS="STRUCTNAME"
 
124
>title</TT
 
125
> of each row that contains the word
 
126
    <TT
 
127
CLASS="LITERAL"
 
128
>friend</TT
 
129
> in its <TT
 
130
CLASS="STRUCTFIELD"
 
131
>body</TT
 
132
> field is:
 
133
 
 
134
</P><PRE
 
135
CLASS="PROGRAMLISTING"
 
136
>SELECT title
 
137
FROM pgweb
 
138
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');</PRE
 
139
><P>
 
140
 
 
141
    This will also find related words such as <TT
 
142
CLASS="LITERAL"
 
143
>friends</TT
 
144
>
 
145
    and <TT
 
146
CLASS="LITERAL"
 
147
>friendly</TT
 
148
>, since all these are reduced to the same
 
149
    normalized lexeme.
 
150
   </P
 
151
><P
 
152
>    The query above specifies that the <TT
 
153
CLASS="LITERAL"
 
154
>english</TT
 
155
> configuration
 
156
    is to be used to parse and normalize the strings.  Alternatively we
 
157
    could omit the configuration parameters:
 
158
 
 
159
</P><PRE
 
160
CLASS="PROGRAMLISTING"
 
161
>SELECT title
 
162
FROM pgweb
 
163
WHERE to_tsvector(body) @@ to_tsquery('friend');</PRE
 
164
><P>
 
165
 
 
166
    This query will use the configuration set by <A
 
167
HREF="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG"
 
168
>default_text_search_config</A
 
169
>.
 
170
   </P
 
171
><P
 
172
>    A more complex example is to
 
173
    select the ten most recent documents that contain <TT
 
174
CLASS="LITERAL"
 
175
>create</TT
 
176
> and
 
177
    <TT
 
178
CLASS="LITERAL"
 
179
>table</TT
 
180
> in the <TT
 
181
CLASS="STRUCTNAME"
 
182
>title</TT
 
183
> or <TT
 
184
CLASS="STRUCTNAME"
 
185
>body</TT
 
186
>:
 
187
 
 
188
</P><PRE
 
189
CLASS="PROGRAMLISTING"
 
190
>SELECT title
 
191
FROM pgweb
 
192
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create &amp; table')
 
193
ORDER BY last_mod_date DESC
 
194
LIMIT 10;</PRE
 
195
><P>
 
196
 
 
197
    For clarity we omitted the <CODE
 
198
CLASS="FUNCTION"
 
199
>coalesce</CODE
 
200
> function calls
 
201
    which would be needed to find rows that contain <TT
 
202
CLASS="LITERAL"
 
203
>NULL</TT
 
204
>
 
205
    in one of the two fields.
 
206
   </P
 
207
><P
 
208
>    Although these queries will work without an index, most applications
 
209
    will find this approach too slow, except perhaps for occasional ad-hoc
 
210
    searches.  Practical use of text searching usually requires creating
 
211
    an index.
 
212
   </P
 
213
></DIV
 
214
><DIV
 
215
CLASS="SECT2"
 
216
><H2
 
217
CLASS="SECT2"
 
218
><A
 
219
NAME="TEXTSEARCH-TABLES-INDEX"
 
220
>12.2.2. Creating Indexes</A
 
221
></H2
 
222
><P
 
223
>    We can create a <ACRONYM
 
224
CLASS="ACRONYM"
 
225
>GIN</ACRONYM
 
226
> index (<A
 
227
HREF="textsearch-indexes.html"
 
228
>Section 12.9</A
 
229
>) to speed up text searches:
 
230
 
 
231
</P><PRE
 
232
CLASS="PROGRAMLISTING"
 
233
>CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));</PRE
 
234
><P>
 
235
 
 
236
    Notice that the 2-argument version of <CODE
 
237
CLASS="FUNCTION"
 
238
>to_tsvector</CODE
 
239
> is
 
240
    used.  Only text search functions that specify a configuration name can
 
241
    be used in expression indexes (<A
 
242
HREF="indexes-expressional.html"
 
243
>Section 11.7</A
 
244
>).
 
245
    This is because the index contents must be unaffected by <A
 
246
HREF="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG"
 
247
>default_text_search_config</A
 
248
>.  If they were affected, the
 
249
    index contents might be inconsistent because different entries could
 
250
    contain <TT
 
251
CLASS="TYPE"
 
252
>tsvector</TT
 
253
>s that were created with different text search
 
254
    configurations, and there would be no way to guess which was which.  It
 
255
    would be impossible to dump and restore such an index correctly.
 
256
   </P
 
257
><P
 
258
>    Because the two-argument version of <CODE
 
259
CLASS="FUNCTION"
 
260
>to_tsvector</CODE
 
261
> was
 
262
    used in the index above, only a query reference that uses the 2-argument
 
263
    version of <CODE
 
264
CLASS="FUNCTION"
 
265
>to_tsvector</CODE
 
266
> with the same configuration
 
267
    name will use that index.  That is, <TT
 
268
CLASS="LITERAL"
 
269
>WHERE
 
270
    to_tsvector('english', body) @@ 'a &amp; b'</TT
 
271
> can use the index,
 
272
    but <TT
 
273
CLASS="LITERAL"
 
274
>WHERE to_tsvector(body) @@ 'a &amp; b'</TT
 
275
> cannot.
 
276
    This ensures that an index will be used only with the same configuration
 
277
    used to create the index entries.
 
278
   </P
 
279
><P
 
280
>    It is possible to set up more complex expression indexes wherein the
 
281
    configuration name is specified by another column, e.g.:
 
282
 
 
283
</P><PRE
 
284
CLASS="PROGRAMLISTING"
 
285
>CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));</PRE
 
286
><P>
 
287
 
 
288
    where <TT
 
289
CLASS="LITERAL"
 
290
>config_name</TT
 
291
> is a column in the <TT
 
292
CLASS="LITERAL"
 
293
>pgweb</TT
 
294
>
 
295
    table.  This allows mixed configurations in the same index while
 
296
    recording which configuration was used for each index entry.  This
 
297
    would be useful, for example, if the document collection contained
 
298
    documents in different languages.  Again,
 
299
    queries that are meant to use the index must be phrased to match, e.g.,
 
300
    <TT
 
301
CLASS="LITERAL"
 
302
>WHERE to_tsvector(config_name, body) @@ 'a &amp; b'</TT
 
303
>.
 
304
   </P
 
305
><P
 
306
>    Indexes can even concatenate columns:
 
307
 
 
308
</P><PRE
 
309
CLASS="PROGRAMLISTING"
 
310
>CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || ' ' || body));</PRE
 
311
><P>
 
312
   </P
 
313
><P
 
314
>    Another approach is to create a separate <TT
 
315
CLASS="TYPE"
 
316
>tsvector</TT
 
317
> column
 
318
    to hold the output of <CODE
 
319
CLASS="FUNCTION"
 
320
>to_tsvector</CODE
 
321
>.  This example is a
 
322
    concatenation of <TT
 
323
CLASS="LITERAL"
 
324
>title</TT
 
325
> and <TT
 
326
CLASS="LITERAL"
 
327
>body</TT
 
328
>,
 
329
    using <CODE
 
330
CLASS="FUNCTION"
 
331
>coalesce</CODE
 
332
> to ensure that one field will still be
 
333
    indexed when the other is <TT
 
334
CLASS="LITERAL"
 
335
>NULL</TT
 
336
>:
 
337
 
 
338
</P><PRE
 
339
CLASS="PROGRAMLISTING"
 
340
>ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
 
341
UPDATE pgweb SET textsearchable_index_col =
 
342
     to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));</PRE
 
343
><P>
 
344
 
 
345
    Then we create a <ACRONYM
 
346
CLASS="ACRONYM"
 
347
>GIN</ACRONYM
 
348
> index to speed up the search:
 
349
 
 
350
</P><PRE
 
351
CLASS="PROGRAMLISTING"
 
352
>CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);</PRE
 
353
><P>
 
354
 
 
355
    Now we are ready to perform a fast full text search:
 
356
 
 
357
</P><PRE
 
358
CLASS="PROGRAMLISTING"
 
359
>SELECT title
 
360
FROM pgweb
 
361
WHERE textsearchable_index_col @@ to_tsquery('create &amp; table')
 
362
ORDER BY last_mod_date DESC
 
363
LIMIT 10;</PRE
 
364
><P>
 
365
   </P
 
366
><P
 
367
>    When using a separate column to store the <TT
 
368
CLASS="TYPE"
 
369
>tsvector</TT
 
370
>
 
371
    representation,
 
372
    it is necessary to create a trigger to keep the <TT
 
373
CLASS="TYPE"
 
374
>tsvector</TT
 
375
>
 
376
    column current anytime <TT
 
377
CLASS="LITERAL"
 
378
>title</TT
 
379
> or <TT
 
380
CLASS="LITERAL"
 
381
>body</TT
 
382
> changes.
 
383
    <A
 
384
HREF="textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS"
 
385
>Section 12.4.3</A
 
386
> explains how to do that.
 
387
   </P
 
388
><P
 
389
>    One advantage of the separate-column approach over an expression index
 
390
    is that it is not necessary to explicitly specify the text search
 
391
    configuration in queries in order to make use of the index.  As shown
 
392
    in the example above, the query can depend on
 
393
    <TT
 
394
CLASS="VARNAME"
 
395
>default_text_search_config</TT
 
396
>.  Another advantage is that
 
397
    searches will be faster, since it will not be necessary to redo the
 
398
    <CODE
 
399
CLASS="FUNCTION"
 
400
>to_tsvector</CODE
 
401
> calls to verify index matches.  (This is more
 
402
    important when using a GiST index than a GIN index; see <A
 
403
HREF="textsearch-indexes.html"
 
404
>Section 12.9</A
 
405
>.)  The expression-index approach is
 
406
    simpler to set up, however, and it requires less disk space since the
 
407
    <TT
 
408
CLASS="TYPE"
 
409
>tsvector</TT
 
410
> representation is not stored explicitly.
 
411
   </P
 
412
></DIV
 
413
></DIV
 
414
><DIV
 
415
CLASS="NAVFOOTER"
 
416
><HR
 
417
ALIGN="LEFT"
 
418
WIDTH="100%"><TABLE
 
419
SUMMARY="Footer navigation table"
 
420
WIDTH="100%"
 
421
BORDER="0"
 
422
CELLPADDING="0"
 
423
CELLSPACING="0"
 
424
><TR
 
425
><TD
 
426
WIDTH="33%"
 
427
ALIGN="left"
 
428
VALIGN="top"
 
429
><A
 
430
HREF="textsearch-intro.html"
 
431
ACCESSKEY="P"
 
432
>Prev</A
 
433
></TD
 
434
><TD
 
435
WIDTH="34%"
 
436
ALIGN="center"
 
437
VALIGN="top"
 
438
><A
 
439
HREF="index.html"
 
440
ACCESSKEY="H"
 
441
>Home</A
 
442
></TD
 
443
><TD
 
444
WIDTH="33%"
 
445
ALIGN="right"
 
446
VALIGN="top"
 
447
><A
 
448
HREF="textsearch-controls.html"
 
449
ACCESSKEY="N"
 
450
>Next</A
 
451
></TD
 
452
></TR
 
453
><TR
 
454
><TD
 
455
WIDTH="33%"
 
456
ALIGN="left"
 
457
VALIGN="top"
 
458
>Introduction</TD
 
459
><TD
 
460
WIDTH="34%"
 
461
ALIGN="center"
 
462
VALIGN="top"
 
463
><A
 
464
HREF="textsearch.html"
 
465
ACCESSKEY="U"
 
466
>Up</A
 
467
></TD
 
468
><TD
 
469
WIDTH="33%"
 
470
ALIGN="right"
 
471
VALIGN="top"
 
472
>Controlling Text Search</TD
 
473
></TR
 
474
></TABLE
 
475
></DIV
 
476
></BODY
 
477
></HTML
 
478
>
 
 
b'\\ No newline at end of file'