Обсуждение: Using GIN/Gist to search the "union" of two indexes?

Hi.

How complicated would it be to make postgresql-fts search the "union" of
several GIN/Gist indexes.

The use-case is that you have two tables:

tablea(id serial, tableb_id int, text tsvector);
and
tableb(id serial, text tsvector);
and indices on both tsvectors.

The typical query would join the two tables on the key:

select id from tablea,tableb where tablea.tableb_id = tableb.id;

And then filter the results on the fts-indexes:

select id from tablea,tableb where tablea.tableb_id = tableb.id and
tablea.text @@ to_tsquery('ftsquery') or tableb.text @@
to_tsquery('ftsquery');

This one is doable .. using some "mocking" of queries in the
application. But if ftsquery is:

"terma & termb" where terma only exists in tablea and termb only exists
in tableb, then it doesn't work. The path would seem to be to "not use"
the indexes.

I guess it would be something like a new "ts_match_vq() that can take
more than one vector and get the underlying logik to do the union at
search time?

Can someone with more insigth into the code tell me if it is persieved a
"hard task" to do?

Thanks.
Jesper
-- 
Jesper

Jesper Krogh <jesper@krogh.cc> writes:
> select id from tablea,tableb where tablea.tableb_id = tableb.id and
> tablea.text @@ to_tsquery('ftsquery') or tableb.text @@
> to_tsquery('ftsquery');
>
> This one is doable .. using some "mocking" of queries in the
> application. But if ftsquery is:
>
> "terma & termb" where terma only exists in tablea and termb only exists
> in tableb, then it doesn't work. The path would seem to be to "not use"
> the indexes.

You probably could maintain a separate "materialized" table with a
single tsvector for the two input tables, then query that vector
alone. Given the right foreign keys or copied data you'll get the
matched data easily too.

Hope this helps, regards,
-- 
dim