Thread: indexes across multiple tables

Toby Tremayne wrote:
> Hi all,
>
> I'm just experimenting with tsearch2 - I have it all working fine but I
> was wondering if there's a way to create indexes containing vector
> columns from multiple tables?  Or if not, how do people usually manage
> this kind of issue?

Postgres doesn't support multi-table indexes so there's no way tsearch2
would be able to.

What exactly are you trying to achieve?

--
Postgresql & php tutorials
http://www.designmagick.com/

On 2/18/07, Chris <dmagick@gmail.com> wrote:
> Toby Tremayne wrote:
> > Hi all,
> >
> > I'm just experimenting with tsearch2 - I have it all working fine but I
> > was wondering if there's a way to create indexes containing vector
> > columns from multiple tables?  Or if not, how do people usually manage
> > this kind of issue?
>
> Postgres doesn't support multi-table indexes so there's no way tsearch2
> would be able to.
>
> What exactly are you trying to achieve?
>
> --

Probably best to join the tables, then simply do the search in the
WHERE clause.  Something like

select p.partname, s.subassemblyname
from part p join subassembly s
on p.partid = s.partid
where p.partidx @@ to_tsquery('Some happy string')
  or s.subidx @@ to_tsquery('Some happy string')

That's how I do it, anyway...

- Ian