Re: Fulltext - multiple single column indexes

Well, thank you both for response, but I'm not sure, I understand Oleg's
solution. This would work, but where is the variability of searched columns?
In your example, I create new indexed column with concatenated vectors of 2
columns. But I sometimes new to search only annotation, sometimes resume,
sometomes both.

Oleg Bartunov wrote:
>
> 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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

--
View this message in context: http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22617663.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.