Re: Database design for separate tsearch table

On Thu, 23 Oct 2008 14:20:49 +0200
Mikkel Høgh <m(at)ooh(dot)dk> wrote:

> >but it seem that just searching on a tsvector in maintable
> >build up with
> >
> >setweight(to_tsvector('pg_catalog.english',
> >coalesce(maintable.body,'')), 'A') || ' ' ||
> >
> >setweight(to_tsvector('pg_catalog.english',
> >coalesce(subtable.body,'')), 'B')
> >
> >is faster.
>
> Ok, that seems to be a good approach, thank you.

Unless proven false by a more scientific test than mine.
I'd like to hear more informed opinions on this.
I noticed a performance difference between 1 vs more index but
mainly I've chosen 1 index because it made all the code simpler and
did suit to my needs. So I didn't dig further in "could I make the
other way faster?".

There is one limit (with its implication): you've a limited number
of weight (ABCD).

> >Beware of the difference between gist and gin indexes for
> >"restricted" weighted searches since with the latter you've to use
> >@@@
> Um, could you clarify that? I know the general differences between
> gist and gin, but not how it affects weighted searches...

http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html

search for @@@

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

--
Ivan Sergio Borgonovo
http://www.webthatworks.it