(Note that since I initially wrote this I have instead gone with a Lucene search engine rather than using TSearch2 directly in PostgreSQL.)

If you want to do complex searches on large text fields in Postgresql? then you need to use tsearch2. The current install comes with tsearch2 installed by default, but you need to run the included tsearch2.sql file first before you can create any tables with tsvector fields.

The instructions to install tsearch2 on linux are different: http://www.devx.com/opensource/Article/21674/0/page/2

On linux it's also necessary to install the dictionary myself: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html

INSERT INTO pg_ts_dict (dict_name,dict_init,dict_initoption,dict_lexize)\\
SELECT 'en_ispell',
  FROM pg_ts_dict
  WHERE dict_name = 'ispell_template';
To make it ready for pg_backup and pg_restore, do: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql

PostgreSQL Misc

When installing on linux the following location has a good instruction on how to install it: http://www.galatea.com/flashguides/start-postgresql-linux
But, the gmake step looped infinitely for me. To fix it I needed to do:
cd /<your psql source dir>
find . -exec touch {} \;

To quit out of the psql command line client, do "q".

On the dev machine, enable stats collection by editing the postgresql.conf file and setting the following to true:

stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
These stats can be reset by doing:
select pg_stat_reset();
The stats can be viewed in pgAdmin on the "Statistics" tab of many of the objects (most importantly on the tables).
Version 6.1 last modified by Geoff Fortytwo on 11/07/2010 at 19:17

Attachments 0

No attachments for this document
Website Top
Send Me Mail!:
   g42website4 AT g42.org
My Encyclopaedia Blog

Creator: Geoff Fortytwo on 2008/05/12 01:17
Copyright 2004-2007 (c) XPertNet and Contributing Authors