Re: two questions about fulltext searchign / tsvector indexes

Vick Khera <vivek(at)khera(dot)org> wrote:

> Jonathan Vanasco <postgres(at)2xlp(dot)com> wrote:

> Personally in these days of cheap disks I'd go with the dedicated
> column. Given that, you want to just have a GIN index on that one
> column, and the query you want, given some plain text string like
> "fluffy dog" is this:
>
> select plainto_tsquery('fluffy dog') @@ my_tsv_column;
>
> I always use a trigger on insert and update to maintain the ts_vector
> column, so there is no doubt of how it was computed by various
> programs.

I was going to make a similar recommendation, but Vick beat me to
it.  The only thing I would add, is that you might want to consider
whether a match in one column should carry more weight than a match
in the other column.  If so, you should convert each to a tsvector
separately, and give each one a different weight before
concatenating the tsvector objects and storing the result.

http://www.postgresql.org/docs/current/interactive/textsearch-features.html#TEXTSEARCH-MANIPULATE-TSVECTOR

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company