pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedFull Text Search examples patch
7+ messages / 5 participants
[nested] [flat]
* Full Text Search examples patch
@ 2008-01-11 21:34 Erik <er@xs4all.nl>
2008-01-12 00:46 ` Re: Full Text Search examples patch Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Erik @ 2008-01-11 21:34 UTC (permalink / raw)
To: pgsql-docs
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">
* Re: Full Text Search examples patch
2008-01-11 21:34 Full Text Search examples patch Erik <er@xs4all.nl>
@ 2008-01-12 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2008-01-12 00:46 UTC (permalink / raw)
To: Erik <er@xs4all.nl>; +Cc: pgsql-docs
"Erik" <er@xs4all.nl> writes:
> 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.)
Huh? These are just examples, they are not supposed to refer to any
specific real table, much less one that comes standard with Postgres.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full Text Search examples patch
@ 2008-01-13 19:09 Erik <er@xs4all.nl>
2008-01-15 22:38 ` Re: Full Text Search examples patch Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 7+ messages in thread
From: Erik @ 2008-01-13 19:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
Tom Lane wrote:
> "Erik" <er@xs4all.nl> writes:
>> 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.)
>
> Huh? These are just examples, they are not supposed to
> refer to any
> specific real table, much less one that comes standard
> with Postgres.
>
> regards, tom lane
I understand that, of course. I am suggesting it might be
an improvement to offer the data to go with the examples.
regards,
Erik Rijkers
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full Text Search examples patch
2008-01-13 19:09 Re: Full Text Search examples patch Erik <er@xs4all.nl>
@ 2008-01-15 22:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-01-16 08:00 ` Re: Full Text Search examples patch Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2008-01-15 22:38 UTC (permalink / raw)
To: Erik <er@xs4all.nl>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
Erik wrote:
> Tom Lane wrote:
> > "Erik" <er@xs4all.nl> writes:
> >> 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.)
> >
> > Huh? These are just examples, they are not supposed to
> > refer to any
> > specific real table, much less one that comes standard
> > with Postgres.
>
> I understand that, of course. I am suggesting it might be
> an improvement to offer the data to go with the examples.
Hmm, wouldn't it be easier to provide the pgweb table?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full Text Search examples patch
2008-01-13 19:09 Re: Full Text Search examples patch Erik <er@xs4all.nl>
2008-01-15 22:38 ` Re: Full Text Search examples patch Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-01-16 08:00 ` Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 7+ messages in thread
From: Oleg Bartunov @ 2008-01-16 08:00 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Erik <er@xs4all.nl>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
On Tue, 15 Jan 2008, Alvaro Herrera wrote:
> Erik wrote:
>> Tom Lane wrote:
>>> "Erik" <er@xs4all.nl> writes:
>>>> 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.)
>>>
>>> Huh? These are just examples, they are not supposed to
>>> refer to any
>>> specific real table, much less one that comes standard
>>> with Postgres.
>>
>> I understand that, of course. I am suggesting it might be
>> an improvement to offer the data to go with the examples.
>
> Hmm, wouldn't it be easier to provide the pgweb table?
probably, we could use one of the sample database from pgfoundry ?
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full Text Search examples patch
@ 2008-01-16 02:06 Erik <er@xs4all.nl>
2008-03-26 14:10 ` Re: Full Text Search examples patch Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 7+ messages in thread
From: Erik @ 2008-01-16 02:06 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
Alvaro Herrera wrote:
> Erik wrote:
>> Tom Lane wrote:
>> > "Erik" <er@xs4all.nl> writes:
>> >> 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.)
>> >
>> > Huh? These are just examples, they are not supposed
>> to
>> > refer to any
>> > specific real table, much less one that comes standard
>> > with Postgres.
>>
>> I understand that, of course. I am suggesting it might
>> be
>> an improvement to offer the data to go with the
>> examples.
>
> Hmm, wouldn't it be easier to provide the pgweb table?
Maybe - I don't know what that pgweb data would be.
There are really two issues:
1. Apply changes I suggested (and attached earlier) to
the documentation text to make code examples use a
single table instead of two different ones. I chose
apod because I knew apod from tsearch2. In the patch
there are also some confusing inconsistencies removed,
for instance column 'textsearch_indexable_col' is in a
later code example used as column 'textsearch'.
2. Whether or not to include some actual table data (be
it apod or pgweb). This is less important as far as I am
concerned.
The first is more useful and easier to apply than the
second.
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development,
> 24x7 support
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by
> donating at
>
> http://www.postgresql.org/about/donate
>
regards,
Erik Rijkers
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Full Text Search examples patch
2008-01-16 02:06 Re: Full Text Search examples patch Erik <er@xs4all.nl>
@ 2008-03-26 14:10 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2008-03-26 14:10 UTC (permalink / raw)
To: Erik <er@xs4all.nl>; +Cc: Alvaro Herrera <alvherre@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
Erik wrote:
> > Hmm, wouldn't it be easier to provide the pgweb table?
>
> Maybe - I don't know what that pgweb data would be.
>
> There are really two issues:
>
> 1. Apply changes I suggested (and attached earlier) to
> the documentation text to make code examples use a
> single table instead of two different ones. I chose
> apod because I knew apod from tsearch2. In the patch
> there are also some confusing inconsistencies removed,
> for instance column 'textsearch_indexable_col' is in a
> later code example used as column 'textsearch'.
>
> 2. Whether or not to include some actual table data (be
> it apod or pgweb). This is less important as far as I am
> concerned.
>
> The first is more useful and easier to apply than the
> second.
Agreed. The data file you sent was 1.5MB of data uncompressed. That
isn't going in the docs.
I looked at adding just the schema you had:
CREATE TABLE apod (
title text,
body text,
sdate date,
keywords text,
id integer
);
but later saw that there were 'apod' references later in the docs that
mentioned columns that weren't in the CREATE TABLE, e.g., 'vector':
SELECT * FROM ts_stat('SELECT vector FROM apod')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
Please send a patch that adds the CREATE TABLE in a consistent manner
and we will consider just that addition. Here is where I suggest you
add the CREATE TABLE in the patch:
<programlisting>
CREATE TABLE apod (
title text,
body text,
sdate date,
keywords text,
id integer);
-- insert some data
SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC LIMIT 10;
I am fine with you updating the later examples to be more consistent
too.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-03-26 14:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-11 21:34 Full Text Search examples patch Erik <er@xs4all.nl>
2008-01-12 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-13 19:09 Re: Full Text Search examples patch Erik <er@xs4all.nl>
2008-01-15 22:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-01-16 08:00 ` Oleg Bartunov <oleg@sai.msu.su>
2008-01-16 02:06 Re: Full Text Search examples patch Erik <er@xs4all.nl>
2008-03-26 14:10 ` Bruce Momjian <bruce@momjian.us>
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