PostgresqlDb

TSearch2

(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',
    dict_init,
    'DictFile="/usr/local/pgsql/lib/english.med",'
    'AffFile="/usr/local/pgsql/lib/english.aff",'
    'StopFile="/usr/local/pgsql/share/contrib/english.stop"',
    dict_lexize
  FROM pg_ts_dict
  WHERE dict_name = 'ispell_template';

PostgreSQL Misc

• How to create triggers

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

select pg_stat_reset();