Re: [HACKERS] Using GIN/Gist to search the union of two indexes?

2010-03-06 Thread Dimitri Fontaine 
Jesper Krogh jes...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Using GIN/Gist to search the union of two indexes?

2010-03-05 Thread Jesper Krogh 
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers