Re: Fulltext - multiple single column indexes - Mailing list pgsql-general

On Fri, 20 Mar 2009, esemba wrote:

>
> 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.


if you assign different labels to the concatenated columns, you can
specify in query which columns you're interested in. Also, you
can explicitly specify weight=0 for columns you're not interested.

>
>
> 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@sai.msu.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>
>

     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