Full text search in Postgresql

The majority of a web-applications needs for a search functionality. The easiest way to develop the search functionality in database driven web-applications on their own is using of a regexps. Usually developers remember about that there are a some disadvantages.

The pattern matching query processes all documents every time and there is no index support.
There are no liguistic support, eg you are searching for a document that contains entry but documents that contains entries will be missed.
There is no relevation, ranking, e.t.c.

So why developers use regexps?

Regular exrepssions

Postgresql supports 3 way of using pattern matching queries:

LIKE
SIMILAR TO
POSIX regular expressions

In this note I use LIKE. Let's take a look on table foobar

Table: tst_foobar
id	body
1	Hello world

and SQL-query that searches for a ello.

SELECT 
* 
FROM 
foobar 
WHERE 
body LIKE '%ello%'

How we see. Using a built in search engine(in this case LIKE) is a native way for database driven applications.

Full text search

Another way to operate with collection of documents where document considered as "a bag of words".
Postgresql has 2 data types

tsvector, eg 'Hello world'::tsvector represents document as "a bag of words"
tsquery, eg 'Hello | world'::tsquery supports boolean operators

and operator @@, which represents, correspondingly, document and query.

SELECT 
* 
FROM 
foobar 
WHERE 
body::tsvector @@ 'Hello | world'::tsquery

April 14th, 2011

Back to main