Re: Fulltext - multiple single column indexes

On Thu, 19 Mar 2009, esemba wrote:

>
> Hi,
> I have table with several columns and need to perform fulltext search over
> volatile number of columns.
> I can't use multicolumn gist index or gin index over concatenated columns,
> so I've created several single column indexes (one for each column I want to
> search) and now I need to query them like this:
>
> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
> coalesce(resume, '')) || ...
> @@ to_tsquery('cs', 'Query text');

alter table YOURTABLE add columnt fts tsvector;
update YOURTABLE set fts=
     to_tsvector('cs', coalesce(annotation, '')) ||
     to_tsvector('cs', coalesce(resume, '')) || ...
create index fts_idx on YOURTABLE using gin(fts);
vacuum analyze YOURTABLE;
select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;


>
> This query works, but EXPLAIN has shown me, that postgres doesn't use the
> indexes, so the query over a table with several thousands of records last
> very long time. I've figured out, that indexes probably cannot be used this
> way. What is a recommendation for this scenario?
> Indexes over static number of columns work fine, but I can't use them,
> because in my application logic I want to let user choose which columns to
> search.
>
> Thank you for your reply.
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83