[SQLObject] Using fulltext indexes on Postgres

[Frank B. <fb...@fo...>]

Hallo,

I'd like to use a full text index on some of my SQLObject tables in
PostgreSQL to speed things up a bit.

I considered two alternatives. One is the fti module. fti requires to
use queries like the following which access a second table carrying
the fulltext index(es): 

 select a.id from normal_fti f1, normal a where f1.string ~ '^clone'
 and a.oid = f1.id ;

"normal a" here is the table managed by SQLObject, "normal_fti" has
the fulltext index and looks like this: 
 
 CREATE TABLE normal_fti (
  string varchar(200),
  id oid
 );

Another approach is the GiST-based tsearch module (I cannot use
tsearch2 because I'm stuck with PSQL 7.1 for now) from:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/

tsearch has a nicer query layout, but needs a new column inside the
SQLObject table of type "txtidx". 

Given an index column called "titleidx" this then gives queries like
this: 

 select * from normal where titleidx @@ 'something';

This looks easier to incorporate into SQLObjects .select syntax to me,
but will it disturb my SQLObject classes? I am aware, that I might not
be able to use automatic table generation.

Now my question is: Say, that both are fast enough for me, what kind
of index would you use in a SQLObject application? And I'd love to
hear, if someone has any experiences with one of these or even some
example code, as I'm not sure yet, how to implement the queries? 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__