[rt-users] RT full text indexing with PostgreSQL

[Kenneth Marshall]

On Sun, Feb 01, 2009 at 09:41:42PM -0600, Kenneth Marshall wrote:
> > I just updated the PostgreSQLFullText pages on the wiki with
> > the triggers to keep the parsed document columns updated whenever
> > there is a change to the dependent columns.
> > 
> > Here are the commands for adding the triggers to keep the textsearchable
> > columns corresponding to attachments.subject/content and
> > objectcustomfieldvalues.largecontent up to date:
> > 
> >   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
> >   ON attachments FOR EACH ROW EXECUTE PROCEDURE
> >   tsvector_update_trigger(textsearchable, 'pg_catalog.english', subject, content);
> > 
> >   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
> >   ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE
> >   tsvector_update_trigger(textsearchable, 'pg_catalog.english', largecontent);
> > 
> > Please let me know if there are any problems and bon voyage.
> > 
> > Ken
> 
> I have added a variation of the above triggers to the wiki page
> to only pre-parse the first 1MB of attachments/customfields:
> 
>   CREATE FUNCTION attachments_trigger() RETURNS trigger AS $$
>   begin
>     new.textsearchable :=
>         to_tsvector('pg_catalog.english', substring(coalesce(new.subject, '') || coalesce(new.content, '') from 1 for 1000000));
>     return new;
>   end
>   $$ LANGUAGE plpgsql;
> 
>   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
>   ON attachments FOR EACH ROW EXECUTE PROCEDURE attachments_trigger();
> 
>   CREATE FUNCTION objectcustomfieldvalues_trigger() RETURNS trigger AS $$
>   begin
>     new.textsearchable :=
>         to_tsvector('pg_catalog.english', substring(coalesce(new.largecontent, '') from 1 for 1000000));
>     return new;
>   end
>   $$ LANGUAGE plpgsql;
> 
>   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
>   ON objectcustomfieldvalues FOR EACH ROW EXECUTE PROCEDURE
>   objectcustomfieldvalues_trigger();
> 
> Please send me any feedback or suggestions.
> 
> Cheers,
> Ken
> 
> PS. The fast full contents searches really rock!
> 

Dear RT Users,

I just finished testing the two index types for PostgreSQL text
indexing (GIST and GIN). Here are the index creation commands using
the GIN index type. I have tried both, and unless you are in an
extremely update intensive environment you will really want GIN
-- very, very fast queries.

CREATE INDEX attachments_textsearch ON attachments
  USING GIN (textsearchable );

CREATE INDEX largecontent_textsearch ON objectcustomfieldvalues
  USING GIN (textsearchable );


Happy full text searching,
Ken