I found the postgres Full Text Search page http://www.postgresql.org/docs/8.3/static/textsearch.html very enlightening.
Especially: http://www.postgresql.org/docs/8.3/static/textsearch-intro.html
Textual search operators have existed in databases for years. PostgreSQL has ~, ~*, LIKE, and ILIKE operators for textual data types, but they lack many essential properties required by modern information systems:
- There is no linguistic support, even
for English. Regular expressions are
not sufficient because they cannot
easily handle derived words, e.g.,
satisfies and satisfy. You might miss
documents that contain satisfies,
although you probably would like to
find them when searching for satisfy.
It is possible to use OR to search
for multiple derived forms, but this
is tedious and error-prone (some
words can have several thousand
derivatives).
- They provide no ordering (ranking) of
search results, which makes them
ineffective when thousands of
matching documents are found.
- They tend to be slow because there is
no index support, so they must
process all documents for every
search.
answered 2009-06-26T17:21:59.647