Now that's done, how do we take advantage of full-text indexing?

Regular search will most likely use a 'LIKE' search (we're searching for 'postgresql search'):

SELECT newsid, newstitle FROM newsitem WHERE newscontent LIKE '%postgresql%' AND newscontent LIKE '%search%';

This will be quite slow because postgresql has to examine each row to see if it contains 'postgresql' and 'search' - even with an index.

Using full text indexing, it becomes:

SELECT newsid, newstitle FROM newsitem WHERE newscontent_fti @@ to_tsquery('default', 'postgresql & search');

The operator - '@@' - tells postgresql to use the special index (this is created when the tsearch2.sql script is installed).

TSearch2 supports multiple dictionaries so 'default' tells it to use the standard dictionary (don't worry about this for now).

Our search terms are separated by '&' - which is the binary operator for 'AND'.

If you want an OR search, use '|' instead, so it becomes:

SELECT newsid, newstitle FROM newsitem WHERE newscontent_fti @@ to_tsquery('default', 'postgresql | search');

Which will find items with 'postgresql' OR 'search' in the content.

The last step we need to take is to make sure our full text index is kept up to date. To do that, we can use a trigger.

CREATE TRIGGER fulltext_update BEFORE UPDATE OR INSERT ON newsitem
FOR EACH ROW EXECUTE PROCEDURE
tsearch2(newscontent_fti, newscontent);

Every time a row is added or updated in the newsitem table, this procedure will update the full text index appropriately.

That's it! It's a little more complicated than other databases full-text indexing, but it works well and is quite fast compared to LIKE searches.

There is more tsearch2 documentation available here - check it out for more advanced options and functionality.