Re: Database design for separate tsearch table

On Thu, Oct 23, 2008 at 01:06:26AM +0200, Ivan Sergio Borgonovo wrote:
>weight them and you'll be able to search by field and "globally".
>
>I didn't make any scientific test but I previously had something
>like:
>
>create table subtable (
>  subtableid int,
>  body text,
>  ftidx tsvector
>)
>create table maintable (
>  maintableid int,
>  body text,
>  subtableid int
>  ftidx tsvector
>);
>
>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.

>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...

Kind regards,
      Mikkel Høgh