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

On Fri, 20 Mar 2009, esemba wrote:

>
> Thanks, this works quite fine, but I've ran into some problems so far:
> - It's not possible to specify more than 4 labels (just ABCD)

this is well known limitation and we are certainly should think about it

> - In query I have to specify searched vectors for each lexem. I think It
> would be better to specify searched vectors per-query.

we provide low level interface, it's up to you to write your very own
query processing.

>
>
> Oleg Bartunov wrote:
>>
>> 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
>>
>> --
>> 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