begriffs open source - ai-pg/blob - full-docs/html/textsearch-tables.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>12.2. Tables and Indexes</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="textsearch-intro.html" title="12.1. Introduction" /><link rel="next" href="textsearch-controls.html" title="12.3. Controlling Text Search" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">12.2. Tables and Indexes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="textsearch-intro.html" title="12.1. Introduction">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="textsearch.html" title="Chapter 12. Full Text Search">Up</a></td><th width="60%" align="center">Chapter 12. Full Text Search</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="textsearch-controls.html" title="12.3. Controlling Text Search">Next</a></td></tr></table><hr /></div><div class="sect1" id="TEXTSEARCH-TABLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">12.2. Tables and Indexes <a href="#TEXTSEARCH-TABLES" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH">12.2.1. Searching a Table</a></span></dt><dt><span class="sect2"><a href="textsearch-tables.html#TEXTSEARCH-TABLES-INDEX">12.2.2. Creating Indexes</a></span></dt></dl></div><p>
   3    The examples in the previous section illustrated full text matching using
   4    simple constant strings.  This section shows how to search table data,
   5    optionally using indexes.
   6   </p><div class="sect2" id="TEXTSEARCH-TABLES-SEARCH"><div class="titlepage"><div><div><h3 class="title">12.2.1. Searching a Table <a href="#TEXTSEARCH-TABLES-SEARCH" class="id_link">#</a></h3></div></div></div><p>
   7     It is possible to do a full text search without an index.  A simple query
   8     to print the <code class="structname">title</code> of each row that contains the word
   9     <code class="literal">friend</code> in its <code class="structfield">body</code> field is:
  10
  11 </p><pre class="programlisting">
  12 SELECT title
  13 FROM pgweb
  14 WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
  15 </pre><p>
  16
  17     This will also find related words such as <code class="literal">friends</code>
  18     and <code class="literal">friendly</code>, since all these are reduced to the same
  19     normalized lexeme.
  20    </p><p>
  21     The query above specifies that the <code class="literal">english</code> configuration
  22     is to be used to parse and normalize the strings.  Alternatively we
  23     could omit the configuration parameters:
  24
  25 </p><pre class="programlisting">
  26 SELECT title
  27 FROM pgweb
  28 WHERE to_tsvector(body) @@ to_tsquery('friend');
  29 </pre><p>
  30
  31     This query will use the configuration set by <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>.
  32    </p><p>
  33     A more complex example is to
  34     select the ten most recent documents that contain <code class="literal">create</code> and
  35     <code class="literal">table</code> in the <code class="structname">title</code> or <code class="structname">body</code>:
  36
  37 </p><pre class="programlisting">
  38 SELECT title
  39 FROM pgweb
  40 WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create &amp; table')
  41 ORDER BY last_mod_date DESC
  42 LIMIT 10;
  43 </pre><p>
  44
  45     For clarity we omitted the <code class="function">coalesce</code> function calls
  46     which would be needed to find rows that contain <code class="literal">NULL</code>
  47     in one of the two fields.
  48    </p><p>
  49     Although these queries will work without an index, most applications
  50     will find this approach too slow, except perhaps for occasional ad-hoc
  51     searches.  Practical use of text searching usually requires creating
  52     an index.
  53    </p></div><div class="sect2" id="TEXTSEARCH-TABLES-INDEX"><div class="titlepage"><div><div><h3 class="title">12.2.2. Creating Indexes <a href="#TEXTSEARCH-TABLES-INDEX" class="id_link">#</a></h3></div></div></div><p>
  54     We can create a <acronym class="acronym">GIN</acronym> index (<a class="xref" href="textsearch-indexes.html" title="12.9. Preferred Index Types for Text Search">Section 12.9</a>) to speed up text searches:
  55
  56 </p><pre class="programlisting">
  57 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));
  58 </pre><p>
  59
  60     Notice that the 2-argument version of <code class="function">to_tsvector</code> is
  61     used.  Only text search functions that specify a configuration name can
  62     be used in expression indexes (<a class="xref" href="indexes-expressional.html" title="11.7. Indexes on Expressions">Section 11.7</a>).
  63     This is because the index contents must be unaffected by <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>.  If they were affected, the
  64     index contents might be inconsistent because different entries could
  65     contain <code class="type">tsvector</code>s that were created with different text search
  66     configurations, and there would be no way to guess which was which.  It
  67     would be impossible to dump and restore such an index correctly.
  68    </p><p>
  69     Because the two-argument version of <code class="function">to_tsvector</code> was
  70     used in the index above, only a query reference that uses the 2-argument
  71     version of <code class="function">to_tsvector</code> with the same configuration
  72     name will use that index.  That is, <code class="literal">WHERE
  73     to_tsvector('english', body) @@ 'a &amp; b'</code> can use the index,
  74     but <code class="literal">WHERE to_tsvector(body) @@ 'a &amp; b'</code> cannot.
  75     This ensures that an index will be used only with the same configuration
  76     used to create the index entries.
  77    </p><p>
  78     It is possible to set up more complex expression indexes wherein the
  79     configuration name is specified by another column, e.g.:
  80
  81 </p><pre class="programlisting">
  82 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector(config_name, body));
  83 </pre><p>
  84
  85     where <code class="literal">config_name</code> is a column in the <code class="literal">pgweb</code>
  86     table.  This allows mixed configurations in the same index while
  87     recording which configuration was used for each index entry.  This
  88     would be useful, for example, if the document collection contained
  89     documents in different languages.  Again,
  90     queries that are meant to use the index must be phrased to match, e.g.,
  91     <code class="literal">WHERE to_tsvector(config_name, body) @@ 'a &amp; b'</code>.
  92    </p><p>
  93     Indexes can even concatenate columns:
  94
  95 </p><pre class="programlisting">
  96 CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));
  97 </pre><p>
  98    </p><p>
  99     Another approach is to create a separate <code class="type">tsvector</code> column
 100     to hold the output of <code class="function">to_tsvector</code>.  To keep this
 101     column automatically up to date with its source data, use a stored
 102     generated column.  This example is a
 103     concatenation of <code class="literal">title</code> and <code class="literal">body</code>,
 104     using <code class="function">coalesce</code> to ensure that one field will still be
 105     indexed when the other is <code class="literal">NULL</code>:
 106
 107 </p><pre class="programlisting">
 108 ALTER TABLE pgweb
 109     ADD COLUMN textsearchable_index_col tsvector
 110                GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
 111 </pre><p>
 112
 113     Then we create a <acronym class="acronym">GIN</acronym> index to speed up the search:
 114
 115 </p><pre class="programlisting">
 116 CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);
 117 </pre><p>
 118
 119     Now we are ready to perform a fast full text search:
 120
 121 </p><pre class="programlisting">
 122 SELECT title
 123 FROM pgweb
 124 WHERE textsearchable_index_col @@ to_tsquery('create &amp; table')
 125 ORDER BY last_mod_date DESC
 126 LIMIT 10;
 127 </pre><p>
 128    </p><p>
 129     One advantage of the separate-column approach over an expression index
 130     is that it is not necessary to explicitly specify the text search
 131     configuration in queries in order to make use of the index.  As shown
 132     in the example above, the query can depend on
 133     <code class="varname">default_text_search_config</code>.  Another advantage is that
 134     searches will be faster, since it will not be necessary to redo the
 135     <code class="function">to_tsvector</code> calls to verify index matches.  (This is more
 136     important when using a GiST index than a GIN index; see <a class="xref" href="textsearch-indexes.html" title="12.9. Preferred Index Types for Text Search">Section 12.9</a>.)  The expression-index approach is
 137     simpler to set up, however, and it requires less disk space since the
 138     <code class="type">tsvector</code> representation is not stored explicitly.
 139    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="textsearch-intro.html" title="12.1. Introduction">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="textsearch.html" title="Chapter 12. Full Text Search">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="textsearch-controls.html" title="12.3. Controlling Text Search">Next</a></td></tr><tr><td width="40%" align="left" valign="top">12.1. Introduction </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 12.3. Controlling Text Search</td></tr></table></div></body></html>