From: Erik <er@xs4all.nl>
To: pgsql-docs@postgresql.org
Subject: Full Text Search examples patch
Date: Fri, 11 Jan 2008 22:34:02 +0100 (CET)
Message-ID: <25283.156.83.0.77.1200087242.squirrel@webmail.xs4all.nl> (raw)
I made some changes to textsearch.sgml, the FTS
documentation. It contained references to a table 'pgweb'
that was nowhere to be found, and a similarly unreferenced
'apod' table, which I happened to have found earlier here:
http://www.sai.msu.su/~megera/postgres/fts/apod.dump.gz
(it has around 1700 rows).
I changed all references to 'pgweb' to 'apod'. This apod
table initially corresponds with the structure of the
table in the dump file. (In the documentation, that table
is subsequently changed by adding a tsvector column.)
It would be nice if that apod table could be included with
postgres in its entirety, or maybe even an updated
version, as the above dump only contains records from
1995-2001. In case the table is deemed to large for
inclusion in the distribution, I have put the initial
'create' statements and selected 20 records, so that the
example queries give some results (albeit fewer results
than there are in the textsearch.sgml document now - it
was apparently run with a full apod table).
With these changes (from table 'pgweb' to 'apod') the
examples can be run.
If the table data is included (whole or truncated) a
paragraph saying where to find it should be written
too.
Erik Rijkers
Attachments:textsearch.sgml.diff (application/octet-stream, 4.6K) download--- doc/src/sgml/textsearch.sgml.orig 2008-01-11 22:20:16.000000000 +0100+++ doc/src/sgml/textsearch.sgml 2008-01-11 22:27:57.000000000 +0100@@ -418,16 +418,16 @@
<para>
It is possible to do full text search with no index. A simple query
to print the <structname>title</> of each row that contains the word
- <literal>friend</> in its <structfield>body</> field is:+ <literal>planet</> in its <structfield>body</> field is:
<programlisting>
SELECT title
-FROM pgweb-WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');+FROM apod+WHERE to_tsvector('english', body) @@ to_tsquery('english', 'planet');
</programlisting>
- This will also find related words such as <literal>friends</>- and <literal>friendly</>, since all these are reduced to the same+ This will also find related words such as <literal>planets</>+ and <literal>planetary</>, since all these are reduced to the same
normalized lexeme.
</para>
@@ -438,8 +438,8 @@
<programlisting>
SELECT title
-FROM pgweb-WHERE to_tsvector(body) @@ to_tsquery('friend');+FROM apod+WHERE to_tsvector(body) @@ to_tsquery('planet');
</programlisting>
This query will use the configuration set by <xref
@@ -448,14 +448,14 @@
<para>
A more complex example is to
- select the ten most recent documents that contain <literal>create</> and- <literal>table</> in the <structname>title</> or <structname>body</>:+ select the ten most recent documents that contain <literal>comet</> and+ <literal>tail</> in the <structname>title</> or <structname>body</>:
<programlisting>
SELECT title
-FROM pgweb-WHERE to_tsvector(title || body) @@ to_tsquery('create & table')-ORDER BY last_mod_date DESC LIMIT 10;+FROM apod+WHERE to_tsvector(title || body) @@ to_tsquery('comet & tail')+ORDER BY sdate DESC LIMIT 10;
</programlisting>
For clarity we omitted the <function>coalesce</function> function
@@ -480,7 +480,7 @@
linkend="textsearch-indexes">) to speed up text searches:
<programlisting>
-CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));+CREATE INDEX apod_idx ON apod USING gin(to_tsvector('english', body));
</programlisting>
Notice that the 2-argument version of <function>to_tsvector</function> is
@@ -510,10 +510,10 @@
configuration name is specified by another column, e.g.:
<programlisting>
-CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body));+CREATE INDEX apod_idx ON apod USING gin(to_tsvector(config_name, body));
</programlisting>
- where <literal>config_name</> is a column in the <literal>pgweb</>+ where <literal>config_name</> is a column in the <literal>apod</>
table. This allows mixed configurations in the same index while
recording which configuration was used for each index entry. This
would be useful, for example, if the document collection contained
@@ -526,7 +526,7 @@
Indexes can even concatenate columns:
<programlisting>
-CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || body));+CREATE INDEX apod_idx ON apod USING gin(to_tsvector('english', title || body));
</programlisting>
</para>
@@ -538,24 +538,24 @@
indexed when the other is <literal>NULL</>:
<programlisting>
-ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;-UPDATE pgweb SET textsearchable_index_col =+ALTER TABLE apod ADD COLUMN textsearch tsvector;+UPDATE apod SET textsearch =
to_tsvector('english', coalesce(title,'') || coalesce(body,''));
</programlisting>
Then we create a <acronym>GIN</acronym> index to speed up the search:
<programlisting>
-CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);+CREATE INDEX textsearch_idx ON apod USING gin(textsearch);
</programlisting>
Now we are ready to perform a fast full text search:
<programlisting>
SELECT title
-FROM pgweb-WHERE textsearchable_index_col @@ to_tsquery('create & table')-ORDER BY last_mod_date DESC LIMIT 10;+FROM apod+WHERE textsearch @@ to_tsquery('comet & tail')+ORDER BY sdate DESC LIMIT 10;
</programlisting>
</para>
@@ -594,8 +594,7 @@
user query. Also, we need to return results in a useful order, so we need
a function that compares documents with respect to their relevance to
the query. It's also important to be able to display the results nicely.
- <productname>PostgreSQL</productname> provides support for all of these- functions.+ <productname>PostgreSQL</productname> provides all these functions.
</para>
<sect2 id="textsearch-parsing-documents">
apod_create_table.sql (application/octet-stream, 19.5K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: er@xs4all.nl
Subject: Re: Full Text Search examples patch
In-Reply-To: <25283.156.83.0.77.1200087242.squirrel@webmail.xs4all.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox